PostgreSQL Indexes — B-tree, GIN, GIST, BRIN

B-tree: the foundation

B-tree is the default. Every index created without an explicit type is a B-tree. It is a balanced tree structure where each node holds many keys (~100-1000 depending on key size); internal nodes route searches downward, leaf nodes hold data pointers or actual values. Height grows logarithmically with data size, so even billions of rows need only 3-4 disk seeks.

B-tree excels at three patterns: equality (find user_id = 42), range (created_at > now() - interval '7 days'), and sorted access (ORDER BY). The keys are kept sorted at every level, so a range scan touches contiguous leaf pages in logical order, not random pages.

Trade-off: writes are slower than a hash index because each insert must maintain the tree balance. For read-heavy and mixed workloads, this is fine; for high-churn scenarios (logs, queues), the cost compounds. But the generality is hard to beat: one B-tree index covers equality, range, and sort in a single structure.

-- B-tree is default
CREATE INDEX idx_user_created ON users (created_at);

-- Works for:
-- WHERE created_at = '2026-01-01'  -- equality
-- WHERE created_at > now() - interval '7 days'  -- range
-- ORDER BY created_at  -- sort

GIN: inverted indexes for containment

GIN stands for Generalized Inverted Index. Instead of storing keys and pointing to rows, it inverts the relationship: it stores each element or key value, and for each, maintains a list of rows that contain it. This flips the cost model: lookups become cheap (find the key, read the list), but inserts become expensive (update many entry lists, not just one tree path).

GIN is built for membership and full-text queries. A jsonb column indexed with GIN turns data->>'field' = 'value' into a direct key lookup. A tsvector column turns phrase searches into fast token lookups. Arrays get @> containment checks. The pattern is always the same: you are asking 'does this row contain this element?' not 'find rows matching a range.'

Trade-off: GIN is slower to build and write to, so it suits read-mostly tables. For a high-volume write stream where you rarely search, a GIN will become a bottleneck; for a static knowledge base or a reporting table, it is unbeatable.

-- GIN indexes for jsonb
CREATE INDEX idx_meta_gin ON events USING GIN (metadata);

-- Fast lookups:
SELECT * FROM events WHERE metadata->>'user_type' = 'admin';  -- key exists and equals
SELECT * FROM events WHERE metadata @> '{"status":"error"}';  -- contains

-- Also for arrays:
CREATE INDEX idx_tags_gin ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['python', 'database'];

GIST: generalized search trees for spatial and custom shapes

GIST (Generalized Search Tree) is a framework for indexing non-standard data types. It stores bounding boxes at each node and uses them to prune search space. For a 2D point, the bounding box is a rectangle; for a time range, it is a span; for any custom type with a distance metric, it is the convex hull.

GIST is the workhorse for PostGIS geometry queries (point-in-polygon, nearest neighbor). It handles range types, custom domains, and even fuzzy text matching. The key insight is that each node stores a summary (the bounding box), and search uses that summary to skip entire subtrees. This makes GIST flexible but slower than B-tree for simple equality or order queries.

Trade-off: GIST is slower than B-tree for vanilla range queries but unbeatable for spatial, geometric, or distance-based searches. It is also not unique-constraint capable (no UNIQUE GIST), and it does not naturally support DESC or NULLS FIRST without an operator class.

-- GIST for spatial
CREATE INDEX idx_location_gist ON places USING GIST (location);

-- Fast queries:
SELECT * FROM places WHERE location <@> point(10, 20) ORDER BY location <-> point(10, 20) LIMIT 10;  -- nearest 10 neighbors
SELECT * FROM places WHERE location && box((0,0), (100,100));  -- overlaps bounding box

BRIN: block range indexes for time-series and append-only

BRIN (Block Range Index) is the smallest index type. It divides the table into fixed-size ranges (typically 128 pages = ~1 MB) and stores a summary for each range: min and max of that range's values. When you query for rows in a value range, BRIN quickly identifies which block ranges could contain matches.

BRIN is purpose-built for time-series data and append-only tables. If rows are inserted in time order (or any order that clusters by the indexed column), BRIN is tiny—often KB of index overhead per GB of data—while a B-tree would use MB. The trade-off is speed: scans are slower than B-tree because the summary is coarse, but for columns that are nearly monotonic, BRIN can still beat a B-tree on cost per query.

BRIN shines when: (1) the table is time-series or append-only, (2) queries filter on the ordering column, (3) index size matters (cloud billing, embedded systems). BRIN falters when: (1) random inserts disorder the table, (2) you need tight index selectivity for small ranges, (3) NULL handling is intricate.

-- BRIN for time-series
CREATE INDEX idx_ts_brin ON metrics USING BRIN (recorded_at) WITH (pages_per_range = 128);

-- Fast time-window queries:
SELECT * FROM metrics WHERE recorded_at > now() - interval '24 hours';  -- uses block range summary

