The five Postgres indexes I actually use

Almost every performance problem I have been called in to look at was not a missing index. It was four indexes nobody had looked at in two years, one of which was doing all the work while three of them quietly doubled the cost of every insert. What follows is not a tour of everything Postgres can build. It is the five types that have earned their place in systems I run, with the failure mode of each spelled out, because the failure mode is the part that costs money.

B-tree: the one you need, in the order you need it

A B-tree is the default, and it earns that. Equality, ranges, sorting, prefix matching — it does all of them on scalars. If you build one index and stop, build this one. The interesting decisions are all about column order.

Postgres can use a composite index on (a, b) for predicates on a alone, or on a and b together. It cannot use it efficiently for a predicate on b alone. That is the leftmost prefix rule, and it causes more "but there is an index on that column" conversations than anything else. Order also decides whether a sort node exists: with (tenant_id, created_at) a query filtered by tenant and ordered by created_at walks the index in order, while (created_at, tenant_id) makes the same query read scattered pages and then sort.

(a, b) and (b, a) are different tools

The rule I use: equality columns first, in ascending order of cardinality, then the range or sort column last. A predicate on (status, region, created_at) wants created_at at the end because it is the one being ranged over.

-- The workhorse. Equality, then the sort column.
CREATE INDEX CONCURRENTLY orders_tenant_created_idx
  ON orders (tenant_id, created_at DESC);

-- Covering a hot read path without widening the tree.
CREATE INDEX CONCURRENTLY orders_tenant_created_cover_idx
  ON orders (tenant_id, created_at DESC)
  INCLUDE (status, total_cents);

INCLUDE columns live in the leaf pages but not in the search keys, so they enable index-only scans without changing the sort order the index provides, and cost less than making those columns keys — though every leaf tuple still gets wider.

Traps I have fallen into

• A low-cardinality leading column. An index on (status, created_at) where status has four values is usable; an index on status alone is a table scan with extra steps.
• A B-tree on a boolean. Almost always useless. With two distinct values across millions of rows, reading the index and then the heap costs more than reading the heap. If you want "the unprocessed rows", that is a partial index, not an index on is_processed.
• Case-insensitive lookups. An index on lower(email) is only usable by queries written as WHERE lower(email) = lower($1), because the planner matches the expression tree, not your intent. The citext type makes the comparison transparent, at the cost of a non-core type. On a system I inherited, a fifth of read traffic was doing sequential scans because the application had started sending mixed-case addresses against a functional index built for lowercase ones. We fixed the query, not the schema.
• One index per column. Each is a separate cost on every write. Two columns queried together usually want one composite index, plus a leftover single-column index only if a genuinely different query shape needs it.

Partial indexes: the highest return per byte

If there is one technique I would teach first, it is this. A partial index covers only the rows matching a predicate. On the right table that turns a 40 GB index into a 400 MB one that fits in cache, which changes a query from "reads a lot of pages" into "reads a few".

-- Soft deletes: only live rows are ever queried by the app.
CREATE INDEX CONCURRENTLY users_active_email_idx
  ON users (lower(email))
  WHERE deleted_at IS NULL;

-- A job queue where only a tiny slice is pending at any moment.
CREATE INDEX CONCURRENTLY jobs_pending_idx
  ON jobs (run_after, id)
  WHERE status = 'pending';

The queue example is where this stops being a nicety. On a table holding four million completed jobs and a few thousand pending ones, the partial index stays small enough to remain in cache under load, and the worker's claim query touches two or three pages instead of hundreds. I have watched that query go from 180 ms to under 2 ms with no application change whatsoever. Writes get cheaper too: a job moving from pending to done removes an index entry rather than adding one.

When the planner will not use it

The planner only uses a partial index if it can prove your query's predicate implies the index's predicate. It cannot prove what it does not know at plan time, which in practice means:

• Parameterised predicates. A generic plan tests whether status = $1 implies status = 'pending' before the value of $1 is known, and the answer is no. The literal 'pending' implies it cleanly. This is the most common reason a partial index sits unused, and it is why I write the worker's claim query with a literal, or with a prepared statement whose plan was built with the value inlined.
• Predicates that are equivalent to a human but not to the planner. COALESCE(deleted_at, 'infinity') > now() looks like deleted_at IS NULL. It is not, as far as the planner is concerned.
• OR branches. A query with an OR across columns needs a partial index per branch, or it falls back to a scan.

