PostgreSQL in Practice

PostgreSQL Index Families

PostgreSQL index access methods implement different operator strategies: B-tree, hash, GIN, GiST, SP-GiST, and BRIN are not interchangeable. Selection begins with predicates and operator classes, then weighs false positives, heap access, write cost, WAL, and maintenance.

B-tree is the default for ordered scalar predicates.
Hash is intentionally narrow.
GIN inverts multivalued content.
GiST and SP-GiST are frameworks.
BRIN summarizes block ranges.
Index refinements still have semantic gates.

Access-method and operator-class matrix
Method	Typical predicates/data	Loss/recheck	Primary cost
B-tree	=, range, ORDER BY, scalar uniqueness	normally exact	page splits, random insert locality, duplicate maintenance
Hash	equality only	exact match then visibility	single-purpose index
GIN	array/JSONB/tsvector membership	class/operator dependent; bitmap may be lossy	many entries, pending list, write/WAL
GiST	ranges, geometry, KNN, exclusion	often consistent-function recheck	overlap and split quality
SP-GiST	tries/quadtrees/radix partitioning	class dependent	data distribution fit
BRIN	correlated values over large heaps	lossy range recheck	false-positive blocks, summarization

Indexes for measured commerce predicates

CREATE INDEX orders_open_customer_placed
  ON commerce.orders (customer_id, placed_at DESC) INCLUDE (status)
  WHERE status IN ('NEW','PAID');
-- products_attributes_gin was defined with jsonb_path_ops in the types topic.
CREATE INDEX orders_placed_brin
  ON commerce.orders USING brin (placed_at) WITH (pages_per_range = 64);

SELECT * FROM pg_stat_user_indexes
WHERE schemaname = 'commerce' ORDER BY idx_scan DESC;