Our index build timed out, the retry found it by name, and the release went out without it

Our parcel API was getting a lookup by tracking number for a courier partner's new portal. The release had two parts, a migration that added an index on parcels.tracking_number and the code for the new endpoint, and our deploy job runs migrations with Alembic before it rolls out the code. The parcels table has about two hundred and ten million rows, so the migration built the index concurrently, which lets writes carry on during the build. CREATE INDEX CONCURRENTLY cannot run inside a transaction, so it sat in Alembic's autocommit block:

with op.get_context().autocommit_block():
op.execute("CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_parcels_tracking_number ON parcels (tracking_number)")

Our migration role has a statement timeout of ten minutes, set after an earlier migration held locks for far longer than anybody intended. On that table the build needed about forty. At ten minutes Postgres cancelled it, the migration failed, and the deploy job went red. The engineer releasing it took the timeout for a slow moment and ran the job again. The second run took under a second and went green, and the new code rolled out.

Within a quarter of an hour the database's CPU was above ninety percent, every tracking lookup was a sequential scan of the whole table, and the partner's portal was timing out. We rolled the code back after twenty five minutes.

psql listed the index on the table, with one extra word after it: INVALID. When a concurrent build fails partway, Postgres leaves the index behind and marks it invalid. The planner never uses it, but every insert and update still has to maintain it, and the documented recovery is to drop it and build it again. IF NOT EXISTS checks only whether a relation with that name exists, and the documentation warns that there is no guarantee the existing index is anything like the one that would have been created. So the retry printed a notice that the relation already existed, skipped the statement, and Alembic recorded the migration as applied.

We dropped the index with DROP INDEX CONCURRENTLY and built it again that night, in a job of its own, from a session with the statement timeout set to zero. Index builds on large tables now run that way, hours before the release that needs them, and code that depends on a new index is switched on by a flag only once the index exists and is valid. After every migration the deploy job runs

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

and fails if it returns anything. Our migration lint rejects IF NOT EXISTS on CREATE INDEX, because a retry is exactly when an index with that name is most likely to be the broken one.

IF NOT EXISTS asked Postgres whether the name was taken. It was, by an index Postgres would never use.

– Sergey Shinder