Skip to content

migrations: Support sane concurrent index creation

Administrator requested to merge ef/migrator-concurrent-index into main

Created by: efritz

Pulled from #29831. This PR supports CREATE INDEX CONCURRENTLY ... operations without risk of a live-lock/deadlock condition in the presence of concurrently running migrators. This PR also adds code to attempt to repair invalid indexes (which should reduce the number of easily-solvable "dirty database" conditions).

Previously, the migrator will take an advisory lock (if it's confident it needs to run queries) and run all definitions in one shot. Now, the migrator will take an advisory lock and run all definitions that don't require concurrent index creation. Once a concurrent index creation query is encountered, the lock is dropped and the migrator instances will coordinate on the progress of the creation operation (by querying Postgres catalog tables).

There are now two ways that migrators can infer progress from another:

  1. If there are pending migrations, a migrator holding a lock is (likely) working on them
  2. If there are pending migrations but no migrator holding a lock, there may be an active index creation query
  3. If there are pending migrations but neither of those conditions hold, the migration has failed (migrator died)

Merge request reports