Zero Downtime Database Migrations for Early-Stage Products

Zero downtime database migrations come down to two rules. First, never hold a heavy lock on a busy table for more than a moment. Second, keep the schema compatible with both the old and the new version of your application for as long as both can be running. Follow those two rules and you never need a maintenance page for a schema change.

The examples here use PostgreSQL, the default for most products we build, but the ideas apply to any relational database. MySQL teams face the same problems, and tools such as gh-ost and pt-online-schema-change exist for exactly this reason.

Most migration outages are not caused by slow SQL. They are caused by locks and by timing.

Many ALTER TABLE commands need an ACCESS EXCLUSIVE lock, which blocks every read and write on the table. On its own, that lock might last milliseconds. But if a long-running query or an idle open transaction already holds a lock on the table, your migration waits, and every new query on that table queues up behind your migration. A change that should take a moment can stall the whole application for as long as that first query runs.

Timing is the other half. During a rolling deploy, old and new versions of your code serve traffic side by side for a few minutes. If the migration renames a column, every request still handled by the old version fails until the rollout finishes. And if you then need to roll the application back, the old code meets a schema it no longer understands.

Expand and contract, sometimes called parallel change, turns one risky change into several safe ones. Take a common case: renaming users.name to users.full_name. Instead of a single rename, ship it as a sequence of separate deploys:

1. Expand. Add the new column with ALTER TABLE users ADD COLUMN full_name text;. A nullable column with no default is a metadata-only change and completes almost instantly.
2. Write to both. Deploy application code that writes every change to both name and full_name, while still reading from name.
3. Backfill. Copy existing values into full_name in small batches, as described below.
4. Switch reads. Deploy code that reads from full_name. Put the switch behind a feature flag if you can, so switching back is instant.
5. Stop writing the old column. Once reads have been stable for a while, remove the code that writes to name.
6. Contract. In a later release, drop the old column with ALTER TABLE users DROP COLUMN name;.

It is more steps than a rename, but every step is small, independently deployable and safe to pause. That is the core of zero downtime database migrations: at every point, both the current and the previous version of the application work against the schema.

A backfill that updates millions of rows in one statement holds locks on all of them, bloats the table and can push replicas far behind the primary. Break it into batches instead:

• Walk the table by primary key range, for example UPDATE users SET full_name = name WHERE id BETWEEN $1 AND $2 AND full_name IS NULL; with a range of a few thousand rows at a time.
• Commit each batch separately and pause briefly between batches, so normal traffic keeps flowing.
• Make it idempotent. The full_name IS NULL condition means a rerun skips rows that are already done.
• Make it resumable by recording the last processed ID, so a failure halfway through costs minutes instead of a restart.
• Run it as a background job, not inside the migration file, so a slow backfill never blocks a deploy.
• Watch replication lag, lock waits and error rates while it runs, and stop if they climb.

Only when the backfill is complete and verified, with the count of rows still missing a value at zero, should reads move to the new column.

A handful of PostgreSQL operations cause most migration incidents. Each one has a safer form:

• Every migration. Start with SET lock_timeout = '3s'; so a migration that cannot get its lock fails fast instead of queuing traffic behind it. Then retry it later.
• Indexes. Use CREATE INDEX CONCURRENTLY, which does not block writes. It cannot run inside a transaction, so tell your migration tool not to wrap that file in one, and if it fails, drop the invalid index it leaves behind before retrying.
• Foreign keys. Add them with NOT VALID, then run ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; as a separate step. Validation checks existing rows without blocking normal reads and writes.
• NOT NULL. On PostgreSQL 12 and later, add CHECK (full_name IS NOT NULL) NOT VALID, validate it, then run SET NOT NULL, which can skip its own full-table scan because the valid check already proves there are no nulls. Drop the check afterward.
• Column type changes. Most type changes, such as integer to bigint, rewrite the whole table under an exclusive lock. Treat them as expand and contract with a new column.
• Defaults. Since PostgreSQL 11, adding a column with a constant default is fast. A volatile default, such as a random value, still rewrites the table.

Migration linters such as Squawk can catch many of these problems in code review, before they ever reach production.

A down migration that drops a column you just added is easy to write and rarely needed. The hard rollbacks come after destructive steps, and no script can bring back data you have dropped. So plan rollbacks around the pattern rather than around scripts:

• Additive steps roll back by doing nothing. An unused new column harms no one. Leave it in place and fix forward.
• The read switch rolls back with a flag. If reads moved behind a feature flag, reverting is a toggle, not a deploy.
• The contract step is the point of no return. Delay it by at least one release, confirm nothing reads the old column, and take a snapshot or confirm a recent point-in-time backup first.
• Application rollbacks must still work. Every schema state should support the previous version of the code, so rolling back the app never requires touching the database.

For bad data, rolling forward with a corrective migration is almost always safer than trying to reverse a migration under pressure.

This is the checklist we use for zero downtime database migrations. Before a migration is merged, the author and the reviewer should both be able to answer each question:

• Does the current production code still work against the new schema?
• Does the previous release still work, in case we roll back the application?
• Is there a lock_timeout, and is every new index created concurrently?
• Does anything rewrite a large table, or scan it while holding an exclusive lock?
• Are backfills batched, idempotent and running outside the migration?
• Is anything dropped that code, reports or integrations might still read?
• Has the migration run against a copy of production-sized data?

It takes minutes to review and is one of the cheapest forms of insurance in the whole delivery pipeline.

On the products we build, schema changes ship through the same pipeline as code and follow the pattern above from the first release, because the habits are cheap while tables are small and essential once they are not. On a platform like Viewings, where agents, landlords and tenants book viewings, submit applications and sign agreements, a migration that locks the bookings table at a busy moment is not a technical footnote. It means failed viewings and lost trust.

Zero downtime database migrations are one part of the wider release practice we describe in shipping faster without breaking production, built on the schema defaults in our PostgreSQL architecture guide. If your product needs this kind of care, it is part of our software product engineering work and of ongoing product iteration after launch.