Database schema migrations without downtime

Most outages blamed on "the migration" have one of two causes. Either the new schema broke the version of the application that was still running, or a single statement took a lock that queued every query behind it. Both are avoidable with a few habits that cost little once they are routine.

The underlying fact is simple: during any rolling deploy, two versions of the application run against one database. Every schema change has to work with both.

Expand and contract

Expand and contract (sometimes called parallel change) splits a breaking change into steps that are each compatible with the code running before and after them.

Take renaming orders.customer_ref to orders.customer_id. Done in one migration, the old pods fail the moment the column disappears. Done in steps:

1. Expand. Add customer_id as a nullable column. Nothing reads it yet.
2. Dual write. Deploy code that writes both columns and still reads the old one.
3. Backfill. Copy existing values into the new column in batches.
4. Switch reads. Deploy code that reads customer_id and stops writing customer_ref.
5. Contract. Once no running version touches the old column, drop it.

Each step is a separate deploy. Any of them can be rolled back without data loss, because the previous version of the code still finds everything it expects. The cost is calendar time, which is cheap compared with an incident.

The same shape covers changing a column type, splitting a table, or moving data to a new table. Only the backfill logic differs.

Know which statements lock

PostgreSQL takes an ACCESS EXCLUSIVE lock for most ALTER TABLE forms. That lock conflicts with everything, including plain SELECT. Usually it is held for milliseconds and nobody notices. The danger is waiting for it: if a long transaction holds any lock on the table, the ALTER queues behind it, and every new query queues behind the ALTER. A metadata change that should take a millisecond becomes a full outage for as long as the slow transaction runs.

The common operations, and what to do instead:

VALIDATE CONSTRAINT takes a SHARE UPDATE EXCLUSIVE lock, which allows reads and writes to continue while the table is scanned. That is the reason for splitting the constraint into two statements.

Always set a lock timeout

The single most useful line in any migration is a lock timeout. If the lock is not available quickly, the statement fails instead of building a queue:

SET lock_timeout = '3s';
SET statement_timeout = '15s';
 
ALTER TABLE orders ADD COLUMN customer_id bigint;
 
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

A failed migration is easy to retry a minute later. A migration that blocked checkout for four minutes is an incident review. Migration tools should retry on lock timeout with a short backoff rather than give up after one attempt.

The validation and index builds go in a separate migration, without the short statement timeout, because they are meant to run for a while:

SET lock_timeout = '3s';
 
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;

CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx
  ON orders (customer_id);

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Most migration tools wrap each file in a transaction by default, so check how yours disables that per migration (Flyway, Rails, Django and golang-migrate all have a way). If a concurrent build fails, it leaves an INVALID index behind. Drop it and try again.

Backfill in batches

A single UPDATE orders SET customer_id = customer_ref::bigint touches every row in one transaction. It holds row locks on the whole table, produces a burst of WAL that replicas have to replay, and leaves millions of dead tuples for autovacuum. On a large table it can also run for an hour with no way to stop it cleanly.

Batch it by primary key instead, committing each batch:

UPDATE orders
SET customer_id = customer_ref::bigint
WHERE id IN (
  SELECT id FROM orders
  WHERE id > $1 AND customer_id IS NULL
  ORDER BY id
  LIMIT 5000
)
RETURNING id;

The job runs this in a loop, passing the highest returned id as the next starting point, until nothing comes back. A few practical rules:

• Keep batches small enough to finish in well under a second. Tune the batch size against real latency, not a guess.
• Pause between batches and watch replication lag. Slow down or stop when it grows.
• Make the job resumable. Store the last processed key so a restart picks up where it stopped.
• Run it as application code or a job, not inside the schema migration. Migrations should be fast and the backfill is not.

Because the application is already dual writing, new rows arrive with both columns set. The backfill only has to catch up on history.

Deploy order

The rules for ordering are short:

• Additive schema changes go before the code that uses them. Run migrations as a step before the rollout, never at application startup from every pod at once.
• Destructive schema changes go after the code that stopped using them, and after the previous release can no longer be rolled back to.
• Never combine a schema change and the code that depends on it in one deploy. If the code rolls back, the schema should still suit it.

ORMs add one trap. Some cache the column list and issue SELECT * or full-column INSERT statements, so dropping a column that the code no longer references by name still breaks old pods. Rails has ignored_columns for this; other ORMs need the field removed from the model one release before the drop.

Where to start

1. Add lock_timeout to every migration, enforced by the migration runner or a template.
2. Add a linter to CI. Squawk flags unsafe PostgreSQL DDL in plain SQL files; Rails teams can use strong_migrations.
3. Move migrations out of application startup and into a single pipeline step that runs before rollout.
4. Write down the expand and contract steps for the next breaking change before writing any of the migrations.
5. Build one reusable batched backfill job, with resumable progress and a replication lag check, and use it every time.