The events table held 1.1 billion rows, about 900 GB on disk, and it carried a 40 GB B-tree on created_at. Every night at 03:00 a partition-detach job ran a range scan over the oldest day, and every night that scan pulled tens of thousands of index pages through shared_buffers just to find rows it could have found by looking at the table's physical layout. We dropped the B-tree, built a BRIN in its place — 40 MB, one thousandth of the size — and the same query got faster. Not because BRIN is magic, but because 40 MB fits in RAM and 40 GB does not. That week I stopped treating "index" as a synonym for "B-tree" and started treating the index type as a design decision.
What follows is each access method PostgreSQL ships, organized by the query shape it serves — plus the two other war stories that taught me the lesson: a jsonb tags column only GIN could tame, and a booking system where GiST stood between us and double-booked rooms.
Why was a 40 MB BRIN faster than a 40 GB B-tree?
Because the B-tree's size was itself the bottleneck. A range query on created_at has to walk B-tree leaf pages to collect row pointers, and 40 GB of leaf pages do not stay cached — every night the range scan evicted useful pages from shared_buffers, then read index blocks and heap blocks from disk. The BRIN, at 40 MB, sat permanently in memory. The scan read a handful of summary entries, learned which block ranges could contain the day's rows, and went straight to sequential heap reads of a contiguous region.
BRIN stores the minimum and maximum value of the indexed column for each range of table blocks — 128 blocks per range by default. When a query asks for created_at >= '2026-07-01', PostgreSQL checks each range's min/max and skips every range whose interval cannot overlap the predicate. That only helps if physically adjacent rows have similar values — exactly what an append-only table gives you, because rows arrive and are written in time order.
The cost sheet is honest: BRIN is useless the moment physical order stops correlating with the column. A table that gets heavy random UPDATEs, or one where the indexed column has no relationship to insert order, will produce min/max ranges that all overlap each other, and every scan degenerates into "check everything, recheck everything." The index is tiny and still worthless.
Creating it is one line:
CREATE INDEX events_created_at_brin
ON events USING brin (created_at)
WITH (pages_per_range = 32);We tuned pages_per_range down from the default 128 to 32. Smaller ranges mean finer-grained summaries — more index entries, slightly bigger index, but far fewer false-positive block ranges to scan and recheck. For a table where a typical query touches one day out of a year, that trade was obviously right. There is no formula here; we tested 8, 32, and 128 against the real query mix and measured.
When is the default B-tree still the right choice?
Almost always. B-tree handles equality and range predicates (=, <, <=, >, >=, BETWEEN, IN), plus ORDER BY on the indexed columns, and it does so in logarithmic time regardless of how the table is physically ordered. If you are indexing a foreign key, a status column, an email address, a tenant id — anything with ordinary equality or ordered-range lookups — B-tree is the answer and you should not overthink it. In my experience it covers roughly 90% of the indexes a schema needs.
CREATE INDEX orders_tenant_created_idx
ON orders (tenant_id, created_at DESC);The mistakes with B-tree are not choosing it but misusing it: indexing low-cardinality boolean columns alone, stacking single-column indexes where one composite index would serve, or keeping a 40 GB B-tree alive on an append-only table out of habit — which was exactly our crime. If you want the deeper playbook on composite column order, covering indexes, and index-only scans, the companion piece on PostgreSQL index optimization goes there. This article is about the rarer moment when B-tree is the wrong tool entirely.
How do you verify a table is a good BRIN candidate?
Check the correlation statistic in pg_stats before you build anything. PostgreSQL's planner already tracks, per column, the statistical correlation between physical row order and logical value order. A value near 1.0 (or near -1.0 for reverse order) means the column is strongly correlated with storage order — BRIN territory. A value near 0 means random scatter — BRIN will be useless.
SELECT attname, correlation
FROM pg_stats
WHERE schemaname = 'public'
AND tablename = 'events';Our created_at column reported a correlation of 0.998. That number was the entire justification for the migration. Two caveats: the statistic is only as fresh as the last ANALYZE, so re-check periodically — pg_stats will quietly tell you when your BRIN is rotting. And on a freshly rebuilt table (after CLUSTER or a dump/restore) correlation can look perfect even for columns that will scatter under normal write load, so judge it against how the table actually grows.
Why does jsonb need GIN, and what does it cost?
Because a containment query has no ordering to exploit. The second war story: a products table with a tags jsonb column, queried with the containment operator @> — "give me every product whose tags contain this key/value pair." A B-tree on a jsonb column indexes the whole document as one opaque value; it can answer equality on the entire document and nothing else. It cannot look inside.
GIN is an inverted index: it stores individual keys and array elements, each pointing at the set of rows containing them. That is exactly what @> on jsonb, && (overlap) on arrays, and @@ on tsvector need — they are all "does this composite value contain/match any of these pieces" questions.
CREATE INDEX products_tags_gin
ON products USING gin (tags);
-- the query it serves
SELECT id, name
FROM products
WHERE tags @> '{"color": "red", "on_sale": true}';The cost sheet, because there is always one: GIN indexes are large — often a substantial fraction of the table's own size — and expensive to maintain on write, since every insert or update can touch many index entries, one per key or element. PostgreSQL softens this with the pending list: with fastupdate enabled (the default), new entries go to an unordered list and get merged into the main index in bulk later, by autovacuum or when the list exceeds gin_pending_list_limit (default 4 MB). Writes stay fast, but queries pay — every GIN scan must also walk the pending list, so a swollen list means slower reads until it is flushed.
In production we watched exactly that happen on a table ingesting about 2,000 jsonb rows per second: read latency on the GIN-backed endpoint crept up through the day and snapped back after each vacuum. Raising gin_pending_list_limit delayed the flushes and made reads worse; lowering it flushed more often and made writes worse. There is no free setting, only a dial. If jsonb is central to your schema, the article on jsonb performance, GIN, and TOAST covers the operator-class choices (jsonb_ops versus jsonb_path_ops) that decide how big that GIN index gets.
When do you need GiST — and should you ever use hash?
You need GiST when your data type has no total order but does have overlap, containment, or distance: ranges, PostGIS geometries, full-text documents, trigram similarity. GiST is a balanced tree like B-tree, but its internal entries describe predicates ("everything under me falls within this bounding box/interval") rather than ordered key ranges. It is also lossy: internal entries can over-approximate, so PostgreSQL must recheck matched heap rows against the real predicate. When you see "Rows Removed by Index Recheck" in EXPLAIN ANALYZE, that is the lossiness showing itself — the EXPLAIN ANALYZE deep dive shows how to read those counters.
The third war story is my favorite use of GiST because there is no substitute for it. A booking system must not allow two reservations for the same room at overlapping times. Application-level checks race; two concurrent transactions both see "no conflict" and both insert. PostgreSQL solves this at the data level with an exclusion constraint, and exclusion constraints on range overlap are backed by GiST:
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings
ADD EXCLUDE USING gist (
room_id WITH =,
during WITH &&
);Here during is a tstzrange, && is range overlap, and the btree_gist extension teaches GiST how to index the scalar room_id so the constraint reads "no two rows may have the same room AND overlapping time ranges." Any insert that violates it fails with an exclusion-constraint violation, under any concurrency, with no application coordination. Double bookings became structurally impossible. The price is the same as every GiST index: writes maintain a more complex tree than B-tree, and large GiST builds are slow.
And hash indexes? They answer equality and only equality — no ranges, no ordering, no ORDER BY. They have been WAL-logged and crash-safe since PostgreSQL 10, so the old "never use hash, it will corrupt on crash" advice is outdated. But the honest answer is that they rarely beat B-tree in practice: a B-tree answers the same equality lookups at essentially the same speed, plus everything else. I have benchmarked hash against B-tree twice on equality-heavy workloads and kept the B-tree both times. Deploying hash is almost never necessary.
How do you catch the wrong index type in production with MonPG?
You catch it by watching the symptoms, because the planner rarely errors — it just gets slow. The tells we learned to watch: an index whose on-disk size is a large fraction of the table (our 40 GB B-tree was the flag), buffer hit ratio dropping during specific scheduled queries (the nightly range scan evicting shared_buffers), and index scans whose row counts are dwarfed by their heap fetches (a BRIN whose correlation has rotted, or a lossy GiST rechecking half the table).
MonPG surfaces exactly these counters: index sizes and their growth over time, buffer hit ratio per query pattern, and per-statement read statistics from pg_stat_statements. The fix for our events table started from a MonPG graph, not from theory — the nightly 03:00 query showed a hit-ratio crater and a read-bytes spike that made no sense for a query touching 0.1% of the rows. That graph sent us to pg_stats to check correlation, and the 0.998 did the rest.
The decision rule, one more time because it is the whole article: start from the query predicate, not the column type. Equality and ranges on ordered data — B-tree. Time-range scans on an append-only table — BRIN, after checking correlation. Containment on jsonb, arrays, or tsvector — GIN, with a write-cost budget. Overlap of ranges or geometries, or an exclusion constraint — GiST. Equality alone — B-tree again, honestly, even though hash exists.