Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Christopher 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 MoreCraig Kerstiens
Craig Kerstiens
Today, we wanted to address some basic principles for better managing data architecture. Postgres is well regarded as a database for traditional system of record. More recently we've been fielding questions on what else can it do, such as: Can it be good for analytics and metrics? The short answer is "yes". When applications expand outside their standard system of record, they add in new types of data and data stores, which introduces complexity managing multiple types of systems. Some common wo...
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 MoreGreg Sabino Mullane
Greg Sabino Mullane
This article will contain spoilers both on how I solved 2022 Day 16's challenge "Probscidea Volcanium" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. My solutions may not be the "best" solution, as the goal is to provide a quick solution. We've also loaded a tutorial for Day 16's challenge if you want to try it with a pre-loaded data set....
Read MoreGreg Sabino Mullane
Greg Sabino Mullane
In an earlier post, I went into a lot of detail about unlogged tables . But tables are not the only thing to get the unlogged treatment - as of version 15 of Postgres, sequences can be unlogged as well! If you want to create your own, it's simply a matter of adding the keyword to your statement: The use case for unlogged sequences in Postgres is primarily to keep the sequence data for an unlogged table out of the WAL stream. Although unlogged tables provide a significant performance boost,...
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 MoreCraig Kerstiens
Craig Kerstiens
Is your database ready for production? You've been building your application for months, you've tested with beta users, you've gotten feedback and iterated. You've gone through your launch checklist, email beta users, publish the blog post, post to hacker news and hope the comments are friendly. But is your database ready for whatever may come on launch day or even 2 months in? Here's a handy checklist to make sure you're not caught flat footed. • Backups❓ • High availability❓ • Logs properly co...
Read MorePaul Ramsey
Paul Ramsey
It's the easiest thing in the world to put a timestamp on a column and track when events like new records or recent changes happen, but what about reporting? Binning data for large data sets like time series is a great way to let you group data sets by obvious groups and then use SQL to pull out a query that easily works in a graph. Here's some PostgreSQL secrets that you can use to build up complete reports of time-based data. Earthquakes are a natural source of time-stamped data, and Crunchy B...
Read MorePhilip Hurst
Philip Hurst
Backups in the database world are essential. They are the safety net protecting you from even the smallest bit of data loss. There’s a variety of ways to back up your data and this post aims to explain the basic tools involved in backups and what options you have, from just getting started to more sophisticated production systems. and are tools designed to generate a file and then allow a database to be restored. These are classified as logical backups and they can be much smaller in size than...
Read More