PostgreSQL Field Notes

Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that make PostgreSQL easier to operate.

PostgreSQL Topic Hubs

PostgreSQL Guide (Pillar)

pgvector Monitoring Query Optimizer Sizing Slow Query Monitoring Query Monitoring

PostgreSQL Tools

HNSW Tuning Lab Plan Autopsy Index Rollout

Tool Comparisons

Compare PostgreSQL monitoring tools

April 26, 202612 min read

Vector Databases: How I Decide Between pgvector and a Dedicated Vector Store

Two teams. One went straight to Pinecone for $4,200/month they didn't need; the other stayed on pgvector past the point it was wise. Here is the framework I use now.

April 15, 20268 min read

Reading EXPLAIN ANALYZE Output Without Drowning

EXPLAIN ANALYZE produces a wall of indented text and three numbers per node. Here is the actual reading order I use, and what each number means in practice.

April 14, 20266 min read

Nested Loops in Postgres: The Plan Type People Fear For the Wrong Reasons

A nested loop join is fast or slow depending on the row counts. Here is when it is the right plan, when it is not, and how to read the EXPLAIN output to tell.

April 13, 20266 min read

Hash Joins in Postgres: When to Pick Them, When the Planner Picks Them For You

Hash joins are the workhorse for large equi-joins. They are also the join type that quietly spills to disk when work_mem is too small, turning a fast query into a slow one.

April 12, 20266 min read

Sort Performance in Postgres: When work_mem Decides Everything

A sort that fits in `work_mem` is fast. A sort that does not is 10x slower. Here is how to tell which case you are in and what to do about it.

April 11, 202612 min read

The PostgreSQL Query Planner: How It Works and When It Gets It Wrong

What the planner does, the statistics that drive its decisions, and the diagnostic workflow for the moments when it picks the spectacularly wrong plan.

April 10, 20266 min read

Postgres CTEs: When WITH Helps and When the Optimizer Wishes You Had Not Used It

CTEs read beautifully and sometimes cost beautifully too. The behavior changed in Postgres 12, and most teams I work with have not updated their mental model.

April 9, 20267 min read

Window Functions in Postgres: Powerful, and Often Slow Until They Are Not

Window functions are the right tool for ranked-per-group queries. They are also surprisingly fragile in performance. Here is how to write them so they actually scale.

April 8, 20266 min read

Making GROUP BY Fast: The Two Indexes That Make the Difference

GROUP BY queries either run instantly because Postgres can stream from a sorted index, or they sort 50GB on disk. Here is how to put yourself in the first camp.

April 7, 20265 min read

DISTINCT ON in Postgres: The Feature More Teams Should Be Using

DISTINCT ON is a Postgres-specific shortcut for "give me one row per group, sorted some way." It is fast, it is pleasant, and the version in your head is probably wrong.

April 6, 20266 min read

Keyset Pagination in Postgres: The Pagination That Stays Fast

OFFSET pagination gets slower the deeper you go. Keyset pagination is constant time. Here is how to switch over and the cases where you cannot.

April 5, 20265 min read

COUNT(*) at Scale in Postgres: When the Honest Answer is "It's Complicated"

A `SELECT count(*)` on a small table is instant. On a 500M-row table it can take a minute. Here is what to do when the count itself is the slow query.