Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

intermediatepg-index~35 min

Write latency rose 30 percent after an index build that everyone believed had been rolled back

Reported symptoms

  • ●INSERT latency on the events table rose from 0.9 ms to 1.2 ms overnight and has stayed there for six weeks
  • ●The rise began during a maintenance window in which a CREATE INDEX CONCURRENTLY was started and then cancelled
  • ●The engineer who cancelled it reported that the index had been rolled back and no index was created
  • ●Query plans on the table are unchanged - nothing is using a new index, which appeared to confirm the rollback
  • ●Table size grew by 22 GB during the same window and has not come back
  • ●The intended query is still slow, because it still has no usable index
  • ●psql \d on the table shows an index the team does not recognise, with the word INVALID after it

Evidence

  • · pg_index for the table shows one entry with indisvalid = f, indisready = t and indislive = t
  • · pg_relation_size on that index is 22 GB, so it is not an empty catalog stub
  • · EXPLAIN on the query the index was built for shows a Seq Scan, confirming the planner will not use it
  • · pg_stat_user_indexes shows idx_scan = 0 and idx_tup_read = 0 for that index since it was created
  • · The index size grew measurably during a subsequent bulk insert, so it is being maintained on write
  • · psql \d output lists the index with the suffix INVALID
  • · The server log for the maintenance window contains ERROR: canceling statement due to user request from the CREATE INDEX CONCURRENTLY session
  • · A comparable index that failed on a duplicate key instead of a cancellation was left with indisvalid = f, indisready = f and a size of 0 bytes
Diagnosis and resolutionclick to reveal

Root cause

A cancelled `CREATE INDEX CONCURRENTLY` does not clean up after itself, and the wreckage it leaves is not inert. `CREATE INDEX CONCURRENTLY` builds in several phases so that it never blocks writers. Between phases it marks the index ready for maintenance before it is valid for querying. If the statement is cancelled after that point, PostgreSQL leaves the index in place with `indisvalid = false` and `indisready = true`. Those two flags mean precisely this: - `indisvalid = false` — the planner will not use it. It cannot: the index may be missing entries for rows written during the interrupted build, so answering a query from it could return wrong results. - `indisready = true` — every `INSERT`, `UPDATE` and `DELETE` must still maintain it. That is what makes it *possible* to finish the build later, and it is why the entry has to be kept up to date. So the index is pure cost. It occupies 22 GB, it is written on every modification, and no query will ever read it. The 30 percent write regression is the maintenance work; the 22 GB is the index. Everything the team observed was consistent with a successful rollback. No plan changed, because the planner ignores it. No query got faster, because the planner ignores it. The only visible traces are the word `INVALID` in `\d` output and the `indisvalid` column, and neither is anywhere a person routinely looks. It is worth knowing that the other common failure mode looks different. A `CREATE INDEX CONCURRENTLY` that fails on a duplicate key never reaches the ready state: it is left with `indisvalid = false`, `indisready = false`, and a size of 0 bytes. That one is genuinely inert — untidy, but costing nothing. Distinguishing the two is the difference between a cleanup task and a live regression.

Remediation

Find every invalid index in every database. This query is short, cheap, and belongs in your regular checks: ```sql SELECT n.nspname AS schema, t.relname AS table, c.relname AS index, i.indisvalid, i.indisready, pg_size_pretty(pg_relation_size(c.oid)) AS size FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid JOIN pg_class t ON t.oid = i.indrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE NOT i.indisvalid ORDER BY pg_relation_size(c.oid) DESC; ``` Read `indisready` and the size before choosing what to do: - `indisready = false`, size 0 — inert. Drop it when convenient. - `indisready = true`, non-zero size — costing you writes right now. Act. For an index you still want, `REINDEX INDEX CONCURRENTLY` completes the job in place and does not block: ```sql REINDEX INDEX CONCURRENTLY events_pad_idx; ``` This is usually the right move: you wanted the index, the build was interrupted, and this finishes it without a second decision about naming or definition. For an index you no longer want, drop it without blocking: ```sql DROP INDEX CONCURRENTLY events_pad_idx; ``` Use `CONCURRENTLY` on the drop as well. A plain `DROP INDEX` takes `ACCESS EXCLUSIVE` on the table, which forms the same lock queue as any other heavy statement. While you are there, look for the other index faults that hide in the same place — indexes nothing reads, and duplicates: ```sql SELECT relname AS table, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC; ``` Treat `idx_scan = 0` as a question rather than an answer: statistics reset when the cluster does, and an index supporting a quarterly job will read as unused for most of the quarter. Check `stats_reset` in `pg_stat_database` before drawing conclusions.

