Pick the Right Postgres Index in 90 Seconds: B-tree, GIN, GiST, BRIN, Hash

• Book: Database Playbook: Choosing the Right Store for Every System You Build
• Also by me: Thinking in Go (2-book series) — Complete Guide to Go Programming + Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

---

You added an index. The query is still slow. EXPLAIN shows a Seq Scan. The team thinks indexes are broken.

They're not. You picked the wrong shape.

Postgres has five index types in the box, and most teams use one of them for everything. That works until it doesn't, and the failure mode is silent: the planner ignores your index and the query keeps scanning 80 million rows. No error, no warning, just an unhappy p99.

This is the 90-second version of the decision.

The 90-second decision tree

Look at your WHERE clause. That's it. Don't look at the column type. Don't look at what your senior used last time. Look at the predicate.

If the predicate is equality on a scalar or a prefix range (=, <, >, BETWEEN, LIKE 'foo%'), reach for a B-tree. This is the default and it earns the spot.

If the predicate is containment (@>, ?, ?&, ?|, full-text @@, trigram %), you want GIN. JSONB columns, tsvector columns, and LIKE '%foo%' belong here.

If the predicate involves geometric overlap, range overlap, or nearest-neighbour (&&, <->, ts_range && ts_range), it's GiST. Also pick GiST when you need an exclusion constraint. No other index supports EXCLUDE USING.

If the predicate is a range scan on an append-only column with 10M+ rows and physical ordering correlated with the column (timestamps, monotonic IDs), BRIN will give you 95% of the speedup at 1/1000th the storage. This is the cheapest win in Postgres and the most underused.

If the predicate is strict equality only, never range, never sort, Hash is technically faster than B-tree. In practice you'll regret picking it. The gains are marginal and B-tree handles equality fine.

That's the tree. Read each section below when you need the SQL, the gotcha, and the EXPLAIN sample.

B-tree: the default, and why "default" isn't a strategy

B-tree handles equality, prefix matches, range scans, and ORDER BY. It's what CREATE INDEX gives you when you don't specify a method. About 90% of indexes in any Postgres database are B-trees and that ratio is roughly correct.

The mistake teams make is stopping at the column-name index:

-- the lazy version
CREATE INDEX ON orders (customer_id);

That index doesn't help WHERE LOWER(email) = $1. It doesn't help WHERE status = 'pending' AND created_at > now() - interval '1 day' if the planner can use a partial. And it stores the full row pointer when you only ever read two columns.

Three B-tree features are worth knowing.

Expression indexes. When your predicate wraps the column in a function, you need to index the expression:

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

-- now this uses the index
SELECT * FROM users WHERE LOWER(email) = '[e-mail]';

Partial indexes. Index only the rows you query:

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

If 0.5% of your orders are pending, this index is 200× smaller than the full one and just as fast for the pending-queue query. Storage and write-amp drop together.

INCLUDE columns. Let the planner read non-indexed columns from the index page (a covering index):

CREATE INDEX idx_orders_customer_total
ON orders (customer_id) INCLUDE (total_cents, status);

With this, SELECT total_cents, status FROM orders WHERE customer_id = $1 can be served from the index alone. No heap fetch. The Index Only Scan line in EXPLAIN is what you're aiming at.

PG18 added skip-scan for B-trees, which means multicolumn indexes like (tenant_id, created_at) finally help queries that filter only on created_at when tenant_id cardinality is low. Before PG18 you needed two separate indexes. Now you don't. Worth the upgrade alone if you have multi-tenant tables.

GIN: when containment beats equality

GIN is the index for "is this value contained in that column". JSONB containment, full-text search, trigram fuzzy match. Anywhere the predicate isn't a single comparison but a set operation.

JSONB is the most common case:

-- the column
ALTER TABLE events ADD COLUMN payload jsonb;

-- the index you actually want
CREATE INDEX idx_events_payload
ON events USING gin (payload jsonb_path_ops);

