Indexes PostgreSQL Articles

Two weeks after a routine OS upgrade, inserts started failing with duplicate-key errors on values that did not exist. The culprit was a glibc collation change that had quietly scrambled every btree on text.

An index-only scan is only index-only when the visibility map says the heap page is all-visible. How to read Heap Fetches in EXPLAIN, build covering indexes with INCLUDE, and keep vacuum honest.

The rule 'never use hash indexes' came from a real scar: before PG10 they skipped WAL, so replicas and crash recovery silently lost them. That was fixed eight major versions ago, and the reputation never recovered.

One RLS policy with a memberships subquery turned our indexed tenant lookups into per-row nested loops overnight — p95 went from 9 ms to 1.4 seconds. Here's how we found it and the pattern that fixed it.

Btree indexes bloat quietly as updates and deletes churn. Here is how to measure bloat with pgstattuple, why it wrecks cache hit rates, and how REINDEX CONCURRENTLY fixes it without taking the table down.

We replaced a 40 GB B-tree on created_at with a 40 MB BRIN and the time-range queries got faster, not slower. Here is how to pick between B-tree, BRIN, GIN, GiST, and hash by query shape.

A four-value status column over 300 million rows carried an 11.8 GB index before PostgreSQL 13. One REINDEX later it was 1.9 GB, same queries, same plans — posting lists did that, and the bloat math has not been the same since.

Two API requests forty milliseconds apart booked the same room: both ran the availability SELECT, both saw zero conflicts, both inserted. EXCLUDE USING gist deleted that race window entirely.

Two weeks after a routine OS upgrade, inserts started failing with duplicate-key errors on values that did not exist. The culprit was a glibc collation change that had quietly scrambled every btree on text.

Btree indexes bloat quietly as updates and deletes churn. Here is how to measure bloat with pgstattuple, why it wrecks cache hit rates, and how REINDEX CONCURRENTLY fixes it without taking the table down.

A BRIN index can be a thousand times smaller than the btree it replaces, or it can sit there doing nothing while costing write overhead. Physical correlation makes the difference.

Both engines can combine multiple single-column indexes to answer one query, but MySQL's index_merge and PostgreSQL's bitmap scans differ in reliability and reach. That difference changes how you design composite indexes after a migration.

InnoDB stores the table inside the primary key; PostgreSQL stores rows in a heap with every index as an equal citizen. That one storage decision changes index design, covering queries, and primary key sizing.

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

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

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.