B-tree, GIN and BRIN: Choosing the Right PostgreSQL Index

One Size Does Not Fit All

PostgreSQL ships several index access methods. Pick wrong and you either get no speedup or you pay 10× the disk and write cost for nothing. The three that matter most day to day are B-tree, GIN and BRIN.

B-tree — The Default Workhorse

Used automatically by CREATE INDEX. Perfect for equality and range comparisons on scalar values, and for ORDER BY.

CREATE INDEX idx_orders_created ON orders (created_at);
-- helps:  WHERE created_at > now() - interval '7 days'
--         ORDER BY created_at DESC
--         WHERE customer_id = 42  (equality)

Supports multicolumn indexes, where column order matters: an index on (a, b) helps WHERE a = ? and WHERE a = ? AND b = ?, but not WHERE b = ? alone.

GIN — For Values That Contain Many Sub-Values

Generalized Inverted Index. Use it when one column holds many searchable items: JSONB documents, arrays, and full-text search vectors.

-- JSONB containment
CREATE INDEX idx_doc_data ON documents USING gin (data jsonb_path_ops);
-- helps:  WHERE data @> '{"status": "active"}'

-- Full-text search
CREATE INDEX idx_articles_fts ON articles
  USING gin (to_tsvector('english', body));
-- helps:  WHERE to_tsvector('english', body) @@ plainto_tsquery('postgres')

-- Array membership
CREATE INDEX idx_post_tags ON posts USING gin (tags);
-- helps:  WHERE tags @> ARRAY['sql']

GIN indexes are larger and slower to update than B-tree. If you write often, consider tuning fastupdate and gin_pending_list_limit.

BRIN — Tiny Indexes for Huge, Naturally-Ordered Tables

Block Range INdex. Instead of indexing every row, it stores the min/max value per block range. The result is astonishingly small — often megabytes for a billion-row table — but it only works when the column correlates with physical row order (think append-only logs ordered by time).

CREATE INDEX idx_events_time_brin ON events
  USING brin (created_at) WITH (pages_per_range = 128);
-- helps:  WHERE created_at BETWEEN '2026-01-01' AND '2026-02-01'
-- on an append-only table where rows arrive in time order

A BRIN index can be 1/1000th the size of the equivalent B-tree. On a well-correlated time-series table it gives most of the benefit at a fraction of the storage and write cost. On a randomly-ordered column it is useless.

Quick Decision Table

The Index You Forgot About

The hidden cost of indexes is the ones nobody uses: every unused index still slows every INSERT and UPDATE and bloats backups. PG Monitoring reports index usage (idx_scan) and flags indexes that have not been touched in weeks, plus the missing indexes that would actually help — quantified in milliseconds saved.