Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Christopher 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 Postgres AI 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 enoug...
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 MoreChristopher Winslett
Christopher Winslett
Note: We have additional articles in this Postgres AI series . In the past month at Crunchy Data , we have talked to a steady stream of customers & community folks wanting to know how to augment their data platforms for AI. Fortunately, Postgres is equipped, nearly out of the box, and ready for the task of storing and querying this data. Through the magic of OpenAI’s API we can easily send data for classification and return the values. Alongside this post, I created a sample code-base and dat...
Read MoreChristopher Winslett
Christopher Winslett
JSON is everywhere, even in your SQL database. Since Postgres added JSON support 10 years ago, we've seen widespread adoption. When JSON support was first released in 2012 the implementation was a fairly flat representation of the JSON data type. It had limited querying and indexing capabilities. Craig , who was active in early JSON discussions with Postgres contributors, admits "Postgres cheated" with these early implementations. As JSONB has been introduced and adopted, I think you can see...
Read MoreChristopher Winslett
Christopher Winslett
TL;DR : keep your data-layer simple, you'll need the agility it offers when budgets get stretched. Over the past 12 years of helping people run databases for their businesses, I have discovered that there are only 2 financial phases in the lifecycle of a database: • Phase 1 : Absolute costs are low, thus marginal costs do not matter. • Phase 2 : Absolute costs are high, thus marginal costs are all that matter. Phase 1 : Absolute costs are low, thus marginal costs do not matter. Phase 2 : Absolut...
Read MoreChristopher Winslett
Christopher Winslett
Have you traced a sql statement back to the source application? Of course you have! Was it time consuming? Probably! SQL queries can hide in plain sight. If you've read through any of Crunchy's advice on query performance or healthy databases you know there's a goldmine of optimization waiting for you in your database. When using an ORM, the queries are obfuscated, and tracing a specific query to the application code can be challenging. Using Ruby as an example below (BTW, I <3 Ruby; I will...
Read More