Indexes PostgreSQL Articles

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.

Loading millions of rows with single-row INSERTs is the slowest possible choice. COPY, batching, building indexes after the load, and managing WAL turn an overnight job into a coffee break.

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'.

VACUUM FULL would have locked a 200GB table for over an hour in the middle of the day. pg_repack rebuilt it online with only a momentary lock, and nobody noticed. Here's how it works and when to trust it.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

REINDEX is a repair tool, not an indexing strategy. Use it when bloat, corruption, or access pattern churn makes rebuilding cheaper than carrying the old index forward.

Both enforce uniqueness. They look identical in `\d`. The differences only show up when you try to alter the table or hit them with a partial uniqueness rule.

Index work is not about adding more indexes. It is about finding which indexes earn their write cost, which ones duplicate each other, and which missing one is hurting users.

JSONB columns are great. JSONB indexing has a steeper learning curve than the docs admit. Here is what works in production.