Choosing the Right Postgres Index, BRIN, GIN, HNSW, IVFFlat

TL;DR — BTREE for selective equality and range, BRIN for append-mostly time-series at 1/1000th the size, GIN for JSONB and full-text, HNSW for low-latency vector search, IVFFlat for huge vector tables on a memory budget. Pick wrong and you pay 10-100x.

I get asked about index choice more than any other Postgres topic. The answer is genuinely simple — there are five indexes most people need, each one solves a specific problem, and the costs of picking wrong are visible in pg_stat_statements within a week.

This post is the decision tree I run mentally when designing a new schema or auditing an existing one. I’ll cover BTREE (the default), BRIN (the secret weapon), GIN (for JSONB and FTS), HNSW (the new vector default), and IVFFlat (for when HNSW won’t fit). I’m skipping SP-GiST and Hash because I rarely use them.

All examples target Postgres 17 with pgvector 0.7.4. If you’re on 15 or 16, everything except the vector indexes works identically.

BTREE, the default that’s usually right

CREATE INDEX orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

A BTREE is what CREATE INDEX builds when you don’t say otherwise. It’s a balanced tree on column values, supports equality and range, and is the right answer for maybe 80% of indexes you’ll ever write.

The non-obvious BTREE rules I follow:

• Compound order: equality, then range, then sort. (customer_id, created_at DESC) for WHERE customer_id = ? ORDER BY created_at DESC. Inverting the columns turns a 1 ms index scan into a 50 ms index scan + sort.
• INCLUDE for covering. CREATE INDEX ... ON t (a) INCLUDE (b, c) puts b and c in the leaf but not the key. Lets index-only scans return them without touching the heap, saving I/O on hot read paths.
• Partial indexes for skewed data. If 99% of rows have status = 'archived', an index on status is useless. CREATE INDEX ... WHERE status <> 'archived' indexes only the 1% you care about and is tiny.

Size budget: a BTREE on a 50M-row int column runs around 1 GB. Doubling that for a covering index is normal.

BRIN, the time-series cheat code

If your data is naturally sorted on disk by some column — almost always a timestamp on an append-only log or events table — BRIN is 1000x smaller than BTREE and almost as fast on range queries.

CREATE INDEX events_created_brin
  ON events USING brin (created_at)
  WITH (pages_per_range = 32);

BRIN stores min/max per “range” of pages instead of one entry per row. The default pages_per_range = 128 covers 1 MB of heap per index entry. On a 100 GB events table you get a roughly 800 KB index. That’s not a typo.

The catch is “naturally sorted”. If you UPDATE old rows or do out-of-order inserts, BRIN ranges widen until the index is useless. The fix in Postgres 17 is the new bloom BRIN op classes for low-cardinality columns and minmax_multi for clustered-but-not-strictly-sorted data:

CREATE INDEX events_user_brin_bloom
  ON events USING brin (user_id bloom_ops);

Use BRIN when:

• Table is append-only or nearly so.
• Column correlates with physical row order (pg_stats.correlation near 1.0 or -1.0).
• Range queries are common.

Don’t use BRIN for primary keys, foreign keys, or any selective lookup. It’s not a BTREE replacement.

GIN, for arrays, JSONB, and full-text

If you’re storing arrays, JSONB, or text you want to search inside, GIN is the answer.

-- JSONB containment
CREATE INDEX docs_data_gin ON docs USING gin (data jsonb_path_ops);

-- full-text search
CREATE INDEX articles_fts_gin
  ON articles USING gin (to_tsvector('english', title || ' ' || body));

-- array overlap
CREATE INDEX posts_tags_gin ON posts USING gin (tags);

GIN (“Generalized Inverted Index”) maps each value-fragment back to the rows containing it. For JSONB it’s keys-and-values, for text it’s lexemes, for arrays it’s elements. A query like data @> '{"status": "open"}' becomes “find rows in the posting list for ‘status:open’”, which is fast even on huge tables.

Two GIN sub-knobs that matter:

• jsonb_path_ops vs jsonb_ops. The first is smaller and faster but only supports @>. The second supports ?, ?|, ?& as well. Default to path_ops unless you need the others.
• fastupdate = on/off. On (default) batches updates in a pending list, faster writes, slower reads until autovacuum merges. Turn off for read-heavy workloads where pending-list scans hurt p99.

