5 min read
An OS upgrade changed glibc's collation, your text indexes were now sorted by rules that no longer match, and PostgreSQL had no idea. Queries return wrong results and unique constraints let duplicates in.
Database Topic Archive
Indexes Articles
Index design, index debt, reindexing, constraint indexes, and evidence-driven DDL.
4 min read
An index that had grown to three times the size of its table was the reason a lookup got slow. B-tree indexes bloat from page splits and churn, and the fix is more nuanced than 'just REINDEX'.
3 min read
Average latency looked great while one tenant's pages timed out. Tenant skew, plan caching against the wrong tenant, and noisy neighbors are the things that actually break multi-tenant Postgres.
3 min read
JSONB shipped us fast and then quietly got slow. The lessons that stuck: index for your real query shape, watch TOAST on wide documents, and stop selecting the whole blob.
3 min read
Indexes speed reads and tax writes. Production tuning means finding indexes that help real queries, indexes that nobody uses, and indexes that make every insert slower.
3 min read
An index-only scan that still hammered the heap taught me the plan name lies. The real story is the visibility map, vacuum health, and whether the index actually covers the query.
5 min read
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.
5 min read
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.
5 min read
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.
5 min read
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.