## TL;DR
Most deploy-time migration pain comes from one DDL statement grabbing an exclusive lock on a hot table while the deploy waits. Avoid it with expand-then-contract: add the new column or table first, ship code that tolerates both shapes, backfill in batches outside the deploy, then drop the old shape in a later release. The deploy itself should never hold a long lock.

```text
database migration locking tables during deploy: how to avoid
```

## Steps

1. Audit the migration for lock-hungry statements: table rewrites, adding columns with defaults on some engines, non-concurrent index builds.
   Expected: you have a list of the statements that will take strong locks and roughly how long each holds them.

2. Rewrite the migration as expand steps only: add nullable columns, new tables, and concurrent indexes; never rename or drop in the same release.
   Expected: the migration completes in seconds with only brief locks, even on large tables.

3. Build indexes concurrently (where the database supports it) and set a statement timeout so a stuck DDL fails loudly instead of blocking the deploy.
   Expected: index builds do not block writes; a timeout surfaces problems instead of hanging the pipeline.

4. Run heavy data backfills as batched background jobs outside the deploy window, not inside the migration.
   Expected: backfill progresses without holding locks; the deploy does not wait on data volume.

5. Deploy application code that reads and writes both the old and new shapes.
   Expected: old and new code versions coexist safely during the rollout.

6. In a later release, once all code uses the new shape, run the contract migration that drops the old columns.
   Expected: cleanup is a small, fast migration because nothing references the old shape anymore.

## Use this when
- deploys stall or time out on a migration step
- an ALTER TABLE blocks reads or writes on a production table
- you need zero-downtime schema changes on Postgres or MySQL

## Not for this skill when
- this is the initial schema on an empty database - locks do not matter with no traffic
- the store is schemaless (most NoSQL) - there is no DDL lock problem to solve
- the migration only touches read-only analytics tables - schedule it freely

## Variant phrasings
- zero downtime database migrations
- postgres migration lock timeout
- online schema change without downtime
- alter table blocking writes production

## Why it happens
Many DDL statements take an ACCESS EXCLUSIVE lock, which conflicts with everything including reads. On a big table the lock is held while the table is rewritten or scanned, so one migration statement can freeze a hot table for minutes while the deploy - and your users - wait.

## Edge cases
- Adding a NOT NULL column without a default rewrites the table on some engines; add nullable, backfill, then set NOT NULL
- A failed concurrent index build leaves an invalid index behind - check index validity and rebuild rather than assuming success
- Expand-then-contract spans releases, so it needs discipline in the deploy tracker or the contract step gets forgotten
- Long-running transactions elsewhere can block even a "fast" DDL - check for idle-in-transaction sessions before blaming the migration

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_AGejpI01BBW5QovFt7BVMQ