Building Indexes Safely, and the Ones You Don't Need

A plain CREATE INDEX on orders blocks every write to the table until it finishes. At 40 million rows that is minutes, and at 3,000 orders a minute on a Saturday morning it is thousands of failed checkouts caused by a statement that looked like a read-only improvement. CREATE INDEX CONCURRENTLY does not block writes, at the price of two table scans, a longer build, and a failure mode that leaves a broken index behind.

The other half of the topic is the more valuable one. Before adding an index to orders, the higher-return exercise is finding the three that nothing has scanned since February — because every one of them is paid for on every insert, in WAL volume, in replay on pg-replica-a, and in vacuum time, and none of them has returned a row to a customer this year.

What a Plain CREATE INDEX Blocks

A normal index build takes a SHARE lock on the table and reads it once. SHARE conflicts with ROW EXCLUSIVE, the lock every INSERT, UPDATE and DELETE takes, so writers do not error — they queue. Readers are unaffected, which is exactly why the incident is confusing: the site looks up, dashboards look normal, and only the write path is on the floor.

The second effect is worse than the first and less well known: a lock request waits behind the queue in front of it, so the pending SHARE lock also blocks writers that arrive while the build is merely waiting to start. Chapter 3 made the same point about ALTER TABLE. The shape of the outage is identical, and so is the mitigation: lock_timeout on the session running the build.

CONCURRENTLY, Mechanically

The concurrent form trades wall-clock time for availability. Postgres makes two passes over the table instead of one, and between and around them it waits for every existing transaction that could modify or use the index to finish. It is more total work and takes significantly longer, and it lets normal operations continue while it happens, which is the whole point on a table the size of orders.

-- SHARE lock: reads continue, every write queues for minutes
CREATE INDEX orders_customer_history_idx
    ON orders (customer_id, placed_at DESC)
    INCLUDE (public_id, status, total);

-- SHARE UPDATE EXCLUSIVE: two passes, longer, no blocked writes
CREATE INDEX CONCURRENTLY orders_customer_history_idx
    ON orders (customer_id, placed_at DESC)
    INCLUDE (public_id, status, total);

That has two operational consequences. Only one concurrent build can run on a given table at a time, so a migration that creates four indexes on orders runs them one after another and takes four times as long — plan the window accordingly. And CREATE INDEX CONCURRENTLY cannot run inside a transaction block, which breaks every migration framework that wraps each migration in BEGIN by default. The correct fix is to configure the tool to run that statement outside a transaction. The fix people reach for at 17:40 on a Friday is deleting the word CONCURRENTLY, and that is how the outage in the previous section happens.

The Invalid Index

A concurrent build can fail — a deadlock, a cancelled session, a uniqueness violation discovered on the second pass. When it does, the statement fails but leaves an index behind, marked invalid in the catalogue. The manual describes exactly what that object is: ignored for querying because it might be incomplete, and still consuming update overhead. It is the worst combination available. Every insert maintains it and no query benefits.

SELECT c.relname AS index_name, t.relname AS table_name
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
  JOIN pg_class t ON t.oid = i.indrelid
 WHERE NOT i.indisvalid
 ORDER BY t.relname;

DROP INDEX CONCURRENTLY orders_customer_history_idx;

The recommended recovery is exactly what it looks like: drop it and run the concurrent build again. The one alternative is REINDEX INDEX CONCURRENTLY, which the documentation notes is the only form able to rebuild an invalid index concurrently. Put that query in the monitoring that already runs against the cluster, because the failure is silent by construction — the migration reported an error weeks ago, somebody retried it successfully, and the corpse from the first attempt is still being maintained on every write.

Removal has a concurrent form too, and it carries its own caveats: DROP INDEX CONCURRENTLY takes one index name, does not support CASCADE, cannot run in a transaction block, and cannot be used on a partitioned table. The CASCADE restriction has a consequence people meet by surprise: an index backing a UNIQUE or PRIMARY KEY constraint cannot be dropped this way at all, because dropping it means dropping the constraint. That is ALTER TABLE … DROP CONSTRAINT, and it takes ACCESS EXCLUSIVE.

REINDEX CONCURRENTLY

A bloated index, whether from the half-full leaf pages left behind by random key insertion or from the aftermath of a bulk delete, is rebuilt rather than vacuumed back into shape. A plain REINDEX locks out writes to the parent table and takes an ACCESS EXCLUSIVE lock on the index itself, which blocks even the reads that would have used it. On a live table that is the same outage as a plain build, arriving during what someone described as routine maintenance.

REINDEX CONCURRENTLY, available since 12, holds only SHARE UPDATE EXCLUSIVE and works through a sequence of six transactions: a transient index is registered, built in a first pass, brought up to date in a second, swapped in by flipping validity flags, and only then is the old one dropped. Two requirements come with it. Both copies exist on disk simultaneously, so a 40 GB index needs 40 GB of headroom before you start. And a failure leaves an invalid leftover named with a _ccnew or _ccold suffix, which is the same standing check as before, looking for the same catalogue flag.

This is the routine remedy after a collation change, after heavy random-key insertion, and as the index-side half of a bloat cleanup — the table side is Chapter 7's subject, and the two are usually scheduled together.

Finding the Indexes No Query Uses

pg_stat_user_indexes counts scans per index in idx_scan, and since 16 it also records last_idx_scan, the time of the most recent one. The pair is what makes the audit trustworthy: a count of zero on its own is ambiguous, and a date is not.

SELECT s.indexrelname,
       s.idx_scan,
       s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       (SELECT stats_reset FROM pg_stat_database
         WHERE datname = current_database()) AS counting_since
  FROM pg_stat_user_indexes s
 WHERE s.relname = 'orders'
 ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC;

Always read the counter next to the date the counters were reset. An index that backs the month-end finance report shows zero scans for 29 days out of 30, and deleting it in week three is a self-inflicted incident that only shows up at month end. On Cartwheel's orders the audit returns three genuine zeros: orders_total_idx, orders_customer_id_status_idx and orders_status_placed_at_idx, added during three separate investigations and never used by a plan since. There is also one duplicate. orders_customer_id_idx is a strict prefix of orders_customer_id_placed_at_idx and answers nothing the composite does not.

There are two exemptions before anything is dropped. A unique index enforcing a constraint may show zero scans and is still doing its job on every insert, and it cannot be dropped as an index anyway. The same is true of the GiST index behind an EXCLUDE constraint. Everything else on that list is four write amplifications per insert that Cartwheel is paying for nothing. Removing them gives back write throughput, WAL volume, vacuum time and replay work on the replica.

Rolling It Out Safely

Index changes stop being risky when they stop being one statement and become a procedure. Build the new index with CONCURRENTLY, in a window where a long-running build is acceptable and no other DDL is running against the same table. Verify with EXPLAIN that the real application query, parameters and all, actually chooses it. Watch idx_scan on the new index climb and idx_scan on the one it replaces stop climbing, over days rather than minutes. Then drop the old one with DROP INDEX CONCURRENTLY.

That is four steps, days apart, each of them reversible until the last. The reason to insist on the order is that steps two and three are where you find out the new index does not do what you thought — a predicate the planner cannot prove, a sort direction that does not match, a covering index defeated by one extra column in the select list. Finding that out while the old index is still there is a correction. Finding it out afterwards is an incident. With the index set finally honest, the next question is why the planner picks what it picks, and that is where Chapter 9 starts.