Christopher Winslett
Christopher Winslett
I just spent last week at Rails World in Amsterdam and had a blast digging back into the Rails and Active Record world. In conversations with developers over the week, I had some notable takeaways from the newest version of Ruby on Rails that I just had to get written up. A quick summary before we dig in: • async queries : send long-running queries the background while the code runs along, great for pages with multiple long-running queries that can be run in parallel • composite primary keys : n...
Read MoreChristopher Winslett
Christopher Winslett
Postgres has been steadily building on the JSON functionality initially released more than 10 years ago . With Postgres 16, working with JSON has gotten a couple nice improvements. Primarily, this release added features that ease the manipulation of data into JSON and improve the standard SQL functionality using JSON. TL;DR: • A SQL/JSON data-type check. For instance, this lets you ask with SQL if something • Addition of SQL-standard JSON functions: , , , and A SQL/JSON data-type check. For...
Read MoreChristopher Winslett
Christopher Winslett
Postgres’ pgvector extension recently added HNSW as a new index type for vector data. This levels up the database for vector-based embeddings output by AI models. A few months ago, we had written about approximate nearest neighbor pgvector performance using the available list-based indexes . Now, with the addition of HNSW, pgvector can use the latest graph based algorithms to approximate nearest neighbor queries. As with all things databases, there are trade-offs, so don’t throw away the list...
Read MoreChristopher Winslett
Christopher Winslett
Note: We have additional articles in this Vectors & LLMs series . Vector data has made its way into Postgres and I’m seeing more and more folks using it by the day. As I’ve seen use cases trickle in, I have been thinking a lot about scaling data and how to set yourself up for performance success from the beginning. The two primary trade-offs are performance versus accuracy. When seeking performance with vector data, we are using nearest neighbor algorithms, and those algorithms are built aroun...
Read MoreChristopher Winslett
Christopher Winslett
We have a lot of Postgres foreign data wrapper ( FDW ) users here at Crunchy. The postgres_fdw lets you connect to remote servers and in some cases can be an alternative for traditional ETL/ELT processes. As I see use of the Postgres foreign data wrapper expanding, I wanted to make some recommendations on how to approach performance. When you query from a single Postgres cluster, the database cleverly uses internal statistics to auto-tune performance . However, when querying from the Postgr...
Read MoreChristopher Winslett
Christopher Winslett
We have been talking a lot here about using Postgres for metrics, dashboards, and analytics . One of my favorite Postgres tools that makes a lot of this work easy and efficient is Hyperloglog ( HLL ). Hyperloglog is like Regex, once you understand it -- you feel like it's a superpower. Also, like Regex -- it can't solve everything. In this post I’ll take you through how to get started with HLL and build some sample queries, and get started with simple tuning. Hyperloglog is a compression and...
Read MoreChristopher Winslett
Christopher Winslett
Note: pgvector 0.5 released HNSW indexes which improved performance significantly. Read more about it HNSW Indexes with Postgres and pgvector . We have additional articles in this Vectors & LLMs series . As we've been helping people get started with AI in Postgres with , there have been few questions around performance. At a basic level, pgvector performance relies on 3 things: 1. Are your queries using indexes? 2. Are you setting your size appropriately for your data set? 3. Do you have en...
Read MoreChristopher Winslett
Christopher Winslett
Crunchy Data is getting ready to be at RailsConf 2023 in Atlanta next week and we’ve been thinking about our Rails and ActiveRecord users and customers. One of the easiest ways to improve query performance using an ORM is to lean on as much SQL as you can. I’m going to walk through some of the ActiveRecord basics and how to use some smart SQL to work around N+1 query problems. What do I mean by " CRUD "? It's short-hand for create-read-update-delete . For instance, ORMs make it so nice to do an...
Read MoreChristopher Winslett
Christopher Winslett
"Last time I had data like this, I stored the data in MongoDB." --Me, last week. I told this to a friend while talking through some of their data problems. As Craig likes to say, Crunchy Data is "Just Postgres" , but we also know there are some clear cut use cases where Postgres isn’t the perfect tool for the job. Don’t get us wrong, Postgres is an amazing database and we strongly believe what you should start with for most applications, but that doesn’t mean you can’t benefit from more tha...
Read MoreChristopher Winslett
Christopher Winslett
MongoDB got an early start as a developer friendly database. Mongo was trivial to get started with and has some good tooling for scaling. Since you didn't have to create a schema, you were off and running in no time. Don't get us wrong, we do firmly believe a well designed schema is important. If you're not managing your schema it's managing you. Because of these capabilities, MongoDB has been used by teams for: • the typical, primary application database • supporting database for large, sharded...
Read More