Christopher Winslett
Christopher Winslett
Automating pull requests to deploy staging applications is a game changer for large teams performing shipping quality products. Using Crunchy Bridge ’s CLI or API, you can easily automate the entire process for these staging deployments. The simplest workflow would look something like the following: In this example, during the “Create Postgres Cluster”, we’ll create a hobby-0 cluster for Postgres. Then, when the PR is closed the cluster will be torn down. We keep it simple for this example, bu...
Read MoreChristopher Winslett
Christopher Winslett
Over the past 12 months, AI has taken over budgets and initiatives. Postgres is a popular store for AI embedding data because it can store, calculate, optimize, and scale using the pgvector extension . A recently introduced gem to the Ruby on Rails ecosystem, the neighbor gem, makes working with pgvector and Rails even better. An “embedding” is a set of floating point values that represent the characteristics of a thing (nothing new, we’ve had these since the 70s). Using the OpenAI API or any o...
Read MoreChristopher 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 More