Build a unique index on data with a duplicate#
A users table should get a unique index on email. One address already appears twice. We ran this on PostgreSQL 16.15.
CREATE TABLE users (id integer PRIMARY KEY, email text NOT NULL);
INSERT INTO users VALUES
(1, 'ana@example.com'),
(2, 'ben@example.com'),
(3, 'ana@example.com');
Migration tools often wrap each file in a transaction. CONCURRENTLY refuses to run there:
BEGIN;
CREATE INDEX CONCURRENTLY users_email_key ON users (email);
-- ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
ROLLBACK;
That failure leaves nothing behind. Most tools have a per-migration switch that turns the transaction off. With it off, the build runs and meets the duplicate:
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);
ERROR: could not create unique index "users_email_key"
DETAIL: Key (email)=(ana@example.com) is duplicated.
The failed build leaves an index behind#
A concurrent build runs in several transactions. The first commits the index's catalog entry before any scan starts, so that later transactions can wait for other sessions to see it. When a later step fails, nothing rolls that entry back:
SELECT indexrelid::regclass AS index, indisvalid, indisready
FROM pg_index WHERE indrelid = 'users'::regclass;
| index | indisvalid | indisready |
|---|---|---|
| users_pkey | t | t |
| users_email_key | f | f |
The CREATE INDEX documentation says such an index "will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead." In our run the build failed before the index was marked ready, so indisready is also false. A failure later in the build, for example a duplicate inserted while the index is being validated, leaves an index that every write maintains and no query uses.
The retry that hides the failure#
A common way to make a migration rerunnable is IF NOT EXISTS:
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS users_email_key ON users (email);
NOTICE: relation "users_email_key" already exists, skipping
CREATE INDEX
The command succeeds, the migration tool records the migration as applied, and the index is still invalid. The documentation warns about this case: with IF NOT EXISTS, the existing index may be nothing like the one the statement would have created. The table accepts another duplicate, and a query on email still scans the whole table, even with sequential scans disabled:
INSERT INTO users VALUES (4, 'ana@example.com');
-- INSERT 0 1
SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM users WHERE email = 'ben@example.com';
-- Seq Scan on users
-- Filter: (email = 'ben@example.com'::text)
Recover and retry#
Fix the data, drop the invalid index without blocking writes, and build it again:
DELETE FROM users WHERE id IN (3, 4);
DROP INDEX CONCURRENTLY users_email_key;
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);
indisvalid is now t. Deleting the duplicates is only right for this example. A real table needs a decision about which row survives, which finding duplicates before a unique constraint covers.
What to rehearse#
Run the migration once against a copy with the production data, and once more after forcing a failure, to see what your migration tool records. After every concurrent index migration, in the rehearsal and in production, look for invalid indexes:
SELECT c.relname AS invalid_index, 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;
Make the migration fail when this returns a row, rather than relying on IF NOT EXISTS. A concurrent build also waits for transactions that started before it, so a long transaction can hold it for a long time; it does not block the application's reads and writes while it waits.
Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.