-- Small index:
-- B-tree on 100M rows: ~2-4 GB
-- BRIN on 100M rows: ~20-50 MB

Comparison: the decision table

Use B-tree as your default. It is simple, fast for nearly all patterns, and indexes UNIQUE constraints. Reach for GIN only when you are indexing jsonb keys, array membership, or full-text tokens. Use GIST when your data is spatial or your operator class defines distance. Pick BRIN when your table is huge, your column is monotonic, and index size is a budget constraint.

Performance characteristics and trade-offs

Index size: BRIN < B-tree < GIST < GIN (roughly). GIN can balloon on high-cardinality jsonb because it stores every key separately. BRIN can fit a 10 GB table in 10 MB of index.

Insert latency: BRIN is fastest (update one block summary). B-tree is moderate (insert into tree). GIN is slowest (update all entry lists). GIST is in between (update bounding boxes).

Query latency: For the pattern each is built for, B-tree and GIN are fastest. BRIN is slower (scan more blocks) but acceptable for time-window queries. GIST is tuned for spatial and will be slower for vanilla range queries.

Vacuuming: B-tree and GIST maintain themselves cleanly. GIN and BRIN require manual REINDEX under some conditions. After a bulk delete on a GIN or BRIN, VACUUM may not fully reclaim space; REINDEX does.

Partial indexes: All four types support WHERE clauses to index only relevant rows. CREATE INDEX idx ON table (col) WHERE is_active = true saves space and write cost on the index.

How to pick the right index: flowchart

Start here: What is your query pattern?

Equality or range: Use B-tree. Fast, predictable, indexes UNIQUE. WHERE id = 5, WHERE created_at > '2026-01-01', ORDER BY id. This is 90% of production indexes.

Membership or containment (jsonb, arrays, full-text): Use GIN. WHERE data->>'key' = 'value', WHERE tags @> ARRAY['tag1'], WHERE doc @@ 'search_query'::tsquery.

Spatial, distance, or custom metrics: Use GIST. WHERE location <@> point(x,y), WHERE shape && polygon(...), nearest-neighbor queries.

Huge monotonic column, small index budget: Use BRIN. WHERE timestamp > now() - interval '7 days' on a 10 TB append-only table. BRIN index is MB; B-tree would be GB.

Multiple columns or composite queries: B-tree still wins. Create a multi-column B-tree: CREATE INDEX idx ON table (col1, col2). GIN and GIST can index multiple columns too, but B-tree is the default and usually fastest.

Common mistakes and anti-patterns

Over-indexing: Creating a B-tree for every column. Each index consumes disk, slows writes, and uses memory in the planner. Index only columns that appear in WHERE, JOIN, or ORDER BY clauses frequently.

Wrong index type: Using B-tree for jsonb membership. WHERE data->>'field' = 'value' with a B-tree index on data will not use the index; the planner does a full scan. Switch to GIN.

Ignoring partial indexes: Indexing inactive rows. If 90% of your queries filter on is_active = true, add a WHERE is_active = true clause to the index. You save 90% of the index space and write cost.

BRIN on random inserts: BRIN assumes monotonic or clustered data. If you insert rows out of order (e.g., backfills), the block range summaries become useless (every summary covers the full value range). Fall back to B-tree.

Never reindexing GIN: GIN does not reclaim space as well as B-tree after deletes. After heavy deletes, REINDEX INDEX idx to compact the index and reclaim disk.

Monitoring and tuning

Check index usage: Query pg_stat_user_indexes to see which indexes are actually used. idx_scan = 0 means the index is never touched; consider dropping it.

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Bloat detection: Bloated indexes waste disk and memory. Use the pgstattuple extension: SELECT * FROM pgstattuple_approx('index_name'). If dead_ratio is high, reindex.

Index size: SELECT pg_size_pretty(pg_relation_size('idx')) shows index size. If it is larger than expected, check for missing partial index WHERE clauses or redundant columns.

Cost estimates: Run EXPLAIN on slow queries to see if the planner is using your index. Seq Scan means no index hit; check that the index type matches the query pattern.

Best practices summary

Start with B-tree. It is the default, covers most cases, and proves its value before you optimize.

Use partial indexes for common filters. WHERE is_active = true or WHERE deleted_at IS NULL saves space and write latency.

Choose index type by query pattern. Equality/range → B-tree. Membership/full-text → GIN. Spatial/custom → GIST. Huge monotonic → BRIN.

Monitor and prune. Drop unused indexes. Reindex bloated GIN and BRIN after heavy deletes. Keep pg_stat_user_indexes in your alerting.

Test index selectivity. EXPLAIN every slow query. An index that touches 10% of the table is worth it; one that touches 50% might not be.

Avoid anti-patterns. Do not use B-tree for jsonb membership, BRIN on random inserts, or over-index columns that are never queried.