Four invalid indexes on an orders table, two of them still maintained on every write, listed in Pilotbase

CREATE INDEX CONCURRENTLY is how you add an index to a table that's in use: it doesn't block writes while it builds. The price is that it can't run inside a transaction, so when it fails there is nothing to roll back. The half-made index stays in the catalog, marked invalid. Queries ignore it. pg_dump skips it, so it isn't in your schema file or your staging database. The migration tool reports an error once and moves on. Months later it's still there.

We came across this while comparing schema dumps: a production table had an index that no dump showed. So we set out to make invalid indexes on purpose, on PostgreSQL 18.6, on an orders table with 2 million rows, and to see what each one costs. There turned out to be two kinds, and the difference matters.

Four ways to get one

1. A unique index over data that isn't unique. The usual reason to add a unique index is that duplicates have been getting in, which means there is probably one already.

text
inv=# CREATE UNIQUE INDEX CONCURRENTLY orders_email_key ON orders (email);
ERROR:  could not create unique index "orders_email_key"
DETAIL:  Key (email)=(user1000@example.com) is duplicated.
Time: 3112.518 ms

2. A timeout or a cancel during the build. A statement_timeout set by the migration tool, a deploy that gets killed, somebody pressing Ctrl-C because it was taking too long.

text
inv=# SET statement_timeout = '400ms';
inv=# CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status, created_at);
ERROR:  canceling statement due to statement timeout

3. A timeout while it waits for another transaction. A concurrent build has to wait, twice, for every transaction that might still see the table the old way. If a session touched the table while the index was building and then sat open (an idle-in-transaction connection, a long report), the build finishes its work and waits for that session. Our timeout fired during the wait:

text
-- session A, started while the build below was running, then left open
BEGIN;
INSERT INTO orders (customer_id, email, status, total_cents) VALUES (1, 'late@example.com', 'new', 100);

-- session B
inv=# SET statement_timeout = '6s';
inv=# CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id, created_at);
ERROR:  canceling statement due to statement timeout
Time: 6000.300 ms

4. REINDEX CONCURRENTLY stopped the same way. A concurrent reindex builds a new copy next to the old index and swaps them at the end. Interrupted, the copy stays, with _ccnew added to the name. The original is untouched and still valid.

A lock_timeout does the same as case 3. It's the setting people add to migrations to make them safe, and it applies to these waits too: with lock_timeout = '2s' and one open transaction, our build ended with "canceling statement due to lock timeout" after 3.4 seconds and left its index behind. The documentation also lists a deadlock as a cause. After the first four, \d orders in psql looked like this:

text
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_customer_idx" btree (customer_id, created_at) INVALID
    "orders_email_key" UNIQUE, btree (email) INVALID
    "orders_status_idx" btree (status, created_at) INVALID
    "orders_total_idx" btree (total_cents)
    "orders_total_idx_ccnew" btree (total_cents) INVALID

Two kinds: never ready, and ready but not valid

pg_index keeps three flags per index. indisvalid says queries may use it. indisready says inserts and updates must maintain it. indislive is false only while an index is being dropped. A concurrent build sets them in steps: the index is created with both off, then built, then switched to ready so new writes go into it, then checked against the table, and only then marked valid. Where the build died decides what's left:

text
         index          | valid | ready |  size
------------------------+-------+-------+---------
 orders_email_key       | f     | f     | 0 bytes    <- 1. duplicate found during the build
 orders_status_idx      | f     | f     | 0 bytes    <- 2. cancelled during the build
 orders_customer_idx    | f     | t     | 60 MB      <- 3. cancelled while waiting, after the build
 orders_total_idx_ccnew | f     | t     | 13 MB      <- 4. reindex cancelled while waiting
 orders_pkey            | t     | t     | 43 MB
 orders_total_idx       | t     | t     | 13 MB

The first two died before the index was switched to ready. Nothing writes to them. They usually hold no disk space either, though not always: when we repeated the unique build later, the duplicate was found at a later point and the failed index was left at 133 MB, still not ready.

The last two were complete and already receiving writes when they were cancelled. They are full-size indexes that every insert and update keeps up to date, and that no query is allowed to read.

What they cost

The PostgreSQL manual says an invalid index "will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead". We measured that with batches of 200,000 inserted rows, three runs in each state, dropping the leftovers in two steps:

