Postgres Index Strategies, in depth: designing indexes from the workload, proving them with EXPLAIN and retiring them safely

Start from query shapes, not from columns

Indexing columns because they look important produces indexes nobody uses and misses the ones that matter. Start from the statements that cost the most total time, which pg_stat_statements records once the extension is loaded through shared_preload_libraries.

SELECT queryid,
       calls,
       round(total_exec_time)                 AS total_ms,
       round(mean_exec_time::numeric, 2)      AS mean_ms,
       rows / greatest(calls, 1)              AS rows_per_call,
       left(query, 120)                       AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

For each statement near the top, write down its shape: which columns are compared with equality, which with a range, which drive ORDER BY, how many rows it returns, and which columns it reads. The shape, not the SQL text, decides the index. Two queries with the same shape can share one index, and a table rarely needs more than a few shapes served.

The worked table and its four hot queries

Take a multi-tenant orders table with 200 million rows, 2,000 inserts a second at peak, and frequent status updates.

CREATE TABLE orders (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id    int         NOT NULL,
    customer_id  bigint      NOT NULL,
    status       text        NOT NULL,   -- 'pending', 'paid', 'shipped', 'cancelled'
    created_at   timestamptz NOT NULL DEFAULT now(),
    total_cents  bigint      NOT NULL,
    email        text        NOT NULL,
    updated_at   timestamptz NOT NULL DEFAULT now()
);

The percentages are what pg_stat_statements would show in this example. Together the four shapes cover over 90% of query time, so four well-chosen indexes plus the primary key is the target, not one per column.

The design loop

The loop matters more than any single rule. Each step has a concrete check, described in the sections that follow, and skipping the pricing or retirement steps is how tables end up with twenty indexes and slow inserts.

Composite indexes: column order is the design

A B-tree on several columns is sorted by its leading column, ties are ordered by the next column, and so on. A query can use a contiguous range of that order. The working rule follows: put columns compared with equality first, then the single column used for a range or a sort. For Q1 that gives:

CREATE INDEX CONCURRENTLY orders_tenant_status_created
    ON orders (tenant_id, status, created_at DESC);

-- Q1 is served as one contiguous index range, already in order:
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Within tenant 42 and status paid, entries are already sorted by created_at descending, so the plan reads the first 50 entries and stops, with no sort node. Swap the order to (created_at, tenant_id, status) and the same query must walk every recent entry for every tenant. Equality columns can appear in any order among themselves; pick the order that also serves other queries, because the index answers any query on a leading prefix too, such as WHERE tenant_id = 42.

PostgreSQL 18 added skip scan for B-tree indexes, which lets a multicolumn index help when there is no condition on a leading column, provided that column has few distinct values. It is a useful safety net for an occasional query, not a reason to ignore column order for a hot one. The page layout behind all of this is explained in B-tree indexes in depth.

Partial indexes for rare values

Q2 looks for pending orders, which are under 1% of rows at any moment. Indexing all 200 million rows by status to find a few hundred thousand is wasteful. A partial index stores only the rows that match its predicate:

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

SELECT id, tenant_id FROM orders
WHERE status = 'pending' AND created_at < now() - interval '15 minutes';

The index is a small fraction of the size of a full one, and rows that never become pending never touch it. The planner uses a partial index only when it can prove the query's WHERE clause implies the index predicate. A literal status = 'pending' qualifies. A parameter status = $1 in a prepared statement may not: once the plan cache switches to a generic plan the value is unknown and the partial index cannot be chosen. Write the rare value as a literal in the query that needs it, or check plan_cache_mode.

Expression indexes must match the query exactly

Q3 compares lower(email). An index on email cannot serve that, because the index stores original values. Index the expression itself:

CREATE INDEX CONCURRENTLY orders_lower_email ON orders (lower(email));
ANALYZE orders;   -- gathers statistics on the expression for the planner

SELECT id FROM orders WHERE lower(email) = lower('Ana@Example.com');

The query must use the same expression the index was built on. upper(email), or a citext column compared with =, will not match. The function must also be immutable. ANALYZE after creation matters because an expression index gets its own statistics, and without them the planner guesses the selectivity.

Covering indexes and index-only scans

Q4 reads total_cents for one customer over 90 days. With an index on (customer_id, created_at) the plan finds the entries quickly but then visits the heap for every row to read the total. Adding the column as a non-key payload lets the index answer alone:

CREATE INDEX CONCURRENTLY orders_customer_created_incl
    ON orders (customer_id, created_at) INCLUDE (total_cents);

