PostgreSQL indexes built for real queries instead of every column

A new table arrives, someone adds an index to every column, and the schema review nods it through because indexes feel like safety. Six months later the same table takes complaints: writes are slow, autovacuum never catches up, and the query that started it all still reads a Seq Scan in the plan. Indexing everything is the most expensive way to design indexes, because every index charges rent on every write and pays out only on the queries it actually matches. This article builds the opposite discipline: start from the real queries, prove each index with the planner, use composite ordering, expressions and partial predicates where they fit, and retire what the statistics prove useless.

What an index costs every INSERT UPDATE and DELETE

An index is not a lookup structure that the server maintains for free. Every INSERT must add a matching entry to every index on the table. Every DELETE must mark one. And every UPDATE that touches a column covered by any index must maintain every index that column belongs to, because PostgreSQL stores the new row version as a new tuple and every index entry pointing at the old version needs a replacement.

This is where the HOT optimization from the MVCC discussion becomes an index design problem. When an UPDATE changes no indexed column and the new version fits on the same page, PostgreSQL keeps the update heap-only and touches no index at all. The moment an indexed column changes, that shortcut is gone and every index on the table pays the maintenance cost. A status column that flips from pending to paid looks harmless, but if that column lives in three indexes, every status flip now maintains three indexes, generates index bloat under concurrent traffic, and keeps autovacuum busy reclaiming the corpses.

The write amplification is linear in index count. A table with twelve indexes pays twelve maintenance operations per row change before the transaction even commits. On a logging table taking ten thousand inserts per second, that arithmetic decides whether the database keeps up with the producers or falls behind by the hour. The first question about any new index is not whether it might help a read, but which reads it will serve, because a maybe is not a reason.

Reading EXPLAIN ANALYZE BUFFERS before creating anything

The index that earns its place is the one whose absence shows up in a query plan. The workflow is strict: take the slow query, run it through the planner with buffers, read the plan, and only then decide. The command for a typical order lookup:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 4231 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

Three parts of the output decide the verdict. The node type, a Seq Scan on a large table or a Filter hanging off it, names the missing access path. The rows estimate against the actual rows shows whether the planner even trusts the statistics. The BUFFERS line, shared hit versus read, shows how much of the table was walked from cache or from disk. An index is justified when the plan reads the whole table to answer a question about a few rows, and the buffer counts make the waste measurable in pages.

The slow queries to investigate come from pg_stat_statements, the extension that accumulates execution counters per statement. Sorting its view by total_exec_time surfaces the queries that cost the fleet the most wall clock, and those are the queries worth indexing for. An index built for a query nobody runs is decoration, and decoration on a hot write path is a tax.

Composite index column order and the leftmost prefix rule

The most common real world predicate is compound: a customer, a status, a time window. The reflex is three single-column indexes, one per condition, and the planner sometimes even combines them with a BitmapAnd. The better answer is one composite index whose column order is chosen deliberately:

CREATE INDEX idx_orders_cust_status_created
    ON orders (customer_id, status, created_at);

A B-tree composite index serves any predicate that is a leftmost prefix of its columns. Queries on customer_id alone use it. Queries on customer_id and status use it. The full triple uses it. A query on status alone does not, because the index is sorted customer first, and a status condition without a customer condition is scattered across the whole structure.

Two rules produce a good order. Equality columns go first, because they pin the position inside the tree, and the range or sort column goes last, so the scan can walk matching entries in already sorted order. When the sort column sits last, the ORDER BY disappears from the plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 4231 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

The plan that used to read Seq Scan followed by Sort now reads Index Scan using idx_orders_cust_status_created, Backward, and the Sort node is gone because the index already provides the order. One structure answered the predicate and the ordering, which two single-column indexes could not do together.

Expression indexes for case insensitive and jsonb lookups

Sometimes the value the query searches for is not stored, it is computed from a stored value. The classic case is the case insensitive email login, where the application queries lower(email) but the index sits on the raw column and matches nothing:

SELECT * FROM users WHERE lower(email) = Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в браузере должен быть включен Javascript.';

The index that serves it is built on the same expression the query uses:

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

PostgreSQL accepts any expression whose result is stable and whose function is marked IMMUTABLE. That word is a contract: the function must return the same output for the same input forever, because the index will store the computed result once and trust it. lower qualifies, and now() famously does not, which is why an attempt to index it fails with a clear error. The lesson generalizes: the expression in the index must match the expression in the query letter for letter, or the planner will not recognize the pair.

The same mechanism handles jsonb, the type that makes PostgreSQL attractive for mixed schemas. A GIN index over the whole jsonb column supports containment queries:

CREATE INDEX idx_events_data ON events USING gin (data);

SELECT * FROM events WHERE data @> '{"type": "payment"}';

