Indexes Articles

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.

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

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.

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.

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.