Picking the Right Postgres Index: B-tree, GIN, GiST, BRIN

Ask most developers what a Postgres index is and the answer is “a B-tree that makes WHERE clauses fast.” That’s true for maybe 80% of indexes in a typical schema, and it’s exactly the assumption that falls apart the first time someone adds WHERE tags @> ARRAY['urgent'] or WHERE ST_DWithin(location, ..., 500) to a query and wonders why a B-tree index on that column does nothing at all. Postgres ships four genuinely different index access methods for a reason — B-tree, GIN, GiST, and BRIN each solve a different shape of “find rows matching this condition quickly,” and reaching for the default one out of habit is how teams end up with an index that costs writes and never gets used on reads.

The real skill isn’t memorizing four acronyms — it’s learning to look at a query’s WHERE clause and recognize which shape of lookup it actually is: equality and range on scalar values, containment inside arrays or JSON, overlap and proximity in geometric or range types, or a simple correlation with physical row order on a huge table. Once you can name the shape, the index type mostly picks itself. This post walks through all four, what each one costs on writes, and where partial and covering indexes change the calculus entirely.

You’ll learn:

• What each of the four core index types is actually structured to answer
• Why B-tree is the correct default and when it stops being enough
• When to use a GIN index for JSONB, array, and full-text search columns
• What GiST is for, and how it differs from GIN for similar-looking use cases
• Why BRIN indexes are nearly free on huge, naturally-ordered tables
• How partial indexes cut both index size and write cost when a column is mostly one value
• How covering indexes let Postgres skip the table entirely with index-only scans

Table of Contents

1. The Basics: What an Index Costs
2. B-tree: The Default, and Why
3. GIN: Containment and Full-Text Search
4. GiST: Overlap, Proximity, and Exclusion
5. BRIN: Nearly Free on Huge, Ordered Tables
6. Partial Indexes: Indexing Only What Matters
7. Covering Indexes and Index-Only Scans
8. Choosing by Query Shape
9. Common Pitfalls

The Basics: What an Index Costs

Every index is a tradeoff, not a free win. On reads, a well-matched index turns a full table scan into a targeted lookup. On writes, every INSERT, UPDATE, or DELETE that touches an indexed column has to update every index covering that column too — more indexes means slower writes, more disk space, and more work for autovacuum. This is why “just add an index” isn’t a universal answer; it’s a bet that the read savings outweigh the write cost, and that bet is different for every table depending on its read/write ratio.

Postgres’s pg_indexes and pg_stat_user_indexes views are the honest source of truth for whether that bet paid off:

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

An index sitting at idx_scan = 0 after weeks of production traffic is pure write overhead with no read benefit — a strong candidate to drop. Before reaching for a new index type, it’s worth reading a plan closely enough to know what shape of scan is actually happening; the EXPLAIN ANALYZE guide covers how to tell a sequential scan crying out for an index from one that’s genuinely the cheaper option.

B-tree: The Default, and Why

B-tree is what CREATE INDEX builds unless you specify otherwise, and it’s the right default because it’s the only structure that efficiently handles equality and ordered range queries — =, <, >, BETWEEN, ORDER BY, and IN — on scalar types like integers, text, timestamps, and UUIDs.

CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Uses the index for both of these:
SELECT * FROM orders WHERE created_at > now() - interval '7 days';
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

A composite B-tree index — one built across multiple columns — is ordered by its leading column first, then the next, and so on, which means column order in the CREATE INDEX statement isn’t cosmetic. An index on (customer_id, status) serves queries filtering on customer_id alone or on customer_id AND status together, but it does nothing for a query that filters on status alone, because the index isn’t sorted by status at the top level. Put the column with the most selective, most commonly-filtered-alone value first.

B-tree stops being a good fit the moment the condition isn’t “does this scalar value fall in this range” — containment (@>, ?), full-text (@@), geometric overlap, and similarity aren’t range questions, and a B-tree literally cannot answer them without degrading to a full scan.

GIN: Containment and Full-Text Search

A GIN index (Generalized Inverted Index) is built for the opposite question from a B-tree: instead of “where does this single value sit in sorted order,” it answers “which rows contain this element inside a composite value.” That’s exactly the shape of a JSONB containment check, an array membership test, or a full-text search.

-- JSONB containment
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);
SELECT * FROM products WHERE attributes @> '{"color": "red"}';

-- Array containment
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgres'];

-- Full-text search
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));
SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('index & performance');

Internally, a GIN index stores a mapping from each individual element (each JSON key/value pair, each array element, each lexeme) back to the rows containing it — an inverted index, the same structure a search engine uses. That structure is what makes containment fast, and it’s also why GIN indexes are noticeably more expensive to maintain on write than B-tree: inserting one row can mean updating dozens of individual entries if that row’s JSONB document or array has dozens of elements.