text
200,000 rows inserted                              time           WAL written
all four invalid indexes present                   1.6-2.3 s      82-197 MB
the two ready ones dropped                         0.71-0.72 s    52 MB
the two never-ready ones dropped as well           0.70-0.80 s    52 MB

The two ready-but-invalid indexes more than doubled the time of every batch and added between 30 and 145 MB of write-ahead log to each one, all of which goes to replicas and into backups. The invalid index on customer_id grew from 60 to 120 MB over those three batches, like any index receiving random keys. Dropping the two that never became ready changed nothing we could measure. So the manual's sentence is true for one kind and not the other, and indisready tells you which you have.

The planner, meanwhile, behaves as if none of them exist. With a complete 60 MB index on (customer_id, created_at) sitting there invalid, a lookup by customer still read the whole table:

text
EXPLAIN SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 10;
 Limit
   ->  Sort
         ->  Gather
               ->  Parallel Seq Scan on orders
                     Filter: (customer_id = 4242)

That is how these get noticed, when they get noticed at all: somebody added an index for a slow query, the query is still slow, and the index is right there in the table's list. If the plan in explainViewer shows a Seq Scan where you expected your new index, look for the word INVALID before you look at anything else.

The rerun that reports success

The name is taken, so running the same statement again fails. Most migration files guard against exactly that error with IF NOT EXISTS, and that makes it worse:

text
inv=# CREATE UNIQUE INDEX CONCURRENTLY orders_email_key ON orders (email);
ERROR:  relation "orders_email_key" already exists

inv=# CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS orders_email_key ON orders (email);
NOTICE:  relation "orders_email_key" already exists, skipping
CREATE INDEX

The second form returns success. The migration is recorded as applied, the deploy goes green, and the index is as invalid as before. IF NOT EXISTS checks the name, not the state. Of 180 migration files we read from two open-source projects, 39 create their index this way; it's the standard pattern, and it turns one failed build into a permanent one.

An invalid unique index can still reject rows

We removed the duplicate, then left a unique index on email in the ready-but-invalid state (cancelled while waiting, as in case 3). It enforces uniqueness on new rows, and nothing else:

text
inv=# INSERT INTO orders (customer_id, email, status, total_cents) VALUES (1, 'user77@example.com', 'new', 100);
ERROR:  duplicate key value violates unique constraint "orders_email_key"

inv=# INSERT INTO orders (...) VALUES (1, 'user77@example.com', 'new', 100) ON CONFLICT (email) DO NOTHING;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Inserts start failing with a constraint the application was never told about, and the upsert that was written to go with the new index fails too, because for ON CONFLICT the index doesn't count. A lookup by email still scans the table. The manual warns about the first part: uniqueness "is already being enforced against other transactions when the second table scan begins", and stays enforced if the build fails after that. A never-ready unique index (case 1) does none of this; we inserted a duplicate straight through one.

Find them

sql
SELECT n.nspname AS schema, t.relname AS "table", c.relname AS index,
       CASE WHEN i.indisready THEN 'yes' ELSE 'no' END AS maintained_on_write,
       pg_size_pretty(pg_relation_size(c.oid)) AS size,
       pg_get_indexdef(c.oid) AS definition
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
  AND c.relkind = 'i'            -- 'I' is a partitioned index: invalid until every partition has its piece
ORDER BY pg_relation_size(c.oid) DESC;
Pilotbase query result listing four invalid indexes on orders: orders_email_key 133 MB not maintained, orders_customer_idx 97 MB maintained, orders_status_idx_ccnew 25 MB maintained, orders_created_idx 0 bytes not maintained
The finder query in Pilotbase. The two rows that say yes are the ones slowing down writes.

Run it in every database of the cluster, and on the primary: invalid indexes exist on replicas too, but that's where you fix them. The relkind filter matters if you use partitioning. CREATE INDEX ... ON ONLY parent makes a parent index that is invalid on purpose until an index on each partition has been attached to it; that's the documented way to index a partitioned table without a long lock, and it isn't a leftover. pg_dump knows the difference too: it wrote out our ON ONLY index and none of the four broken ones.

Keep the definition column. Since no dump contains an invalid index, that query result is the only copy of what somebody meant to build.

Clear them

A _ccnew index: drop it. It's a spare copy; the original is still doing the work. Then run the REINDEX again if you still want it. (_ccold is the opposite case, an old index that a finished reindex couldn't remove. Drop that too.)

