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

Reaching for CREATE INDEX without thinking gives you a B-tree every time — and a B-tree is the wrong tool for full-text search, JSONB, arrays and billion-row time-series tables. Knowing the four index types PostgreSQL offers is the difference between a query that flies and one that ignores your index entirely.

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 — and the deciding question is always the same: what shape is the data, and what shape is the query?

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.