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

SQL Server MariaDB MySQL 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 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.

April 1, 20266 min read

GIN vs GiST in Postgres: Which Index Type to Reach For

GIN and GiST cover the cases B-tree cannot. They overlap, they have very different cost profiles, and the docs are vague about which to use. Here is the practical answer.

March 31, 20265 min read

BRIN Indexes in Postgres: Tiny, Underrated, and Sometimes Exactly Right

BRIN indexes are 100x smaller than B-tree on the right data. They are also useless on the wrong data. The deciding factor is whether the table is naturally sorted.

March 30, 20265 min read

Foreign Keys in Postgres: The Indexes the Database Will Not Make For You

Foreign key constraints check the parent side automatically. The child side is your problem. Forgetting this index is one of the most common silent performance bugs.

March 29, 20267 min read

Expression Indexes in Postgres: The 30-Minute Lesson That Cost Us Three Days

Expression indexes are powerful but exacting. If the query expression and index expression do not match, the index you trusted may not be used.