INCLUDE columns are stored in leaf entries but are not part of the sort key, so they do not affect ordering. An index-only scan still checks the visibility map for each heap page, and pages modified since the last vacuum must be visited anyway. On a table with frequent updates the gain depends on autovacuum keeping the visibility map current; the mechanism is covered in index-only scans in depth.

Proving an index with EXPLAIN (ANALYZE, BUFFERS)

An index that exists is not an index that is used. Run the real query, with realistic parameters, and read the plan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(total_cents) FROM orders
WHERE customer_id = 9001 AND created_at >= now() - interval '90 days';

-- Shape of a good result (numbers are illustrative):
-- Aggregate (actual rows=1)
--   ->  Index Only Scan using orders_customer_created_incl on orders
--         Index Cond: ((customer_id = 9001) AND (created_at >= ...))
--         Heap Fetches: 3
--         Buffers: shared hit=7

Check four things. The node names the index you intended. The Index Cond line contains every column you meant to be searched, not a Filter line discarding rows after the fact. Heap Fetches is small relative to rows returned. And Buffers is close to the number of pages the answer needs. Compare buffers before and after the index: buffers are a more stable measure than milliseconds, which vary with cache state. When the planner ignores a good index, the cause is usually stale statistics or a misestimate, which query planner architecture explains.

Pricing the write cost

Every insert adds an entry to every index. With the primary key and the four indexes above, one insert writes five index entries plus the heap tuple, and each index's pages compete for the buffer cache. Updates are subtler. PostgreSQL can perform a heap-only tuple (HOT) update, which writes no new index entries, when no indexed column changes and the new tuple version fits on the same heap page. Changing any indexed column forces new entries in every index. Since PostgreSQL 16, columns covered only by summarizing indexes such as BRIN do not block HOT.

In the example, status is updated constantly and appears in orders_tenant_status_created, so status changes are never HOT. That is a real cost and an informed choice, because Q1 needs it. Adding updated_at to any index would make nearly every update non-HOT and is rarely worth it. Measure the HOT ratio and leave free space on pages so HOT has room:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / greatest(n_tup_upd, 1), 1) AS hot_pct
FROM pg_stat_user_tables WHERE relname = 'orders';

ALTER TABLE orders SET (fillfactor = 90);   -- applies to newly written pages

Building, rebuilding and retiring without downtime

A plain CREATE INDEX blocks writes to the table for the whole build. CREATE INDEX CONCURRENTLY does not, at the cost of two table scans, a wait for transactions that might see the old state, and the rule that it cannot run inside a transaction block. If it fails, for example on a deadlock or a uniqueness violation, it leaves an INVALID index that is still maintained on every write but never used. Check after every build:

SELECT indexrelid::regclass AS index, indisvalid, indisready
FROM pg_index WHERE NOT indisvalid;

-- Drop and retry, or rebuild in place (PostgreSQL 12+):
REINDEX INDEX CONCURRENTLY orders_lower_email;

-- Candidates for retirement: never or not recently scanned
SELECT indexrelid::regclass, idx_scan, last_idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

last_idx_scan exists from PostgreSQL 16 and tells you when an index was last used, which is more useful than a cumulative count. Usage statistics are per server, so an index unused on the primary may be the one your read replicas depend on: check every node before dropping. Also look for redundant prefixes. An index on (tenant_id) is redundant next to (tenant_id, status, created_at) unless it is much smaller and heavily used. Index bloat after heavy updates is covered in vacuum and bloat architecture.

Failure modes

• Index exists, plan ignores it. Usually stale statistics, a type or collation mismatch, a function wrapped around the column, or a parameter hiding a partial-index predicate.
• Wrong column order. A range column placed before an equality column; the plan scans far more entries than it returns, visible as high buffer counts.
• Silent INVALID index. A failed concurrent build adds write cost forever and serves nothing.
• Write throughput decay. Indexes added one incident at a time until inserts slow and HOT disappears.
• Dropped a replica's index. Usage checked on the primary only, then reporting queries on a replica fall to sequential scans.
• Lock pile-up. A non-concurrent build or a DROP INDEX without CONCURRENTLY during peak traffic queues every writer behind it.

Trade-offs at a glance

What to do next

1. Enable pg_stat_statements and list the top 20 statements by total time on each busy table.
2. Write the shape of each one and group statements that share a shape.
3. Design one candidate per group with equality columns first, then a range or sort column; use partial, expression or INCLUDE only where the shape calls for it.
4. Prove each candidate with EXPLAIN (ANALYZE, BUFFERS) on a production-sized copy, and record buffers before and after.
5. Check the HOT ratio before and after any index that covers a frequently updated column.
6. Build with CONCURRENTLY and check indisvalid every time.
7. Each quarter, review idx_scan and last_idx_scan on every node and drop indexes that no longer serve a shape.