The jsonb_path_ops operator class is half the size of the default jsonb_ops and faster for the @> containment operator. The tradeoff is jsonb_path_ops only supports @>, not ? or ?|. If you only ever do containment, use jsonb_path_ops. If you need key-existence checks too, fall back to the default.

A query that uses it:

SELECT * FROM events
WHERE payload @> '{"user_id": 42, "type": "login"}';

EXPLAIN ANALYZE on a 20M-row table will show something like:

Bitmap Heap Scan on events
  Recheck Cond: (payload @> '{"user_id": 42, "type": "login"}'::jsonb)
  Heap Blocks: exact=18
  ->  Bitmap Index Scan on idx_events_payload
        Index Cond: (payload @> '{"user_id": 42, "type": "login"}'::jsonb)
        Buffers: shared hit=4
Planning Time: 0.2 ms
Execution Time: 1.4 ms

The same query against a B-tree on payload (which Postgres will let you create) plans to a Seq Scan because B-tree on JSONB doesn't support @>.

Trigrams are the other big GIN use case. LIKE '%middle%' searches:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_products_name_trgm
ON products USING gin (name gin_trgm_ops);

SELECT * FROM products WHERE name ILIKE '%wireless%';

Here's the cost you don't see on day one. GIN inserts are roughly 10× the cost of B-tree inserts. GIN maintains a pending list (the fastupdate mechanism) that batches writes, but under sustained write load that list grows and queries start scanning it linearly. If you have a 50K-write/minute table, monitor pg_stat_user_indexes.idx_blks_read on your GIN indexes. If it climbs, tune gin_pending_list_limit or accept a slower auto-vacuum cycle.

The shape of the trap: you add a GIN index on a tags jsonb column for a hot events table, the read query goes from seconds to milliseconds, and write throughput quietly drops by an order of magnitude. The index is correct. The write profile isn't. Always benchmark inserts before shipping a GIN index on a high-write table.

GiST: geometry, ranges, and the exclusion constraint trick

GiST handles operators B-tree can't express: overlap (&&), distance (<->), containment between geometric objects. If you're using PostGIS, every spatial index you create is GiST under the hood.

The non-obvious application is exclusion constraints. Postgres lets you say "no two rows in this table can have overlapping values for these columns" using GiST:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
  id bigserial PRIMARY KEY,
  room_id int NOT NULL,
  during tstzrange NOT NULL,
  EXCLUDE USING gist (
    room_id WITH =,
    during WITH &&
  )
);

Now the database itself refuses to let two bookings for the same room overlap in time. No application-level lock, no race condition window, no SELECT FOR UPDATE dance. Try it:

INSERT INTO room_bookings (room_id, during) VALUES
  (1, '[2026-06-01 09:00, 2026-06-01 10:00)');
-- OK

INSERT INTO room_bookings (room_id, during) VALUES
  (1, '[2026-06-01 09:30, 2026-06-01 11:00)');
-- ERROR: conflicting key value violates exclusion
-- constraint "room_bookings_room_id_during_excl"

This is one of those features that, the first time you see it, makes you rewrite three services. Every booking system, calendar, resource scheduler, and meeting-room app you've ever shipped probably had this constraint enforced in app code with a race condition. GiST + EXCLUDE makes it a one-liner.

The btree_gist extension is required because GiST doesn't natively know about = on integers. The extension teaches it.

BRIN: the index that's almost free

BRIN (Block Range INdex) stores a summary per N pages of the heap. For each block range, it tracks the min and max value of the indexed column. That's it. The whole index for a 200GB table can be a few megabytes.

The catch: BRIN only works when the column is physically correlated with the heap order. Timestamps on an append-only events table are the perfect case. UUIDs are the worst case.

CREATE INDEX idx_events_created_brin
ON events USING brin (created_at)
WITH (pages_per_range = 128);

pages_per_range = 128 is the knob worth knowing. Default is 128 since PG10 but the value at table-creation time is what you got. Smaller ranges mean a bigger, more precise index. For a 100M-row events table with one row per millisecond, pages_per_range = 32 often beats the default. Test with EXPLAIN (ANALYZE, BUFFERS).

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events
WHERE created_at >= now() - interval '1 hour';

