Hatchet · The startup's Postgres survival guide

2026-07-22<br>The startup's Postgres survival guide

A guide to preventing Postgres from toppling over.

Alexander BelangerCo-FounderHatchet

Over the past half year or so, I’ve been writing an internal doc for our engineers trying to distill two years of Postgres battles into a somewhat cohesive document. While I love the Postgres manual, I find it’s hard to turn to when shit hits the fan because it’s just so darn comprehensive. I thought this might be useful for others and would appreciate feedback (or other tidbits that you’ve learned running Postgres in production).

Before starting Hatchet, while I was familiar with SQL, the extent of my knowledge was basically: if a query is slow, you need an index. That’s the starting point for this doc; I’m going to assume you’re familiar with SQL basics, rows, tables, and know roughly what an index is.

And if Claude is writing all of your queries, this might be a waste of time! I recommend supabase/agent-skills

A quick note on ORMs

This guide should still be useful, but you might need to translate some of these tips into your ORM of choice. Lots of optimizations as you scale just aren’t possible with ORMs unless you can break past the abstraction layer and write SQL. You can do this gracefully or non-gracefully; Prisma TypedSQL or equivalents look interesting for this. We use sqlc at Hatchet which gets us very similar behavior; highly recommend if you’re a Go stack.

Table of contents

The simple stuff: good reads, writes and schemas

Writing a good schema

Writing good read queries

Writing performant joins

Compound indexes and aligning ORDER BY to your indexes

Writing good write queries

Migrations

Connection management

Intermediate: the query planner, bulk updates, and autovacuum

Introducing the leakiest of abstractions, the query planner

Sometimes it just makes sense to seq scan

Writing lots of data

Default autovacuum settings can kill your database

Other types of bloat

Some advanced stuff

FOR UPDATE SKIP LOCKED

Partitioning

Tricks for large table migrations

The simple stuff: good reads, writes and schemas

Let’s start with the basics: queries and schemas at low volume.

Writing a good schema

After you’re deployed, schemas are by far the hardest to change moving forward, so it’s worth spending some time on them. I’d recommend building your schema iteratively: start with a rough approximation for your tables and primary keys, then write some queries on those tables based on your application needs. You can approximate this with some questions: Is this a high-read and/or high-write table? What are the most common filters on reads? Which columns am I updating the most?

If you want to be more formal about it, you can look into database normalization into 1NF/2NF/3NF, but I’ve found normal forms to sometimes be at odds with query efficiency and ease of use, which is critical when you’re moving fast—sometimes it’s just easier to dump data into a jsonb column.

My rules of thumb for schemas are:

Use identity columns (auto-incrementing integers, slightly more performant than bigserial) or built-in UUIDs for primary keys

Always use timestamptz

Always use primary keys

Use foreign keys with cascading deletes for low-volume tables, particularly where database consistency and correctness are important. Careful at higher volume.

Writing good read queries

Let’s start with SELECT queries. A useful—albeit slightly inaccurate—mental model for fast selects is: under the hood, Postgres is either going to find a single row in a table very quickly, or it’s going to read every single row in your table using something called a sequential scan 😞.

It’s going to find a single row very quickly when you filter by:

An explicit index

A unique constraint (just a special case of index)

A primary key (these are automatically indexed in Postgres)

Indexes by default use a btree implementation. It’s most helpful to think of indexes as just another table in Postgres, with data stored in a specific format which is optimized for lookups (more on this later). These trees are great because finding a single row happens in approximately log(n) time, where n is the number of rows in the table—in other words, really fast.

When Postgres can’t use an index, it’ll use something called a sequential scan, or seq scan. Seq scans are much slower than index lookups, but modern databases are so fast at loading rows into memory that you probably won’t even notice at first: seq scans on tables with less than 20k rows are pretty much instant.

Writing performant joins

For inner joins, there’s rarely an argument for not using primary keys as the inner join; it usually speaks to a schema design or normalization problem. Treat ON clauses with the same respect as a WHERE clause—the same principles apply. Use an index.

Compound indexes and aligning ORDER BY to your indexes

Often the first slow query in your application will be a list query...