Indexing Strategies: When Each Index Type Actually Pays Off

I’ve been pulled into a lot of slow-query war rooms over the years, and they tend to end the same way. Someone runs EXPLAIN, sees a sequential scan, types CREATE INDEX on whatever column shows up in the WHERE clause, and the ticket gets closed. Sometimes that’s exactly right. Often it’s a coin flip that happened to land well.

The reflex is understandable. “Add an index” is the one piece of database advice everyone has absorbed, so indexes get treated as a single undifferentiated fix. More indexes, faster queries, done. What that framing misses is that Postgres ships several fundamentally different index types, and each one is a bet on a specific access pattern. An index that doesn’t match the pattern isn’t a mild inefficiency. It’s pure write tax with no read payoff.

My narrower point in this post: you can cover almost every real workload with five tools, B-tree, GIN, BRIN, partial indexes, and covering indexes, as long as you know the one question each of them answers well. I’ll walk through when each one pays off, where each one stops, and how to audit the indexes you already have. What I won’t do is pretend there’s a universal answer, because the whole argument is that there isn’t one.

 Postgres Does More Than You Think → post 1, the series opener on Postgres capabilities.

Key takeaways

• B-tree is the right default for equality, range, and sort queries. It’s the wrong tool for containment, arrays, and full-text, no matter how many of them you stack.
• GIN pays off the moment you query inside a value: JSONB containment, array membership, text search. The price is slower writes and bigger maintenance windows.
• BRIN is nearly free to store and maintain, but it only works when physical row order correlates with the indexed column, which usually means append-only, time-ordered data.
• Partial and expression indexes shrink the problem instead of the query: index only the rows and the shapes your queries actually touch.
• Every index taxes every write to the table. If pg_stat_user_indexes shows zero scans, the index isn’t an asset, it’s rent.

Why is “just add an index” the wrong reflex?

Because an index is not a free lookup table, it’s a second data structure that Postgres must keep synchronized with your table on every insert, update, and delete. The read speedup is the visible half of the trade. The invisible half is write amplification, extra WAL, larger backups, longer vacuums, and a planner that now has more choices to get wrong.

That trade is worth making constantly. Indexes are the single highest-return tool in relational performance work, and nothing in this post argues otherwise. The argument is about matching, not abstinence. A B-tree on a JSONB column does nothing for containment queries. A GIN index on a plain timestamp column is waste. The index type has to answer the same question the query asks, or you’ve bought overhead and stored it in production.

The other half of the reflex problem is that indexes get added and never audited. Have you ever seen a migration that drops an index because nobody could prove it was used? I’ve reviewed schemas carrying fifteen indexes on a single hot table, several of them redundant prefixes of each other, each one silently taxing every write. The fix for a slow query became a permanent line item.

So the working rule for the rest of this post: an index earns its place when you can name the query pattern it serves, and it keeps its place only while pg_stat shows it being scanned. Everything below is just working out which type serves which pattern.

When is a B-tree index actually the right call?

Most of the time, which is exactly why it’s the default. A B-tree pays off whenever your query compares whole values with equality or range operators, or sorts by them: WHERE user_id = 42, WHERE created_at >= now() - interval '7 days', ORDER BY created_at DESC. It also backs every primary key and unique constraint, so you’re already running B-trees whether you chose them or not.

The decision that actually separates senior schema work from reflex work isn’t whether to use a B-tree. It’s column order in composite B-trees. Postgres can use a composite index efficiently from the leftmost column onward, so the order has to mirror how you filter:

-- Serves: WHERE tenant_id = $1 AND created_at >= $2 ORDER BY created_at DESC
CREATE INDEX idx_events_tenant_time
    ON events (tenant_id, created_at DESC);

-- Equality columns first, then the range or sort column.
-- Reversed order (created_at, tenant_id) would force a much wider scan.

 [PERSONAL EXPERIENCE] The multi-tenant composite ordering lesson comes from a real SaaS engagement where reversing two columns changed a dashboard query from seconds to single-digit milliseconds.

I once spent an afternoon on a multi-tenant dashboard query that had the right columns indexed in the wrong order. Swapping (created_at, tenant_id) to (tenant_id, created_at) took the query from seconds to milliseconds, with no other change. Same columns, same data, same B-tree. The only thing that changed was whether the index answered the question in the order the query asked it.

