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.