Database Migrations Without Downtime

Even a statement that needs its lock for only a millisecond can cause an outage. RunxBuild describes the mechanism: a long-running query holds a lock on the table, your ALTER requests ACCESS EXCLUSIVE and waits, and every subsequent query, including trivial reads, queues behind your ALTER because lock requests are ordered. The table is effectively unavailable until the slow query finishes.

The fix is to fail fast instead of waiting.

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE users ADD COLUMN last_login timestamptz;
COMMIT;

If the lock cannot be acquired within the timeout, the statement errors and rolls back, and traffic continues. Your migration tool then retries after a pause until it catches a quiet moment. Safe-pg-migrations applies this automatically, setting a short lock timeout and a short statement timeout on its DDL, and its README describes the lock timeout as acting like a seatbelt. The project also avoids wrapping migrations in one large transaction, because locks are released sooner and failed statements can be retried individually.

Two related habits help.

• Combine related changes into one statement. RunxBuild notes that three separate ALTER TABLE statements acquire the lock three times, and each acquisition is another chance to queue behind a slow query.
• Watch for long-running transactions before you migrate. A forgotten analytics query or an idle transaction holding locks is the usual reason a migration cannot get its lock.

To handle mixed versions, split every breaking change into steps that are each backward compatible. Prisma's guide describes the expand and contract pattern as a way to move from an old data structure to a new one without affecting uptime, by introducing the new structure in the background, preparing the data, and switching over in discrete steps. The guide also notes that you can roll back easily at most points in the process.

Prisma's sequence, applied to splitting a name column into first_name and last_name, looks like this.

1. Expand the schema. Add first_name and last_name as nullable columns. Do not touch name. Prisma stresses that new columns must not carry constraints that would make current client behavior fail.
2. Write to both. Deploy code that writes name and the two new columns on every insert and update, while still reading from name.
3. Migrate existing data. Backfill the new columns from name. Prisma warns this step needs care, since splitting "John Smith" is easy and splitting a four-part name is not.
4. Test the new structure against production-like queries while name remains the source of truth.
5. Switch reads to the new columns.
6. Stop writing the old column.
7. Contract. Drop name in a later release, once no running code references it.

Each step is a separate deploy. That feels slow, but each one is small, safe and reversible. The same property makes application rollbacks safe, because the previous release still works against the expanded schema.

These recipes cover most day-to-day migrations in Postgres. The facts about version behavior come from the RunxBuild guide as read on 2026-10-17.

Adding a column. RunxBuild explains that adding a nullable column, or one with a constant default, only updates the catalog in modern Postgres, so the lock is held for milliseconds regardless of table size. The guide notes that since PostgreSQL 11 a constant default is stored as metadata, while a volatile default such as now() or gen_random_uuid() still forces a full table rewrite.

Adding NOT NULL. Atlas's guide explains the trap: a plain SET NOT NULL acquires an ACCESS EXCLUSIVE lock, which blocks reads as well as writes, and holds it while it scans every row. The safer path adds a check constraint marked NOT VALID, which is instant, then runs VALIDATE CONSTRAINT, which scans under a weaker lock that does not block reads or writes.

ALTER TABLE users
  ADD CONSTRAINT users_tenant_id_not_null
  CHECK (tenant_id IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_tenant_id_not_null;

Adding an index. The Postgres documentation says that with CONCURRENTLY, Postgres builds the index without taking any locks that prevent concurrent inserts, updates, or deletes. The cost is two table scans and a longer build. Two details matter. The statement cannot run inside a transaction block, so your migration tool must run it on its own. And if the build fails, it leaves an index marked INVALID that still adds write overhead, so the documented recovery is to drop it and run the concurrent build again.

Adding a foreign key. Safe-pg-migrations adds foreign keys without validation and validates them separately, which avoids taking a lock on the entire tables for the length of the check.

Many migrations need to fill a new column for existing rows. The tempting approach is one large UPDATE. RunxBuild explains why that hurts: a single update across millions of rows holds one enormous transaction, bloats the table with dead tuples, and can block vacuum for the duration.

Backfill in small batches with a commit after each one.

UPDATE users
SET tenant_id = derive_tenant(id)
WHERE id IN (
  SELECT id FROM users
  WHERE tenant_id IS NULL
  ORDER BY id
  LIMIT 5000
);

Run it in a loop from a script or background job until no rows remain. Good backfills share a few traits.

• They are idempotent. The WHERE tenant_id IS NULL filter means a restart picks up where it stopped.
• They are throttled. A short sleep between batches leaves room for production traffic and replication.
• They run after dual writes are live. Otherwise new rows created during the backfill are left empty.
• They are separate from schema changes. A schema migration should finish in seconds, and a backfill may take hours.

Some changes cannot be made cheaply in place, especially in MySQL on very large tables. Online schema change tools solve this by rebuilding the table in the background.

Percona's documentation describes how pt-online-schema-change works: it creates an empty copy of the table to alter, modifies the copy, copies rows over from the original, and then swaps the new table in. Triggers on the original table keep the copy up to date while rows are copied, and the original table is not locked, so clients can keep reading and writing.

Vitess documents a similar managed approach, with online DDL strategies and revertible migrations that the database layer schedules and controls.

These tools are powerful but add moving parts, such as triggers, extra disk space and a final swap. For most Postgres changes, the recipes above are simpler. Reach for a copy-and-swap tool when a change truly requires rewriting a large table.

Review every migration against this list before it ships.

1. Does each statement set a lock timeout, and does the tool retry on timeout?
2. Will any statement scan or rewrite a large table while holding a strong lock?
3. Are indexes created concurrently, outside a transaction?
4. Are new constraints added as NOT VALID and validated separately?
5. Can the currently deployed code run against the new schema?
6. Can the previous release run against it too, in case of rollback?
7. Is any data change done as a batched, idempotent backfill rather than in the migration?
8. Is the destructive part, such as dropping a column, deferred to a later release?
9. Has the migration run against a copy of production data with realistic size?

Coding agents write a growing share of migrations, and they reproduce whatever patterns they have seen. Quanta Intelligence skills can encode a checklist like this one, so an agent proposes the concurrent index and the NOT VALID constraint by default.