Postgres’s fastupdate mechanism (on by default) batches those updates into a pending list rather than writing directly into the index tree on every insert, trading a background maintenance cost for lower per-write latency. On a write-heavy table with a GIN index, watch pg_stat_user_tables for autovacuum frequency — a GIN-heavy table often needs more aggressive autovacuum settings than the defaults provide.

GIN also supports multicolumn indexes as of Postgres 9.4 onward, which matters for a common pattern: filtering on a JSONB column and a plain scalar column in the same query.

CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE INDEX idx_products_multi ON products
USING GIN (tenant_id, attributes);

SELECT * FROM products
WHERE tenant_id = 42 AND attributes @> '{"in_stock": true}';

The btree_gin extension lets a scalar column like tenant_id participate in a GIN index alongside a jsonb or array column, so a single index can serve a combined filter instead of forcing the planner to choose between a B-tree on tenant_id and a GIN index on attributes and then bitmap-AND the two result sets together. Whether the combined index or two separate indexes is faster depends on selectivity — measure both with EXPLAIN (ANALYZE, BUFFERS) rather than assuming.

GiST: Overlap, Proximity, and Exclusion

GiST (Generalized Search Tree) looks similar to GIN on the surface — both handle non-scalar data — but the questions they answer are different. GIN answers “does this row contain this exact element.” GiST answers “does this row’s value overlap, intersect, or lie near this other value,” which is a fundamentally different, lossier structure built around bounding regions rather than exact element lookups.

-- Geometric proximity (with the earthdistance/cube or PostGIS extension)
CREATE INDEX idx_venues_location ON venues USING GIST (location);
SELECT name FROM venues
WHERE ST_DWithin(location, ST_MakePoint(-122.42, 37.77), 5000);

-- Range overlap
CREATE INDEX idx_bookings_period ON bookings USING GIST (during);
SELECT * FROM bookings WHERE during && tsrange('2026-08-01', '2026-08-05');

-- Exclusion constraints (no overlapping bookings for the same room)
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING GIST (room_id WITH =, during WITH &&);

That last example is worth calling out on its own — GiST is the only one of these four structures that backs an EXCLUDE constraint, which is how Postgres enforces “no two rows may have overlapping ranges for the same key” at the database level instead of in application code. This is genuinely hard to get right with row-level locking and application logic alone, and the database-level constraint closes race conditions that application code reliably misses under concurrency.

GiST and GIN both support the same operator classes for some types (like jsonb), and when both apply, GIN is usually faster to query but slower to build and larger on disk, while GiST builds faster and stays smaller at the cost of some query speed — check both if you’re on the boundary and the choice isn’t dictated by needing EXCLUDE or geometric operators specifically.

BRIN: Nearly Free on Huge, Ordered Tables

BRIN (Block Range Index) takes a completely different approach: instead of indexing individual rows, it stores the minimum and maximum value for each physical block range (128 pages by default). That makes it dramatically smaller than a B-tree — often two to three orders of magnitude smaller — at the cost of being far less precise: a BRIN index can only tell you which block ranges might contain a matching row, not which exact rows do, so Postgres still has to re-check candidate rows against the actual condition.

CREATE INDEX idx_events_logged_at ON events USING BRIN (logged_at);

This is the right tool specifically when a column correlates strongly with physical insertion order — a created_at or logged_at timestamp on an append-only table is the canonical case, because rows inserted around the same time genuinely live near each other on disk. A time-series or event log table with a hundred million rows can get a BRIN index that’s a few hundred kilobytes, versus a B-tree on the same column running into the hundreds of megabytes, with query performance that’s close enough for range-scan-heavy workloads to make the tradeoff worthwhile.

BRIN is the wrong choice the moment the indexed column doesn’t correlate with physical row order — a status column that gets updated in place scattered across the table, for example, defeats the entire premise, because the min/max per block range stops meaning anything useful.

Partial Indexes: Indexing Only What Matters

A partial index adds a WHERE clause to CREATE INDEX itself, so only rows matching that condition get indexed at all. This is the single most underused tool in this list, and it directly answers when to use a postgres partial index instead of a full one: any time a column is heavily skewed toward one value that your queries never actually filter for.

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

If 95% of orders are completed or cancelled and the application only ever queries for pending ones on this path, a full index on status wastes most of its size covering rows nobody looks up that way. The partial index above is a fraction of the size of a full index on the same column, cheaper to maintain on every write to a non-pending row (because those writes don’t touch this index at all), and — because it’s smaller — more likely to stay resident in cache. The same pattern applies to soft-deletes (WHERE deleted_at IS NULL), feature flags, and any enum-like column with one dominant, rarely-queried value.