One footnote: Postgres also offers hash indexes, and since version 10 they’re crash-safe and occasionally slightly faster for pure equality. In practice the benefit is rarely worth managing a second index type that can’t help with ranges or sorts. I’d treat hash as trivia unless a benchmark on your workload says otherwise.

Where’s the line? A B-tree stops paying off the moment the question changes from “which rows have this value” to “which rows contain this value.” Containment in JSONB, membership in arrays, and word matching in text all leave B-tree territory. It’s also near-useless on low-cardinality columns like booleans, where scanning half the table through an index is slower than just scanning the table.

When does GIN pay off over B-tree?

GIN pays off the moment you query inside a value rather than comparing the whole value. That’s three workloads in most systems: JSONB containment, array membership, and full-text search. A GIN index stores an entry for every element inside the composite value, which is exactly why it can answer “which rows contain this key” without touching the heap for every candidate.

-- JSONB containment
CREATE INDEX idx_orders_meta ON orders USING gin (metadata);
SELECT * FROM orders WHERE metadata @> '{"channel": "mobile"}';

-- Array membership
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];

-- Full-text search
CREATE INDEX idx_docs_fts ON docs USING gin (to_tsvector('english', body));

The catch is symmetrical to the benefit. Because GIN indexes every element inside every value, writes are meaningfully more expensive than B-tree writes, index builds take longer, and the index itself is larger. GIN partially hides this with a pending list that batches new entries, but that list has to be flushed eventually, and the flush shows up as latency spikes if you never think about it. On a heavy-write table, an eager GIN index is a tax you pay on every insert to speed up a query you might run occasionally. Is that trade still positive for your workload? Sometimes yes. It deserves the question.

There’s also a planning subtlety worth knowing: GIN answers containment brilliantly but doesn’t help you rank or sort results. For full-text search that’s fine at modest scale, and I’ve argued before that Postgres search carries teams much further than they expect. Ranking millions of documents by relevance under load is a different job.

 Why Your RAG System Is Actually a Search Problem → post 4, where retrieval quality and hybrid search are covered in depth.

Where’s the line? GIN stops winning when write throughput is the constraint, or when the workload shifts from “find matching documents” to “rank matching documents by relevance with facets under real traffic.” That second job is where a dedicated search engine earns its operational cost. Until then, GIN on the column you actually query inside is usually the honest answer.

Is BRIN worth using, and where does it shine?

Yes, in one specific shape of data, and it’s almost embarrassing how cheap it is there. A BRIN index doesn’t store an entry per row. It stores a summary per block range: the minimum and maximum value in each chunk of physical pages. For a query like “give me last week’s rows” on a table where rows were inserted in time order, that summary lets Postgres skip nearly all of the table while the index itself stays tiny, often thousands of times smaller than the equivalent B-tree.

-- Append-only events table, rows arrive in time order
CREATE INDEX idx_events_created_brin
    ON events USING brin (created_at);

-- The index stores min/max per block range, so a range query
-- skips every block whose summary can't contain matching rows.

The word doing all the work in that paragraph is “physical.” BRIN only helps when the physical order of rows on disk correlates with the indexed column. Append-only logs, events, metrics, and audit trails have that property naturally, because time only moves forward and rows land in arrival order. The moment a table sees heavy updates and deletes, vacuumed space gets reused, new rows land in old pages, correlation decays, and every block range’s min/max widens until the index can’t exclude anything.

 [UNIQUE INSIGHT] Framing BRIN as a property of the table’s life story rather than a property of the column is the mental model that predicts when it degrades. –>

The mental model I use: B-tree indexes a column, BRIN indexes the table’s life story. If the story is “rows arrive in order and stay put,” BRIN is close to free performance. If the story involves churn, BRIN quietly stops working without ever erroring, which is worse than failing loudly. Check correlation in pg_stats before trusting it.

For genuinely large time-series workloads, this is also the on-ramp to a bigger conversation about partitioning and time-series storage, which deserves its own post later in this series.