The at sign operator with containment is the query form GIN accelerates, and on event tables with millions of rows the difference between the index scan and the sequential scan is the difference between a dashboard that loads and one that times out. For equality on a single extracted key, a smaller expression index beats the full GIN:

CREATE INDEX idx_events_type ON events ((data->>'type'));

The choice between them is a size decision: the GIN indexes everything and answers many query shapes, the expression index answers one shape at a fraction of the maintenance cost.

Partial indexes covering indexes and the visibility map

Two index features shrink structures to exactly the workload. A partial index carries a WHERE clause and contains only the rows that satisfy it:

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

On a table where two percent of orders are pending at any time, this index holds two percent of the rows, fits in memory when the full index would not, and stays cheap to maintain because updates to completed orders never touch it. The queries it serves must use a predicate the index can verify, and the literal status = 'pending' in the WHERE of the query matches the literal in the index definition.

A covering index adds payload columns through INCLUDE, stored in the index but not part of its key:

CREATE INDEX idx_orders_cust_cover
    ON orders (customer_id) INCLUDE (status, total);

This targets the index-only scan, the plan where the query reads everything from the index and skips the heap entirely. The catch is the visibility map, the structure that records which heap pages contain only tuples visible to every transaction. Index-only scans rely on it, and the map is maintained by VACUUM, which means a table under heavy updates loses index-only scans gradually until autovacuum restores the map. A plan that says Index Only Scan with a large heap fetches count is the symptom, and the health check is watching n_dead_tup in pg_stat_user_tables rather than blaming the index.

Finding dead index weight with pg_stat_user_indexes

Indexes that once served queries that no longer exist never announce their retirement. The usage statistics do:

SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_stat_user_tables t ON t.relid = s.relid
ORDER BY pg_relation_size(s.indexrelid) DESC;

An index with idx_scan at zero across months of production traffic is a candidate for removal, and the candidate deserves the same rigor as a new index: check when the statistics were last reset, confirm the application truly stopped the query, and only then remove it. Removal on a live table uses DROP INDEX CONCURRENTLY, because the plain DROP takes a lock that blocks writes for the duration. Creation under load has the same consideration: CREATE INDEX CONCURRENTLY builds without blocking writes, at the cost of two table scans and a failure mode that leaves an invalid index behind, visible as indisvalid false in pg_index, waiting to be dropped and retried.

Index bloat itself has a remedy: REINDEX CONCURRENTLY rebuilds a structure in place since PostgreSQL 12, and on hot tables that churn indexed columns it belongs on the maintenance calendar the same way vacuum does.

The index strategy that grows with the application

The discipline that survives years of schema churn is not a fixed set of indexes, it is a loop: measure the real queries, build the minimum structure that serves them, prove it in the plan, and prune what the statistics expose. The loop runs on every release that ships new queries, and it needs one list of working rules:

1. Start from the slowest statements in pg_stat_statements, never from the list of columns;
2. Prefer one deliberately ordered composite index over several single column guesses;
3. Put equality columns first and range or sort columns last in every composite key;
4. Match expressions letter for letter between the query and any expression index;
5. Prove every new index with EXPLAIN ANALYZE BUFFERS before and after the change;
6. Review idx_scan quarterly and drop proven dead weight with DROP INDEX CONCURRENTLY.

An index is an answer to a question the application actually asks. The team that treats each one as a small engineering decision, justified by a plan and audited by statistics, ends with a schema that reads fast because it carries exactly the structures the queries need, and writes fast because it carries nothing else.

BRIN GiST and the index types that earn their niche

B-tree is the default answer, and two other types answer questions B-tree answers badly. BRIN, block range index, stores summaries per range of heap blocks instead of per row, minima and maxima of the indexed column, and it is microscopic compared to its table. On append only data whose physical order matches the indexed order, a time column on a log table that only ever receives rows at the end of time, the block summaries are exact enough to skip almost the entire heap for a time window query:

CREATE INDEX idx_events_brin ON events USING brin (created_at);

The index is a few megabytes where the B-tree would be gigabytes, the cost is that rows updated in place break the correlation and degrade the skipping. The diagnostic is the correlation column in pg_stats, values near one mean physical and logical order agree and BRIN is a candidate, values near zero mean the heap is shuffled and BRIN is useless.

GiST serves the predicates B-tree cannot express, ranges, geometric containment and nearest neighbor. A booking table with an exclusion constraint that prevents overlapping reservations for the same resource:

CREATE EXTENSION btree_gist;
ALTER TABLE bookings
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (
    resource_id WITH =,
    during WITH &&
  );

The constraint is enforced by the index at insert time, and it turns a class of application level double booking bugs into database level refusals, the same argument the unique constraint made for duplicates. The index vocabulary is a menu of data shapes, and the schema that picks from the menu deliberately answers questions the default cannot even pose.