Covering Indexes and Index-Only Scans

A normal index scan still has to visit the underlying table (the “heap”) to fetch columns that aren’t in the index itself. A covering index, built with INCLUDE, stores extra columns inside the index leaf pages purely so the query can be answered from the index alone, skipping the heap entirely — an index-only scan.

CREATE INDEX idx_orders_customer_covering
ON orders (customer_id, status) INCLUDE (total, created_at);

EXPLAIN (ANALYZE, BUFFERS)
SELECT total, created_at FROM orders
WHERE customer_id = 4821 AND status = 'pending';
-- Index Only Scan using idx_orders_customer_covering

The INCLUDE columns aren’t part of the index’s sort key — they don’t help with filtering or ordering, they just ride along so the heap lookup becomes unnecessary for queries that only need those columns back. This trades index size (the included columns duplicate data that already lives in the table) for read speed, and it only pays off fully when the table’s visibility map is well-maintained by autovacuum — an index-only scan still needs to check heap visibility for pages that haven’t been vacuumed recently, so a table under heavy churn without adequate autovacuum tuning won’t see the full benefit.

Choosing by Query Shape

• Equality or range on a scalar column, sorting, or general-purpose default: B-tree.
• “Does this JSONB/array contain X” or full-text search: GIN.
• Geometric overlap, proximity, range overlap, or an EXCLUDE constraint: GiST.
• Huge, append-mostly table where the column correlates with insertion order: BRIN.
• A skewed column where queries only ever care about the rare value: a partial index, combined with whichever base type fits the column.
• A hot query that only needs a few columns back: a covering index with INCLUDE, on top of whichever type fits the filter itself.

These aren’t mutually exclusive — a partial GIN index, or a covering B-tree with a WHERE clause, are both completely valid and often the best answer once you’ve identified both the query shape and the skew in the data.

Treat this table as a starting point, not a verdict — the only way to know which index actually helps a specific query is to build it and compare EXPLAIN (ANALYZE, BUFFERS) before and after, since real selectivity on your actual data can shift the tradeoff either direction.

One more thing worth building into the habit early: none of these index types should be built with a bare CREATE INDEX on a table already serving production traffic. A plain CREATE INDEX takes a lock that blocks writes to the table for the full duration of the build, which on a large table can mean minutes of blocked INSERT/UPDATE/DELETE statements. CREATE INDEX CONCURRENTLY builds the same index — B-tree, GIN, GiST, or BRIN, the option applies to all four — without holding that lock, at the cost of a slower build and a small chance of needing to retry if it’s interrupted partway through.

Common Pitfalls

Mistake: adding a B-tree index and expecting it to speed up a JSONB containment query. A B-tree on a jsonb column can support equality on the whole document but not @> containment. Solution: use GIN for containment, full-text, and array membership.

Mistake: leaving fastupdate GIN indexes unmonitored on a high-write table. The pending list can grow large enough that autovacuum struggles to keep up, and query latency creeps up as a result. Solution: watch pg_stat_user_tables and tune autovacuum thresholds for GIN-heavy tables specifically.

Mistake: using BRIN on a column with no correlation to physical order. The index looks like it exists but barely narrows anything down, and queries fall back to scanning most of the candidate block ranges anyway. Solution: check correlation in pg_stats for the column before choosing BRIN.

Mistake: building a composite B-tree index with columns in the wrong order. An index on (status, customer_id) doesn’t serve a query filtering on customer_id alone as well as (customer_id, status) would. Solution: lead with the column used most often in isolation or with the highest selectivity.

Mistake: never checking pg_stat_user_indexes for unused indexes. Every index not in real use is pure write overhead. Solution: review idx_scan periodically and drop indexes sitting at zero after a full traffic cycle.

Wrapping Up

There’s no single “right” Postgres index — there’s a right index for the specific shape of the query you’re actually running, and Postgres gives you four structurally different tools because scalar range lookups, containment, overlap, and huge-table correlation are four genuinely different problems. B-tree covers most of what a typical schema needs; GIN and GiST cover the JSONB, array, full-text, and geometric cases a B-tree can’t touch at all; BRIN buys enormous space savings on huge, naturally-ordered tables; and partial and covering indexes are modifiers that make any of the above cheaper or faster once you understand your data’s actual skew and access pattern.

Next time you reach for CREATE INDEX, are you matching it to the query’s actual shape, or defaulting to B-tree out of habit?