Where’s the line? BRIN stops paying off when correlation between physical order and the column breaks: update-heavy tables, out-of-order backfills, or columns unrelated to insert order. It’s also the wrong tool for point lookups. It narrows the search to block ranges, it doesn’t find individual rows, so WHERE id = 42 still wants a B-tree.

What do partial and expression indexes buy you?

They buy you a smaller problem. A partial index covers only the rows matching a predicate, and an expression index covers a computed shape of a column instead of the raw value. Both are ways of saying: my queries don’t touch the whole table, so my index shouldn’t either.

The classic partial-index case is a status column where the interesting rows are a small minority. If 98% of jobs are done and every query polls for pending, indexing all statuses is mostly indexing rows nobody asks about:

-- Only pending jobs are ever polled
CREATE INDEX idx_jobs_pending
    ON jobs (created_at)
    WHERE status = 'pending';

-- Case-insensitive login lookups
CREATE INDEX idx_users_email_lower
    ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

The partial version is smaller, faster to scan, cheaper to maintain, and, this part surprises people, it skips index writes entirely for rows that don’t match the predicate. On a queue table where completed jobs vastly outnumber pending ones, that’s a real write saving, not just a storage one.

Expression indexes follow the same logic in a different direction. If every query wraps the column in lower(), a plain index on email never gets used, because the planner sees lower(email), not email. Index the expression your query actually contains, and the planner can match them. The rule for both features is exact matching: the predicate or expression in the index has to line up with what the query says, closely enough that the planner can prove it.

Where’s the line? Partial indexes stop paying off when the predicate no longer mirrors real query shapes, or when you’ve minted a bespoke partial index for every screen in the app. A dozen overlapping partials on one table is index sprawl wearing a clever disguise, and each one still needs the planner to pick it. If you can’t name the exact recurring query a partial index serves, it shouldn’t exist.

Can a covering index really let Postgres skip the table?

Yes, and when it works it’s the closest thing indexing has to a free lunch, with two honest conditions attached. Normally an index scan finds matching entries, then visits the heap to fetch the remaining columns. If the index itself contains every column the query needs, Postgres can run an index-only scan and never touch the table:

-- Query: SELECT status, total FROM orders WHERE customer_id = $1
CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id) INCLUDE (status, total);

The INCLUDE clause adds payload columns to the index leaves without making them part of the search key, so the index stays efficient to search while carrying the answer with it. For hot, narrow lookups, the ones that run thousands of times a minute and fetch two columns, this can remove most of the I/O from the query.

Condition one: visibility. Postgres can only skip the heap for pages it knows are all-visible, which is information maintained by vacuum. On a table with heavy churn, the visibility map is perpetually stale, and your “index-only” scan quietly does heap fetches anyway. EXPLAIN ANALYZE tells you the truth here, look at the Heap Fetches line, not the plan node name.

Condition two: weight. Every INCLUDE column makes the index physically bigger, and a bigger index means more WAL, slower writes, and less of it fitting in cache. Stuffing six columns into INCLUDE to chase index-only scans on a wide query usually costs more than it saves. Covering indexes are for narrow, frequent reads, not for smuggling the whole table into the index.

Where’s the line? Covering indexes stop paying off on update-heavy tables that never keep a clean visibility map, and on queries wide enough that the INCLUDE payload bloats the index. If the query needs half the row anyway, let it visit the heap. That’s what the heap is for.

What does every index cost you on writes?

Every index on a table adds work to every insert, most updates, and every delete on that table, and the bill compounds in ways that don’t show up in query benchmarks. Each new row must be entered into each index. Each update that touches an indexed column forces new index entries too, and, more subtly, defeats Postgres’s HOT optimization, which would otherwise let an update stay inside the same heap page without touching indexes at all. Indexing a frequently updated column doesn’t just slow that column’s updates, it makes every update to those rows more expensive.

Then there’s the operational tail: more WAL to ship, more to replay on recovery, bigger backups, longer vacuum passes, more index bloat to monitor. None of these are dramatic alone. Together they’re why an over-indexed hot table feels sluggish in ways nobody can attribute to a single query.

The good news is that Postgres tells you exactly which indexes are earning their keep:

