PostgreSQL Indexing Strategies in 2026: B-tree vs GIN vs BRIN vs Hash
A practical guide to PostgreSQL index types in 2026 — when to reach for B-tree, GIN, GiST, BRIN, or Hash, with real query examples and EXPLAIN ANALYZE output.
On this page
I once inherited a Postgres table with 40 million rows and a query that took 11 seconds to return a handful of matches. There was an index on the column being filtered. The index was just the wrong type — a B-tree sitting on a JSONB column that needed a GIN index, doing sequential-scan-adjacent work because it couldn’t actually help with the containment query being run against it. Swapping the index type, not adding more indexes, took that query from 11 seconds to 40 milliseconds. That’s the story indexing strategy in Postgres always comes back to: it’s rarely about whether you have an index, it’s about whether you have the right kind.
Postgres ships five index access methods you’ll actually use in production — B-tree, Hash, GiST, GIN, and BRIN — and each one is built around a completely different assumption about your data and your queries. Picking the default (B-tree, always) and calling it done is how you end up with slow JSONB lookups, bloated indexes on huge append-only tables, and full-text search that grinds to a halt. Let’s go through each one properly.
Why PostgreSQL Indexing Strategy Isn’t One-Size-Fits-All
Every index in Postgres is a trade: you pay in write overhead and disk space, and you get back faster reads for a specific shape of query. A B-tree is exceptional at range queries and equality on scalar values. It is structurally incapable of efficiently answering “does this array contain this value” or “is this point inside this polygon” — not because Postgres is bad at it, but because a sorted tree of scalar values simply doesn’t encode that kind of relationship.
This is the mental model worth keeping: index type should follow query shape, not column type. A jsonb column might want a GIN index for containment queries, a B-tree expression index for a specific extracted field you always filter on, or no index at all if you rarely query it. The column’s data type is a hint, not the answer.