GIN is bigger than BTREE — figure 2-4x the size of an equivalent BTREE. Write amplification is real, so I avoid GIN on tables with sustained high write rates above a few thousand inserts per second.

The GIN documentation covers the operator classes worth knowing.

HNSW, the new vector default

pgvector 0.7.4 ships two index types for embeddings, and HNSW is what you want for almost every new project.

CREATE INDEX docs_embedding_hnsw
  ON docs USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

HNSW (“Hierarchical Navigable Small World”) builds a multi-layer graph where each node connects to roughly m neighbors. Query traverses the graph greedily. Recall is tunable at query time via ef_search:

SET LOCAL hnsw.ef_search = 100;  -- default 40

Higher ef_search means higher recall, slower queries. The trade is continuous and you can tune per query class.

Memory footprint: roughly 4 * d * m bytes per vector for index overhead, plus 4 * d for the vector itself, where d is dimensionality. For a million 1536-dim vectors at m=16, that’s 100 GB of vectors plus ~100 GB of index. RAM is the real constraint, not disk.

For deeper discussion of when each pgvector index makes sense at scale, see pgvector tuning in 2024, HNSW and IVFFlat in production.

IVFFlat, for huge vector tables

When you have 100M+ vectors and HNSW won’t fit in RAM, IVFFlat is the fallback. It clusters vectors into lists partitions at build time, and at query time scans only the closest probes partitions:

CREATE INDEX docs_embedding_ivf
  ON docs USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 1000);

lists should be roughly sqrt(rows) for under 1M vectors, or rows/1000 for larger. Memory footprint is ~ vectors + small overhead, far less than HNSW.

The catch: IVFFlat must be built after the data is loaded, because the clustering uses the existing distribution. Rebuild after major data ingestion or recall degrades. HNSW doesn’t have this restriction — it builds incrementally as you insert.

The decision tree

This is the order I run through mentally.

1. Lookup by equality or range on a regular column? BTREE. Done.
2. Range query on a timestamp on an append-only table? BRIN. Order of magnitude less space than BTREE.
3. Search inside a JSONB document, text body, or array? GIN. Pick jsonb_path_ops for containment-only.
4. Vector similarity search and dataset fits in RAM? HNSW.
5. Vector similarity search and dataset doesn’t fit? IVFFlat with lists = rows/1000.
6. Spatial or geometric overlap? GiST. (Out of scope here, but PostGIS handles this for you.)

Anything that’s not on this list, I’d want a specific reason to deviate from these defaults.

Common Pitfalls

The most common mistakes I see in code review.

• Indexing low-cardinality boolean columns. A BTREE on is_active where 99% of values are true is dead weight. Use a partial index on the rare value.
• Index per column instead of compound. Three single-column BTREEs on (a), (b), (c) can’t replace one BTREE on (a, b, c). Bitmap-AND of multiple indexes is slow.
• Building HNSW before data is loaded. It works, but takes forever as the graph rebalances. Bulk-load first, then index.
• Forgetting pg_stat_user_indexes. If idx_scan = 0 after a month in production, drop the index. Writes pay for it; nothing benefits.
• REINDEX without CONCURRENTLY. Locks the table. REINDEX INDEX CONCURRENTLY exists since 12 and is the right command for live systems.
• Misjudging GIN write cost. GIN updates are batched but expensive. A high-churn JSONB column with GIN can dominate WAL volume. Measure with pg_stat_statements before assuming the index pays for itself.

If you’re not sure which index a query uses, capture an EXPLAIN and look. Then check pg_stat_user_indexes for confirmation it’s actually getting hit.

What’s Next

Index choice is one of the few areas in databases where the cost of getting it wrong is visible quickly and the cost of fixing it is also bounded. You can build a new index CONCURRENTLY, validate it with pg_stat_user_indexes, then drop the old one. Iterate.

The vector index space is moving fast. HNSW in pgvector 0.7.4 is good enough that I’d stop reaching for external vector databases unless you genuinely need 1B+ vectors with sub-10ms p99. For everything below that, Postgres 17 plus pgvector is the simplest stack that works.

Pick the right index from this list and you’ve solved 95% of your performance problems before they happen.