Do not guess — run EXPLAIN and look for the index name. If it is absent, the predicate did not match, and the fix is almost always to make the query's predicate syntactically identical to the index's. Use CREATE INDEX CONCURRENTLY on any production table: it is slower, it cannot run inside a transaction block, and a cancelled build leaves an invalid index that has to be dropped. Check pg_index.indisvalid afterwards and give it a generous timeout.

Covering indexes, index-only scans, and the visibility map

An index-only scan means Postgres answers the query from the index without visiting the heap — the payoff of INCLUDE, and a large one. On a warm table, a query that touched 300 heap pages can drop to reading 12 index pages.

Except that "index only" is a slight lie. Postgres must still check that each row it found in the index is visible to your snapshot, and visibility lives in the heap, as a bit in the visibility map. If the bit is set, the heap page is known to contain no dead tuples relevant to anyone and the visit is skipped. If it is not set, Postgres goes to the heap anyway and your index-only scan becomes an ordinary index scan with extra bookkeeping.

That is why the first read after a bulk load is slow and the second is fast. A COPY of ten million rows leaves the visibility map almost empty, so the first query reports millions of Heap Fetches in EXPLAIN (ANALYZE). Then VACUUM runs, sets the bits, and the same query does hundreds. If you build a covering index for a hot path and it does not help on a freshly loaded table, this is why.

EXPLAIN (ANALYZE, BUFFERS)
SELECT tenant_id, created_at, status, total_cents
FROM orders
WHERE tenant_id = 4711
  AND created_at >= now() - interval '7 days';

-- Index Only Scan using orders_tenant_created_cover_idx
--   Heap Fetches: 0        <-- this is the number that matters

The other half of the story is cost. A covering index with four INCLUDE columns can be two or three times the size of the plain one, and every update to any of those columns writes new index tuples — the HOT optimisation is unavailable once a column is in an index. On a high-write table I have seen a "helpful" covering index add 15% to p99 commit latency while removing 40 ms from a reporting query that ran twice an hour.

Index bloat makes it worse: on the worst table I have measured, an 8 GB index held perhaps 2.5 GB of live data. REINDEX CONCURRENTLY is the tool, and it is worth scheduling on your hottest few indexes rather than waiting for an incident.

GIN: for anything that is not a scalar

The query shapes that matter here are containment and prefix matching, and both are GIN's home ground: @> on jsonb, the array operators, tsvector search and pg_trgm similarity. What GIN does not do is cheap writes. It indexes each element inside the value, so a document with twenty keys produces twenty-plus index entries.

jsonb_path_ops versus the default

For jsonb you have two operator classes. The default, jsonb_ops, indexes every key and every value, supporting @>, ?, ?& and ?|. jsonb_path_ops indexes hashes of paths to values and supports only containment — and is typically two to three times smaller and meaningfully faster for it. That is what almost everyone actually writes.

CREATE INDEX CONCURRENTLY events_payload_idx
  ON events USING gin (payload jsonb_path_ops);

-- Containment only. This is the query shape that benefits.
SELECT id FROM events WHERE payload @> '{"kind":"checkout"}'::jsonb;

If you need the ? key-existence operator, you need the default class and should budget for the size. A second expression index with the other class is possible if you genuinely need both query shapes.

fastupdate and the pending list

GIN's write path surprises people. With fastupdate = on — the default — new entries go into an unsorted pending list rather than into the main structure, and reads scan that list linearly and merge it with the index results. When it exceeds gin_pending_list_limit (4 MB by default) it is flushed into the main structure, which is expensive and happens at a moment nobody chose. The consequence is latency that does not look like a load curve: fast, fast, fast, then slow for a second during a flush, then fast again. I have lost an afternoon to that pattern more than once. Lower gin_pending_list_limit so flushes are smaller and more frequent, call gin_clean_pending_list() from a maintenance job at a quiet hour, or turn fastupdate off on a table where write latency is already the problem.

GIN entries are also expensive to update: changing one key inside a jsonb column re-indexes every element for that row. On the events table above, dropping from five indexes on that table to one GIN index cut average insert time by about 60%. Nothing about the application changed.

BRIN: cheap, and only if your data is physically ordered