Verification

The invalid-index query returns no rows, or only rows you have deliberately chosen to keep. If you rebuilt it, the index is now valid and the planner uses it: ```sql SELECT c.relname, i.indisvalid, i.indisready FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid WHERE c.relname = 'events_pad_idx'; EXPLAIN (COSTS OFF) SELECT * FROM events WHERE pad = 'value'; ``` A rebuilt index returns `indisvalid = t` and the plan changes from `Seq Scan` to an index scan. Both halves matter — a valid index the planner still ignores is a different problem. If you dropped it, `INSERT` latency returns to its previous value and the 22 GB is returned to the filesystem. Measure the latency rather than assuming it: this is the only direct confirmation that the index was the cause of the regression. `pg_stat_user_indexes` shows `idx_scan` climbing for the rebuilt index. Zero scans on a valid index means the planner still prefers something else, and the index build was wasted work even though it succeeded.

Prevention

**Check for invalid indexes after every maintenance window**, and on a schedule. The query is cheap and the failure is silent by construction. **Alert on `indisvalid = false`.** There is no metric for this in most monitoring stacks, and the cost of one that is `indisready` is continuous. **Never assume a cancelled `CREATE INDEX CONCURRENTLY` cleaned up.** It does not. The runbook step for cancelling one must end with "then check `pg_index` and drop or rebuild what is left". **Read `\d` output after DDL.** The `INVALID` suffix is right there, and it was there for six weeks. **Use `DROP INDEX CONCURRENTLY`**, not plain `DROP INDEX`, on a live table. **Watch write latency after every index change**, in both directions. An index makes reads faster and writes slower, always. That trade is the reason to add one and it is a cost that must be measured, not assumed to be negligible. **Audit unused indexes periodically**, using `idx_scan` alongside `stats_reset`, and treat a zero as a question. An unused index has the same write cost as an invalid one and is far more common. **Prefer `REINDEX INDEX CONCURRENTLY` to drop-and-recreate** when an index needs rebuilding. It keeps the definition, the name, and the dependencies, and it does not leave a window with no index at all.

Reported symptoms

INSERT latency on the events table rose from 0.9 ms to 1.2 ms overnight, six weeks ago, and has stayed there.

The rise began during a maintenance window in which a CREATE INDEX CONCURRENTLY was started and then cancelled. The engineer reported that the index had been rolled back and no index was created.

Query plans on the table are unchanged — nothing uses a new index, which appeared to confirm the rollback. The intended query is still slow, because it still has no usable index.

Table size grew by 22 GB during the window and has not come back.

psql \d on the table shows an index nobody recognises, with the word INVALID after it.

Evidence provided

Read-only / Safewhat a cancelled CREATE INDEX CONCURRENTLY leaves in the catalog
$ psql -c "SELECT c.relname, i.indisvalid, i.indisready, i.indislive, pg_size_pretty(pg_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_index i ON i.indexrelid=c.oid WHERE i.indrelid='t'::regclass;"
  relname  | indisvalid | indisready | indislive | size  
-----------+------------+------------+-----------+-------
t_pad_idx | f          | t          | t         | 21 MB
t_pkey    | t          | t          | t         | 49 MB

It is not a catalog stub — that is 21 MB of real index in the reproduction, and 22 GB in the incident.

Read-only / Safethe planner will not touch it
$ psql -c "EXPLAIN (COSTS OFF) SELECT * FROM t WHERE pad = repeat('y',200);"
 QUERY PLAN 
------------
Seq Scan on t
 Filter: (pad = 'yyyyyyyy...yyyy'::text)
