GIN, GiST, BRIN, and Friends

A B-tree answers one kind of question: is this value equal to, less than or greater than that one. A jsonb containment check, an overlapping time range, a nearest-neighbour search and a day-slice of a billion-row time-ordered table are four different questions, and Postgres ships a separate access method for each. Choosing correctly moves a query from ten seconds to ten milliseconds, or an index from 40 GB to 90 MB.

The mistake this topic exists to prevent is picking by reputation. GIN is not "the advanced one" and BRIN is not "the small one you should use when disk is tight". Each implements a specific set of operators, and the only question that matters is which access method knows how to evaluate the operator your query already contains.

GIN — the Inverted Index

GIN stores pairs of a key and a posting list: for each distinct element found inside the indexed values, a sorted list of the rows containing it. The same row appears in many posting lists, because a document or an array contains many elements, and each key value is stored exactly once no matter how many rows hold it. That is the inverted index of every search engine, built into the database.

CREATE INDEX products_attributes_idx
    ON products USING gin (attributes jsonb_path_ops);

SELECT id, name FROM products
 WHERE attributes @> '{"brand": "Lume"}';

CREATE INDEX products_tags_idx ON products USING gin (tags);
SELECT id FROM products WHERE tags @> ARRAY['organic'];

The containment query becomes a lookup of one key instead of a scan of 200,000 documents. The operator class is the decision worth understanding: the default jsonb_ops supports containment, the two jsonpath operators, and the key-existence family ?, ?| and ?&. jsonb_path_ops supports only containment and the jsonpath operators, and the trade is fewer operators for better performance on the ones it keeps and a considerably smaller index. If the application only ever asks "does this document contain that", take the smaller one. The same access method covers arrays through array_ops, which handles overlap, containment both ways and equality, so tags is indexable without a second design decision.

It comes with two limits. GIN entries hold fragments of the original value rather than the value itself, so a GIN index can never support an index-only scan — the heap is always read. And insertion is expensive by construction, because one row produces one entry per element inside it. Version 18 at least builds them faster: GIN indexes can now be created in parallel.

GiST — the Extensible Tree

GiST is not an index so much as a framework for building balanced trees over "does this contain, overlap, or come near that". Range types use it for &&, geometry uses it for the whole family of positional operators, and both PostGIS and nearest-neighbour ordering are built on it — ORDER BY location <-> point LIMIT 5 reads five entries rather than sorting every row by distance.

It is also the access method behind every EXCLUDE constraint, so Cartwheel already has one whether or not anybody chose it: the rule that no courier is double-booked is EXCLUDE USING gist (courier_id WITH =, shift WITH &&), and the constraint is enforced by the index it creates. GiST searches are lossy: the tree returns candidates that the executor rechecks against the heap, which makes it cheaper to write than GIN and less precise to read. Drop the index and you have dropped the rule.

BRIN — Tiny, and Only When Order Correlates

BRIN indexes a table's physical layout rather than its rows. The table is divided into block ranges of pages_per_range pages, 128 by default and so one megabyte of table per range, and the index stores a summary per range, for a sortable type the minimum and maximum value it contains. A query with a range predicate reads the summaries, discards every range whose minimum and maximum cannot contain a match, and scans only the surviving ranges.

CREATE INDEX delivery_events_occurred_brin
    ON delivery_events USING brin (occurred_at)
    WITH (pages_per_range = 128, autosummarize = on);

SELECT count(*) FROM delivery_events
 WHERE occurred_at >= '2026-08-12'
   AND occurred_at <  '2026-08-13';

delivery_events takes 4 million rows a day and nothing ever updates one, so rows arrive in occurred_at order and stay there. One day's data occupies a contiguous run of block ranges, which is exactly the shape BRIN was built for: the 40 GB B-tree on that column becomes roughly 90 MB, and a day-range query reads a few thousand summaries plus the pages that can actually match. The index is three orders of magnitude smaller than the thing it replaced and costs almost nothing to maintain.

The dependency is total, and it is on physical order, not on the data. Index a column whose values are scattered across the table and every range's minimum-to-maximum span covers the whole domain, so every range matches, every page is read, and the index has added work while removing none. The same collapse happens retroactively if the table is rewritten in a different order — a VACUUM FULL or a repack can destroy the correlation the index depends on without touching the index itself.

Freshly appended pages are the other trap. When a new page falls outside the last summarized range, that range acquires no summary until a summarization run happens, and an unsummarized range is always scanned. On an append-only table that means the newest data, which is the part the dashboard queries most, is the part the index does not help with. Setting autosummarize fixes it, and the parameter is off by default; brin_summarize_new_values() does it on demand.

Hash, SP-GiST, and bloom

Hash indexes store a 32-bit hash of the value and therefore answer equality and nothing else — no ranges, no ordering, no unique constraints. Their real problem was historical: before 10 they were not WAL-logged, which meant they did not survive a crash and did not exist on replicas. Version 10 added write-ahead logging and removed the warning from the manual. They are now defensible for pure-equality lookups on long keys, where hashing a 200-byte string beats storing it, and unremarkable everywhere else.

SP-GiST covers the non-balanced structures: quadtrees, k-d trees and radix tries, which suits prefix-shaped and spatially partitioned data. The bloom contrib module builds a signature index over many columns at once, aimed at tables with a dozen attributes that get queried in arbitrary combinations; it answers equality only, ships operator classes for int4 and text, cannot be unique, cannot find nulls, and always rechecks the heap because signatures produce false positives. It is a narrow tool and genuinely the right answer for the wide-table-arbitrary-filter shape that would otherwise need eight separate B-trees. One signature per row, whatever the number of columns folded into it.

Choosing by the Operator

The decision procedure is not "which index is fastest". It is: look at the operator in the query, then pick the access method that has an operator class implementing it.

=  <  >  BETWEEN  IN            ->  btree
@>  ?  ?|  ?&  &&  (jsonb, arrays) ->  gin
@@  (tsvector @@ tsquery)         ->  gin
&&  (ranges)   <->  (geometry)   ->  gist
=   only, on a long key           ->  hash
range predicate on a huge,
naturally ordered column          ->  brin

Read that list backwards when you are stuck: if you cannot name the operator your query uses, you are not yet ready to choose an index. And confirm the choice rather than assuming it, because an index that exists for the right operator can still be bypassed and the operator can end up evaluated as a filter after the fact. The plan says which of the two happened, on the line under the scan node, and Chapter 9 reads that line properly.

What Each Costs to Maintain

GIN is the expensive one. Every row produces many entries, so ingestion slows and vacuum has more to do. Its fastupdate setting, on by default, softens that by parking new entries in an unsorted pending list and merging them in bulk later — when the table is vacuumed or autoanalyzed, when gin_clean_pending_list() is called, or when the list passes gin_pending_list_limit, which defaults to 4 MB. That trades steady cost for occasional spikes, and searches must scan the pending list as well as the tree. Turn fastupdate off when consistent response time matters more than insert speed.

GiST is cheaper to write than GIN and pays on the read side through rechecks. BRIN maintenance is close to free, a summary per megabyte of table updated in place, with summarization lag as its only real cost. B-tree remains the cheapest per write of the exact methods, and it is still the default. The other four exist for questions it cannot express.