BRIN stores, for each range of table pages, only the minimum and maximum value of the column. A 500 GB log table might need 40 MB of BRIN to cover a timestamp column. Nothing else in Postgres has that storage ratio, and for append-only data it is close to free performance.

CREATE INDEX CONCURRENTLY events_occurred_brin_idx
  ON events USING brin (occurred_at)
  WITH (pages_per_range = 32);

pages_per_range is the only real tuning knob, and it defaults to 128. Smaller values mean finer summaries and more index entries to scan; larger values mean coarser summaries and more heap pages read per match. If a range summary covers data you do not want, every page in that range is read and rechecked, so this is the difference between reading two pages and reading two thousand. For a table where rows land at the end and old rows are seldom touched, 32 or 64 is usually right.

The failure mode is worth stating plainly: BRIN only works when the physical order of rows correlates with the column's order. If rows arrive in random timestamp order — an ingest pipeline that backfills asynchronously, say — then every range contains a spread of values, every range matches everything, and BRIN degrades into a full table scan plus overhead. Measure before you build:

SELECT attname, correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'events'
ORDER BY abs(correlation) DESC;

A correlation near 1 or -1 means BRIN will behave. Near 0 means it will not: fix the write pattern, CLUSTER once and accept that it decays, or use a B-tree. I have seen a BRIN index scan as many pages as the table contained while still charging maintenance on every insert.

Finding the indexes that are dead weight

Every index you own is a tax on writes and a claim on cache. Dropping one is the cheapest performance work available, and it is the work nobody schedules. The evidence lives in pg_stat_user_indexes, which counts scans per index since the last statistics reset. An index with idx_scan = 0 after a full traffic cycle — including the month-end reports and the batch jobs — is a candidate.

SELECT s.schemaname,
       s.relname AS table_name,
       s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan,
       i.indisunique,
       i.indisvalid
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Three caveats before you drop anything. Statistics reset — check stats_reset in pg_stat_database and confirm the window covers your rarest query. A zero-scan index may still be serving a foreign key's referential check or a constraint you have forgotten. And indisvalid = false means pure overhead — a bug, not a tuning opportunity.

The complement is pg_stat_statements, which tells you what actually runs. Order by total execution time, not mean time: a query that runs 400,000 times at 3 ms each is a bigger problem than one that runs twice a day for 4 seconds. Then check the worst few with EXPLAIN (ANALYZE, BUFFERS). Plans that mention no index are candidates for a new one, and there is open-source tooling that reads pg_stat_statements, extracts predicates and suggests indexes — worth running against a staging copy of production statistics, but treat its output as hypotheses. A suggested index you have not measured is a guess with a CREATE INDEX in front of it.

Type	Best for	Storage	Write cost	Main failure mode
B-tree	Equality, ranges, sorting, prefixes on scalars	Moderate; roughly the size of the indexed columns	Low per row, paid on every insert and relevant update	Wrong column order, so the leftmost prefix rule rules it out for the query you care about
Partial B-tree	A small hot subset of a large table	Fraction of the full index, proportional to matching rows	Lowest — non-matching rows are not maintained	Planner cannot prove the predicate, so it goes unused with parameterised queries
Covering B-tree	Read-heavy paths that must avoid the heap	Wider leaf pages; often 2–3× the plain index	High — removes HOT updates and widens every leaf tuple	Heap fetches because the visibility map is stale; bloat from updates
GIN	jsonb, arrays, tsvector, trigrams	Large; one entry per element inside each value	High and bursty — pending list flushes cause latency spikes	Read latency spikes from the pending list; slow updates on wide documents
BRIN	Append-only tables whose physical order matches the column	Tiny — megabytes where B-tree needs gigabytes	Very low	Low physical correlation makes every range match everything

The order I work in on an unfamiliar database is always the same: find the queries consuming the most total time, find out why their plans are bad, then look at which indexes exist for those tables. Adding is the last resort, not the first. Two of the largest wins I have had in the last three years were a partial index replacing a three-column composite, and dropping six indexes that had accumulated on a table where every insert was contended.

An index is a promise you make to one query and charge to every write. Most databases I meet are carrying promises to queries that no longer exist.

From a capacity review I wrote in 2022, and have quoted at myself ever since

None of this is exotic: five index types, one habit, and a willingness to delete things you built. The databases I am happiest with are the ones where somebody, once a quarter, runs the query above and has the nerve to act on it.