B-tree: The Default, and When It’s Actually the Right Default
B-tree is Postgres’s default index type for a reason — it’s the correct choice for the overwhelming majority of columns you’ll ever index. It handles equality (=), range comparisons (<, <=, >, >=, BETWEEN), sorting (ORDER BY), and IN lists, all with logarithmic lookup time.
CREATE INDEX idx_orders_created_at ON orders (created_at);
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
ORDER BY created_at;
Index Scan using idx_orders_created_at on orders
(cost=0.43..1245.67 rows=18420 width=96)
(actual time=0.031..3.812 rows=18213 loops=1)
Index Cond: ((created_at >= '2026-01-01') AND (created_at < '2026-02-01'))
Planning Time: 0.112 ms
Execution Time: 4.902 ms
Reach for B-tree by default on: primary keys, foreign keys, timestamp columns you filter by range, status/enum columns you filter by equality, anything you sort or paginate on. Use it also for composite indexes where you filter on multiple columns together — column order matters, and the rule is “equality columns first, range column last”:
CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, created_at);
This index serves WHERE customer_id = ? AND status = ? efficiently, and also serves WHERE customer_id = ? AND status = ? AND created_at > ? because created_at is the trailing range column. Flip the order and put created_at first, and the index becomes far less useful for the equality-heavy version of the query.
Hash: Narrow, Fast, and Almost Never What You Need
Hash indexes support only equality (=) — no ranges, no sorting, no LIKE. In exchange, a hash lookup is theoretically O(1) versus a B-tree’s O(log n), which sounds like a win until you measure it: on modern Postgres, B-tree equality lookups are close enough in practice that the difference rarely matters, and B-tree gives you range and sort support for free.
CREATE INDEX idx_sessions_token_hash ON sessions USING HASH (session_token);
Since Postgres 10, hash indexes are WAL-logged and crash-safe (they weren’t before, which is why older advice says “never use hash indexes” — that caveat is outdated but the underlying reasoning about narrow usefulness still holds). The legitimate case for hash today: a very wide column (like a long token or hash string) where you only ever do exact-match lookups and want a smaller, slightly faster index than a B-tree on the same column. It’s a niche, not a default.
EXPLAIN ANALYZE
SELECT * FROM sessions WHERE session_token = 'a1b2c3...';
Index Scan using idx_sessions_token_hash on sessions
(cost=0.00..8.02 rows=1 width=64)
(actual time=0.018..0.019 rows=1 loops=1)
Execution Time: 0.041 ms
Fast, but a B-tree on the same column would likely clock in within microseconds of this. Don’t reach for Hash unless you’ve measured a real difference for your workload.
GIN: The Index for “Contains” Queries — JSONB, Arrays, Full-Text Search
GIN (Generalized Inverted Index) is where Postgres indexing gets interesting. Instead of storing one entry per row like a B-tree, GIN builds an inverted index — mapping each individual element (a JSONB key, an array value, a lexeme in a tsvector) back to the rows that contain it. This is exactly the structure you need for “does this composite value contain X” queries.
JSONB containment:
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops);
EXPLAIN ANALYZE
SELECT * FROM events WHERE payload @> '{"type": "checkout_failed"}';
Bitmap Heap Scan on events
(cost=24.15..892.30 rows=210 width=340)
(actual time=0.412..2.105 rows=198 loops=1)
Recheck Cond: (payload @> '{"type": "checkout_failed"}'::jsonb)
Heap Blocks: exact=192
-> Bitmap Index Scan on idx_events_payload_gin
(cost=0.00..24.10 rows=210 width=0)
(actual time=0.298..0.298 rows=198 loops=1)
Execution Time: 2.201 ms
Note jsonb_path_ops versus the default jsonb_ops operator class: jsonb_path_ops builds a smaller, faster index but only supports the @> containment operator — no key-existence (?) queries. If you need ?/?|/?& operators too, use the default jsonb_ops, at the cost of a larger index.
Array containment:
CREATE INDEX idx_products_tags_gin ON products USING GIN (tags);
SELECT * FROM products WHERE tags @> ARRAY['clearance', 'electronics'];
Full-text search:
CREATE INDEX idx_articles_search_gin ON articles USING GIN (to_tsvector('english', body));
SELECT title FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'postgres & indexing');
The trade-off: GIN indexes are expensive to write to. Every insert or update touches potentially many inverted-index entries, and by default GIN batches pending changes into a “pending list” flushed on VACUUM or when it fills up (gin_pending_list_limit) to keep individual writes cheaper. On write-heavy tables, this is a real cost worth benchmarking, not something to ignore.
GiST: Geometric, Range, and Nearest-Neighbor Queries
GiST (Generalized Search Tree) is the index type for data where “closest to” or “overlaps with” matters more than equality — geometric types, range types, and nearest-neighbor search via extensions like PostGIS.
CREATE INDEX idx_reservations_period ON reservations USING GIST (during);
-- Prevent overlapping bookings for the same room, enforced at the DB level
ALTER TABLE reservations
ADD CONSTRAINT no_overlap EXCLUDE USING GIST (room_id WITH =, during WITH &&);
EXPLAIN ANALYZE
SELECT * FROM reservations
WHERE during && tstzrange('2026-03-01', '2026-03-05');
Index Scan using idx_reservations_period on reservations
(cost=0.28..38.21 rows=12 width=88)
(actual time=0.045..0.089 rows=9 loops=1)
Index Cond: (during && '["2026-03-01 00:00:00+00","2026-03-05 00:00:00+00")'::tstzrange)
Execution Time: 0.121 ms
That EXCLUDE USING GIST constraint is worth calling out on its own — it’s a genuinely underused Postgres feature that lets the database itself reject overlapping ranges (double-booked rooms, overlapping date ranges, conflicting time slots) without application-level locking gymnastics. GiST also backs pg_trgm trigram indexes for fuzzy text search (similarity(), % operator) and PostGIS spatial indexes for ST_Contains/ST_DWithin queries — anywhere “nearest” or “overlaps” is the real question.
BRIN: The Index for Massive, Naturally Ordered Tables
BRIN (Block Range Index) is the odd one out, and it’s the one most teams don’t know about until they hit a genuinely huge table. Instead of indexing every row, BRIN stores a summary (typically min/max) per physical block range — say, every 128 pages. It works because it doesn’t try to point at individual rows; it eliminates whole block ranges that can’t possibly contain a match.
The catch: BRIN only pays off when the column is correlated with physical row order — classic case being an append-only created_at or auto-incrementing id column, where new rows land at the end of the table in roughly increasing order.
CREATE INDEX idx_logs_created_at_brin ON logs USING BRIN (created_at)
WITH (pages_per_range = 128);
EXPLAIN ANALYZE
SELECT count(*) FROM logs
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02';
Aggregate (cost=3421.10..3421.11 rows=1 width=8)
(actual time=18.223..18.224 rows=1 loops=1)
-> Bitmap Heap Scan on logs
(cost=180.02..3398.44 rows=9064 width=0)
(actual time=2.104..16.981 rows=8912 loops=1)
Recheck Cond: ((created_at >= '2026-08-01') AND (created_at < '2026-08-02'))
Rows Removed by Index Recheck: 512
-> Bitmap Index Scan on idx_logs_created_at_brin
(cost=0.00..177.75 rows=182800 width=0)
(actual time=1.842..1.842 rows=25600 loops=1)
Index Cond: ((created_at >= '2026-08-01') AND (created_at < '2026-08-02'))
Execution Time: 18.301 ms
For a 200-million-row log table, that BRIN index takes up a few hundred kilobytes — versus what would be several gigabytes for an equivalent B-tree. It’s slower per-row than a B-tree (notice the recheck overhead), but for time-series and log-style tables where the table itself is the bottleneck to store and maintain, the space savings and near-zero write overhead make it the clear winner. BRIN is a bad fit for columns with no correlation to physical storage order — an indexed customer_id on a table where rows are inserted in arbitrary order gets you nothing, because every block range ends up containing every possible value.