Aggregate
  ->  Bitmap Heap Scan on events
        Recheck Cond: (created_at >= ...)
        Rows Removed by Index Recheck: 1842
        Heap Blocks: lossy=2048
        ->  Bitmap Index Scan on idx_events_created_brin
              Buffers: shared hit=3
Planning Time: 0.15 ms
Execution Time: 38 ms

Three buffer hits, 38ms, for a range scan over 100M rows. The equivalent B-tree would be 4GB on disk and roughly the same query time. BRIN is 2MB. Storage and maintenance cost is the win, not raw speed.

PG17 introduced multi-minmax BRIN, which handles slightly-out-of-order data (some late-arriving rows) without falling apart. PG18 improved the planner's BRIN cost estimates, so the planner picks it correctly more often. If you're on PG14 or older and BRIN keeps getting ignored, that's why.

The gotcha: if your created_at values are inserted out of order at scale (late-arriving telemetry, backfills, anything that breaks correlation), BRIN's ranges overlap and the index becomes useless. Check correlation:

SELECT correlation FROM pg_stats
WHERE tablename = 'events' AND attname = 'created_at';

Above 0.9 and BRIN is great. Below 0.5 and you want a B-tree.

Hash: yes it's safe now, but you probably don't want it

Hash indexes were unsafe pre-PG10 (not WAL-logged, didn't survive crash recovery). They've been fine since 2017. Nobody updated their mental model.

CREATE INDEX idx_sessions_token_hash
ON sessions USING hash (session_token);

Hash supports only =. No range, no sort, no prefix. The argument for Hash over B-tree is that for very long keys (long UUIDs, hex tokens), Hash indexes can be smaller and slightly faster on equality lookups.

In real benchmarks the difference is in single-digit percent and only at the kind of QPS where you're already past Postgres anyway. Pick Hash when you've measured and proven B-tree is the bottleneck. Otherwise B-tree, every time.

The trade matrix

Index	Query shape	Build cost	Storage	Write-amp	Notes
B-tree	=, <, >, prefix LIKE, sort	Low	Medium	1×	Default. Skip-scan in PG18
GIN	@>, ?, @@, %	High	Large	~10×	Pending list under write load
GiST	&&, <->, EXCLUDE	Medium	Medium	2-3×	Only index for exclusion constraints
BRIN	Range on correlated column	Very low	Tiny (~1/1000 B-tree)	Negligible	Needs correlation > 0.9
Hash	= only	Low	Small	1×	WAL-safe since PG10. Rarely worth it

How to verify you picked right

EXPLAIN (ANALYZE, BUFFERS) is the only honest answer. Run it. If you don't see Index Scan, Index Only Scan, or Bitmap Index Scan mentioning your index name, the planner ignored it. Reasons it ignores indexes:

• Statistics stale. Run ANALYZE table_name
• Predicate doesn't match index expression (you indexed email, queried LOWER(email))
• Selectivity too low. Postgres correctly chose Seq Scan because the index would touch most pages anyway
• Wrong operator class (GIN without jsonb_path_ops for a @> query that needs it)

pg_stat_user_indexes tells you which indexes never get used:

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC
LIMIT 20;

Anything with idx_scan = 0 after a month of production traffic is paying write-amp for nothing. Drop it.

Run this query quarterly. The bottom of that list is where storage goes to die.

What's the weirdest index decision you've inherited or shipped? GIN on a column that never gets queried? A B-tree where BRIN would've been 1000× smaller? Drop it in the comments. Bonus points if you remember the EXPLAIN line that finally made you change it.

---

If this was useful

This post pulled from the indexing chapter of the Database Playbook: Choosing the Right Store for Every System You Build. The book walks through index selection alongside the broader question of which store fits which workload: Postgres, MySQL, DynamoDB, Cassandra, ClickHouse, and the rest. The indexing chapter covers GIN write-amp tuning, BRIN multi-minmax in PG17/18, and the exclusion-constraint pattern in more depth than this post had room for.