SELECT schemaname, relname, indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;
-- idx_scan = 0 since the last stats reset means nothing reads it.
-- Confirm the stats window covers real traffic before dropping.

Run this quarterly. Anything with zero scans over a genuine traffic window, and no constraint duty, is a candidate to drop, after checking it’s not a unique index enforcing correctness or an index that only matters for a month-end job. What does an unused index actually give you? Slower writes, bigger backups, and a false sense of preparedness. This audit is also one of the quieter levers on your infrastructure bill, since write amplification and storage both convert directly into money on managed databases.

 Database Choices That Quietly Kill Your AWS Bill → post 5, on cost consequences of database decisions.

So how do you actually decide?

Four questions, asked in order, settle almost every indexing decision I see. The point isn’t ceremony. It’s that each question eliminates most of the remaining options.

1. What is the query actually asking? Whole-value equality, range, or sort points at B-tree. Looking inside a value, JSONB keys, array elements, words in text, points at GIN. A range over time-ordered, append-only data points at BRIN. Write the query first, then read the operator it needs. The operator names the index type.

2. Which rows does it ask about? If the answer is “a small, describable slice,” a partial index beats indexing the world. If the query wraps the column in a function, the expression is what needs indexing, not the column.

3. What’s the write profile of this table? A read-mostly table tolerates generous indexing. A hot write path makes every index type more expensive and makes GIN and wide covering indexes specifically questionable. If the table is both write-hot and query-hot, that tension is the design problem, and no index type dissolves it.

4. Did it actually get used? Confirm with EXPLAIN ANALYZE the day you add it, then check pg_stat_user_indexes after a week of real traffic. An index the planner ignores is indistinguishable from an index you never built, except that you’re paying for it.

Answer those four honestly and the remaining choices are details. Skip them and you’re back to the coin flip from the war room.

 When to Use Each AWS Database Service → post 6, the same decision-first approach applied at the service level.

The right handful, not the full set

The practical default hasn’t changed in decades and it’s still correct: a small number of B-tree indexes shaped by your actual query patterns, with composite column order matching how you filter. Reach for GIN when queries look inside values, BRIN when append-only data arrives in order, partial indexes when only a slice of rows matters, and INCLUDE when a hot narrow query can skip the heap. Five tools, each with one clear job.

The failure mode isn’t picking the wrong type once. It’s accumulation: indexes added under incident pressure, never matched to a named query, never audited, until the table pays double on every write to maintain structures nothing reads. That schema doesn’t look broken in any single EXPLAIN. It just gets slower everywhere, gradually, expensively.

An index pays off when it matches the question your query asks. Everything else is rent.

Frequently asked questions

• What is the default index type in PostgreSQL?

B-tree is the default index type in PostgreSQL, created whenever you run CREATE INDEX without specifying a method. It handles equality, range comparisons, sorting, and uniqueness, which covers most relational workloads. Primary keys and unique constraints are backed by B-tree indexes automatically, so every Postgres database already relies on them.

• When should I use GIN instead of B-tree in Postgres?

Use GIN when queries look inside composite values rather than comparing whole values: JSONB containment, array membership, and full-text search. GIN indexes each element inside a value, so it answers containment queries B-tree can’t. The trade-off is slower writes, longer builds, and a larger index, so apply it where those queries actually run.

• Do indexes slow down writes in PostgreSQL?

Yes. Every index must be updated on inserts, on deletes, and on updates that touch indexed columns, which adds I/O and WAL volume to each write. Indexing frequently updated columns also defeats HOT updates, making row updates more expensive overall. Read speedups usually justify this, but only for indexes that queries genuinely use.

• What is a partial index in PostgreSQL?

A partial index covers only rows matching a WHERE predicate defined at index creation, such as jobs with status pending. It’s smaller, cheaper to maintain, and skips index writes for non-matching rows entirely. The planner uses it only when the query’s condition provably matches the index predicate, so mirror real query shapes exactly.

• How do I find unused indexes in Postgres?

Query pg_stat_user_indexes and look for indexes with idx_scan at or near zero over a window of real production traffic. Sort by index size to prioritize the expensive ones. Before dropping, confirm the index isn’t enforcing a unique constraint and isn’t needed by periodic jobs like month-end reports outside your stats window.