(2 rows)
Read-only / Safebut every write still maintains it
$ psql -c "SELECT pg_size_pretty(pg_relation_size('t_pad_idx'));" -c "INSERT INTO t ... 200000 rows" -c "SELECT pg_size_pretty(pg_relation_size('t_pad_idx'));"
 before_insert 
---------------
21 MB

INSERT 0 200000

after_insert 
--------------
22 MB

And pg_stat_user_indexes confirms nothing has ever read it:

 indexrelname | idx_scan | idx_tup_read | size
--------------+----------+--------------+-------
 t_pad_idx    |        0 |            0 | 22 MB

The server log for the window contains ERROR: canceling statement due to user request from the CREATE INDEX CONCURRENTLY session.

Work the evidence before reading on

  1. indisvalid = f and indisready = t. What does each flag control?
  2. No plan changed and no query got faster. Why is that consistent with the index still existing?
  3. The index grew during an insert. What does that cost?
  4. Where would you have to be looking to have caught this on day one?

Root cause

A cancelled concurrent build leaves something that is not inert

Everything observed was consistent with a successful rollback

No plan changed, because the planner ignores it. No query got faster, because the planner ignores it. The 30 percent write regression is the maintenance work and the 22 GB is the index — and neither was attributed to a build everybody believed had been undone.

The only visible traces are the word INVALID in \d and the indisvalid column. Neither is anywhere a person routinely looks.

Resolution

Find every invalid index, in every database:

SELECT n.nspname AS schema, t.relname AS table, c.relname AS index,
       i.indisvalid, i.indisready,
       pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid
ORDER BY pg_relation_size(c.oid) DESC;

Read indisready and the size before choosing:

indisreadySizeMeaningAction
false0 bytesInertDrop when convenient
truenon-zeroCosting writes nowAct

For an index you still want, finish the job in place:

REINDEX INDEX CONCURRENTLY events_pad_idx;

This is usually right: you wanted the index, the build was interrupted, and this completes it without a second decision about naming or definition. On the reproduction it returned the index to indisvalid = t.

For one you no longer want:

DROP INDEX CONCURRENTLY events_pad_idx;

While you are there, look for the neighbouring fault — indexes nothing reads:

SELECT relname AS table, indexrelname AS index, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Treat idx_scan = 0 as a question, not an answer. Statistics reset when the cluster does, and an index supporting a quarterly job reads as unused for most of the quarter. Check stats_reset in pg_stat_database first.

Verification

The invalid-index query returns no rows, or only rows you deliberately kept.

If you rebuilt it, both halves must hold — valid and used:

SELECT c.relname, i.indisvalid, i.indisready FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid WHERE c.relname = 'events_pad_idx';

EXPLAIN (COSTS OFF) SELECT * FROM events WHERE pad = 'value';

A valid index the planner still ignores is a different problem, and worth knowing about before you close the ticket.

If you dropped it, measure INSERT latency returning to 0.9 ms and the 22 GB coming back. That measurement is the only direct confirmation that the index caused the regression.

idx_scan climbs for a rebuilt index. Zero scans on a valid index means the build was wasted work even though it succeeded.

Prevention

Check for invalid indexes after every maintenance window, and on a schedule. The query is cheap and the failure is silent by construction.

Alert on indisvalid = false. Most monitoring stacks have no metric for it, and the cost of one that is also indisready is continuous.

Never assume a cancelled CREATE INDEX CONCURRENTLY cleaned up. The runbook step for cancelling one must end with “then check pg_index and drop or rebuild what is left”.

Read \d output after DDL. The INVALID suffix was there for six weeks.

Use DROP INDEX CONCURRENTLY on a live table.

Watch write latency after every index change, in both directions. An index makes reads faster and writes slower, always. That trade is the reason to add one, and it is a cost to measure rather than assume away.

Audit unused indexes periodically, with stats_reset in hand. An unused index costs the same on write as an invalid one, and is far more common.

Prefer REINDEX INDEX CONCURRENTLY to drop-and-recreate. It keeps the definition, the name and the dependencies, and never leaves a window with no index at all.