Andrew Atkinson Software Engineer blog about PostgreSQL, Ruby on Rails, Elasticsearch, Kubernetes, and Vim. Andrew is the Author of Ruby bestseller High Performance PostgreSQL for Rails
In this post we’ll look at a recipe for adding and removing big indexes on big tables. The create operation can take hours, so it’s helpful to have a plan in place for this to run in the background and to monitor progress. We’ll assume these operations take place at the same time as the client application queries, which means they run concurrently and we’ll need to monitor the resources being…
📌 Overview Average execution time for high calls/min insert queries dropped significantly for a handful of tables after switching primary key columns to uuidv7() values. These databases were running Postgres 18.4 and kept their uuid primary key data type, but changed the generation function for new inserts from either v1 or v4, to v7. One thing to note is this DDL change requires an exclusive…
📌 Overview Ruby on Rails has helped make it possible to scale out the database layer, meeting the demands of millions of Aura Frames customers enjoying their digital photo frames. In late 2025, the team added additional primary databases to expand capacity for peak write and read load ahead of Christmas Day, the busiest day of the year for the company. Rails manages queries and schema changes for…
📌 Overview On Christmas Day 2024, Postgres infrastructure powering the Aura Frames API had problems under peak load, being unavailable for three hours and disrupting the experience for new customers. The team knew it would need improvements to handle the surge for Christmas 2025 and beyond. One year later, much of the resource intensive data access was reworked, the Postgres infrastructure was…
The Postgres community releases Beta versions, and with Docker it’s been easier than ever to configure pre-release versions to use and test. With the recent announcement of PostgreSQL 19 Beta 1 , it’s a great time to do that. Let’s get an instance up and running and test some of the new capabilities in Postgres 19. Pre-Release Versions of Postgres with Docker First, you’ll need to install Docker…
In this post we’ll cover two types of Postgres internals. The first internal item is an “SLRU.” The acronym stands for “simple least recently used.” The LRU portion refers to caches and how they work, and SLRUs in Postgres are a collection of these caches. SLRUs are small in-memory item stores. Since they need to persist across restarts, they’re also saved into files on disk. Álvaro Herrera 1…
Introduction Over the last decade, when working on databases with UUID Version 4 1 as the primary key data type, these databases have usually had bad performance and excessive IO. UUID is a native data type in Postgres stored as binary data. Various UUID versions are in the RFC. Version 4 has 128 bits, with 122 being random, obfuscating information like when the value was created or where it was…
Introduction In this post, we’ll cover some database design principles and package them up into a catchy mnemonic acronym. Software engineering is loaded with acronyms like this. For example, SOLID principles describe 5 principles, Single responsibility, Open-closed, Liskov substitution, Interface segregation and Dependency inversion, that promote good object-oriented design. Databases are loaded…
Introduction Much of the time taken processing HTTP requests in web apps is SQL queries. To minimize that, we want to avoid unnecessary and duplicate queries, and generally perform as few queries as possible. Think of the work that needs to happen for every query. The database engine parses it, creates a query execution plan, executes it, and then sends the response to the client. When the…
Introduction If you’ve created web apps with relational databases and ORMs like Active Record (part of Ruby on Rails), you’ve probably experienced database performance problems after a certain size of data and query volume. In this post, we’re going to look at a specific type of problematic query pattern that’s somewhat common. We’ll refer to this pattern as “Big IN s,” which are queries with an…