Migration runner cannot express CREATE INDEX CONCURRENTLY — every index migration blocks writes fleet-wide

[rcbevans]

Problem

apply_pending wraps every migration in a single transaction (src/taskq/migrate.py:189):

async with conn.transaction():
    await conn.execute(migration.render(schema))

Postgres forbids CREATE INDEX CONCURRENTLY (and DROP INDEX CONCURRENTLY, and several ALTER TYPE/VACUUM forms) inside a transaction block. The runner therefore cannot express a non-blocking index build at all — not as an option a migration author can opt into, but structurally.

Every index migration is consequently a blocking build that holds ACCESS EXCLUSIVE on the target table for the full duration of a full-table scan, locking out INSERT/UPDATE/DELETE fleet-wide while it runs.

Why it matters

This was harmless while migrations only touched small, low-churn tables — 01.00.01_01_pre_per_property_cron.sql adds a unique constraint to cron_schedules, which is tiny.

It stops being harmless the moment a migration touches jobs or job_events, which are written on essentially every enqueue, dequeue, lifecycle transition, and progress report. On a deployment with real history, such a migration stalls the entire worker fleet for however long the index build takes — minutes on a large table. There is no way for the migration author to avoid this, and nothing in the framework signals the hazard.

Two in-flight changes hit this independently, which is what makes it structural rather than incidental:

• a unique-index drop/recreate on jobs
• a new index on job_events

Neither author did anything wrong; the runner simply offers no safe path. A migration framework that makes the dangerous option the only option will keep producing dangerous migrations.

Proposed fix

Let a migration declare that it must run outside the transaction wrapper, so CONCURRENTLY becomes available.

Sketch:

• A per-migration marker — a filename phase token, a header comment directive (e.g. -- taskq:no-transaction), or a sidecar metadata field — that discover() parses and apply_pending honours by executing that migration without opening a transaction.
• Because a non-transactional migration cannot be rolled back atomically, such migrations should be required to be idempotent and re-runnable (CREATE INDEX CONCURRENTLY IF NOT EXISTS, etc.), and the ledger update needs care: record completion only after the statement succeeds, and tolerate a partially-built INVALID index from an interrupted run (the standard remedy is to drop and rebuild).
• Consider surfacing the distinction in the migration ledger so operators can see which migrations are safe to run online.

Prior art worth following: Rails' disable_ddl_transaction!, Django's AddIndexConcurrently / atomic = False, Alembic's autocommit-block pattern, and the strong_migrations gem's rules for flagging exactly this class of hazard.

Interim

Until this lands, any migration touching a hot table should carry an explicit ops note stating what it locks, why CONCURRENTLY is unavailable, and that large deployments should apply it in a maintenance window. That is a mitigation, not a fix — it depends on every future author knowing to write the note.

Notes

• Filed as a framework-level gap rather than against any individual migration.
• Related, lower priority: job_attempts → job_attempts_archive in the archive sweep still copies via SELECT ja.*, relying on identical physical column order between the two tables. The jobs side of the same CTE has been made explicit; the job_attempts side is the same latent hazard and should get the same treatment.