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

Browse by topic

pgvector and RAG Indexes Slow Queries Locks and Transactions Backups and Durability Replication and WAL Connections and Pooling CPU, Memory, and I/O Vacuum and Bloat Cloud Providers

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

Planner problems usually start with one wrong estimate. Learn how to spot the first lie in the plan, then fix the statistics, predicate, or index that caused it.

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.

April 4, 202611 min read

How I Actually Find and Fix Slow PostgreSQL Queries

A practical slow-query incident workflow: find the fingerprint, prove the bottleneck, avoid the tempting wrong fix, and ship the smallest change that actually holds.

April 3, 20266 min read

Composite Index Column Order: The Decision That Costs Most People a Day

Two columns in a composite index can go in either order. Which order matters more than people expect, and "index everything both ways" is a worse answer than the right call.

April 2, 20268 min read

Partial Indexes in Postgres: The Right Index Is Often a Smaller One

Partial indexes work when the product mostly asks about a predictable slice of a table. They fail when the predicate is vague, parameterized, or not repeated by the query shape.