sql
DROP INDEX CONCURRENTLY orders_status_idx_ccnew;   -- 8 ms here

Anything else: drop it and create it again, or rebuild it in place. The manual recommends the first. REINDEX INDEX CONCURRENTLY on the invalid index does the same in one statement and keeps the name:

text
inv=# REINDEX INDEX CONCURRENTLY orders_customer_idx;
REINDEX
Time: 2393.124 ms          -- valid now; the lookup by customer uses it

Three things we ran into while clearing ours:

Fix the cause first, or you get two. Rebuilding the unique index while the duplicate was still in the table failed again, and left a second invalid index beside the first: orders_email_key and now orders_email_key_ccnew. Find the duplicates (GROUP BY email HAVING count(*) > 1), deal with them, then rebuild. For a timeout, set statement_timeout and lock_timeout to 0 for that session and look in pg_stat_activity for old open transactions before you start.

Only REINDEX INDEX rebuilds an invalid index. The table-wide form skips them and says so:

text
inv=# REINDEX TABLE CONCURRENTLY orders;
WARNING:  skipping reindex of invalid index "public.orders_customer_idx"
HINT:  Use DROP INDEX or REINDEX INDEX.

Drop with CONCURRENTLY, outside a transaction. A plain DROP INDEX needs an ACCESS EXCLUSIVE lock on the table. Against a 4 second report ours gave up at its 2 second lock_timeout, and without a timeout it would have held up every other query on the table while it waited. DROP INDEX CONCURRENTLY waited out the same report (3.6 seconds) without getting in anyone's way. It can't run inside BEGIN: "DROP INDEX CONCURRENTLY cannot run inside a transaction block". Dropping an index safely has the longer version.

Check before and after a migration

One query, zero rows expected. Run it in CI against the database after migrations have been applied, and as the last step of a deploy:

sql
SELECT count(*) AS invalid_indexes
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid AND c.relkind = 'i';

And in the migration itself, replace the guard that hides the failure with one that clears it:

sql
-- no transaction around this file
SET statement_timeout = 0;   -- a concurrent build may wait a long time for other transactions, and that's fine:
SET lock_timeout = 0;        -- it blocks nobody while it waits, and either timeout would leave an invalid index
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_idx;  -- a no-op the first time, clears a failed attempt on a retry
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id, created_at);

This drops a healthy index too if the file is ever run twice, which is why it belongs in a migration that runs once, not in a script that runs on every deploy. For that, check indisvalid for the name and drop only when it's false.

The file can be checked before it runs as well. Paste a migration into sqlInspector and it flags CREATE INDEX CONCURRENTLY IF NOT EXISTS for this exact reason, along with a concurrent build or drop placed inside BEGIN ... COMMIT (which Postgres refuses) and the index and constraint changes that lock the table for the whole build. It reads the text in your browser; nothing is uploaded and nothing is run.

sqlInspector with a twelve-line migration: findings for CREATE INDEX without CONCURRENTLY, a foreign key without NOT VALID, SET NOT NULL, and IF NOT EXISTS that can skip a broken index, each with the safer SQL
A migration in sqlInspector. Line 8 is the IF NOT EXISTS pattern from this article.

In short

A failed concurrent build or reindex leaves an index that queries can't use. If indisready is false it costs a name and sometimes disk; if it's true, every write pays for it, and a unique one rejects rows. No dump or schema diff will show either kind, and IF NOT EXISTS will step over both. Look in pg_index for NOT indisvalid, drop the _ccnew copies, rebuild the rest with REINDEX INDEX CONCURRENTLY once the cause is gone, and make zero invalid indexes a check your deploy runs.

Run the finder query from this article against each of your databases in Pilotbase, primary and replicas side by side. Desktop for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Sources: PostgreSQL 18 documentation, CREATE INDEX (Building Indexes Concurrently: what a failed build leaves, the recommended recovery, uniqueness enforced before the index is valid), REINDEX (_ccnew and _ccold, only REINDEX INDEX rebuilds an invalid index), pg_index (indisvalid, indisready, indislive), pg_dump. The migration files counted are the 90 newest PostgreSQL migrations each from the public Mattermost and Sourcegraph repositories on GitHub. Every other number, message and screenshot is from our own runs: PostgreSQL 18.6 in Docker on a 12th-gen Core i5 laptop, default settings, an orders table of 2 million rows; queries run in Pilotbase and psql.