Quick Reference: Which Index When
| Index Type | Best For | Operators | Write Cost | Typical Size |
|---|---|---|---|---|
| B-tree | Equality, ranges, sorting | =, <, >, BETWEEN, IN |
Low | Medium |
| Hash | Exact-match only, wide columns | = |
Low | Small-Medium |
| GIN | JSONB, arrays, full-text search | @>, ?, @@ |
High | Large |
| GiST | Geometric, ranges, nearest-neighbor | &&, <->, @> |
Medium | Medium |
| BRIN | Huge, naturally ordered append-only tables | =, <, >, BETWEEN |
Very Low | Tiny |
Common Indexing Mistakes That Quietly Wreck Performance
Indexing every column “just in case." Every index slows down every write to that table, and the planner has to consider (and sometimes misjudge) more options at query time. Index what you actually filter, join, or sort on — check pg_stat_user_indexes periodically for indexes with near-zero scans and drop them.
Wrong composite index column order. As covered above, put equality columns before range columns. An index on (created_at, customer_id) is close to useless for WHERE customer_id = ? AND created_at > ? compared to (customer_id, created_at).
Forgetting that a B-tree can’t help containment queries. This was the 11-second query from the intro — a B-tree on a jsonb column does nothing for a @> containment check. Postgres will fall back to a sequential scan (or worse, look like it’s using the index but recheck almost every row) unless the index type actually matches the operator.
Not using expression or partial indexes. If you always query WHERE lower(email) = ?, index lower(email), not email — a plain B-tree on email won’t be used for that query. If you only ever query WHERE status = 'pending' and that’s 2% of rows, a partial index (CREATE INDEX ... WHERE status = 'pending') is dramatically smaller and faster than indexing the whole column:
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';
Ignoring index bloat. Heavily updated tables accumulate dead tuples in their indexes over time. REINDEX CONCURRENTLY (available since Postgres 12) rebuilds an index without locking writes — worth scheduling periodically on high-churn tables rather than assuming autovacuum alone keeps indexes lean.
Trusting the index exists without confirming the planner uses it. Always verify with EXPLAIN ANALYZE, not just EXPLAIN — the estimated plan and the actual executed plan can diverge, especially on stale statistics. Run ANALYZE on a table after bulk loads before trusting query plans against it.
FAQ
Do I need a GIN index for every JSONB column? No — only for columns you query with containment (@>), key-existence (?), or full-text operators. If you only ever extract one specific field, a B-tree expression index on that extracted field (CREATE INDEX ON t ((payload->>'user_id'))) is often cheaper and faster.
Can I have multiple index types on the same column? Yes. It’s common to have a B-tree for equality/sort and a separate GIN or GiST index on the same column for a different query pattern, though each additional index adds write overhead — only do this if both access patterns are genuinely needed.
Is BRIN ever better than B-tree on a small table? Practically no — BRIN’s advantage is space and write efficiency at large scale on correlated data. On a small or randomly-ordered table, a B-tree will outperform it on lookup speed.
How do I know which index Postgres is actually using? Run EXPLAIN ANALYZE on the query and look for Index Scan, Index Only Scan, or Bitmap Index Scan referencing your index name. Seq Scan means no index was used at all.
Does adding an index always speed up reads? No — for very small tables, or queries that return most of the table anyway, the planner will correctly choose a sequential scan over an index scan, because random I/O per row through an index can be slower than one linear pass.
Closing Thought
Postgres gives you five genuinely different tools for the same broad job, and the biggest performance wins usually come from matching the tool to the query shape rather than adding more indexes to a table that already has plenty. Before creating your next index, ask what operator the query actually needs — =, range, containment, or proximity — and let that answer pick the index type. That one habit would have saved me a very confusing afternoon staring at an 11-second query that had an index on it the whole time.