Latest posts from 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

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

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.

The easy CRUD Basics with ActiveRecord

"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"

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 JSON datasets
• rolling log using MongoDB’s capped collections
• rapidly updating JSON documents using MongoDB’s atomic updates

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 data packet here

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

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

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

For many developers, databases are basically magic. Like Penn & Teller, this blog post is set to break the illusion. Databases are just like any other code, they have algorithms and processes. These algorithms and processes are meant to improve performance, but can cause limitations if they are not expected.

Disclaimer