Deploys and releases

Database migrations that survive a zero-downtime deploy

2026-11-09 · 10 min read

A blue/green deploy gets you two releases running at the same time for a few seconds. The old one is still serving requests it started before the switch; the new one is taking the rest. Both talk to one database. If the new release needs a schema change the old release cannot cope with, those few seconds are an outage, and it is the kind that no health check catches because both releases are individually fine. This post covers the discipline that makes schema changes safe during overlap: expand and contract, the shape of common changes, and the specific traps in SQLite and Postgres.

Who needs this

• A Go service on Postgres that renames a column. The new release reads display_name, the old one still writes name. Between the switch and the old instance stopping, writes land in a column the new release ignores, and some rows end up with an empty name that nobody notices for a week.
• A SQLite app that adds a NOT NULL column. The new release inserts with the column, the old one inserts without it. The old release's inserts start failing with constraint errors for exactly as long as the overlap lasts, and again for as long as you keep it as your rollback target.
• A Postgres table with a few million rows that gets an index. A plain CREATE INDEX blocks writes for as long as it builds. On the production table that means a stalled checkout, not a failed deploy, and the deploy script will happily report success.
• Anyone who wants rollback to stay a one-minute operation. A rollback restarts the previous code against the current schema. If your migrations are not backwards compatible, the rollback in roll back a bad deploy in under a minute is not safe.

The rule: the schema must serve two releases at once

During a deploy, at any moment, either release N or release N+1 may be handling a request. So the database has to work for both. Equivalently: each migration must be compatible with the code that was deployed before it, and each code release must be compatible with the schema from before its migration.

That sounds restrictive, and it is, but it reduces to a small pattern. Split every breaking change into steps that are each safe alone, and ship them in separate releases.

Expand, then contract

1. Expand. Add the new thing (column, table, index) without removing or changing anything that release N uses. Deploy a release that can work with both shapes.
2. Migrate data. Backfill existing rows into the new structure, in batches, while both shapes are kept in sync (the app writes both, or a trigger does).
3. Switch. Release N+2 reads from the new structure only.
4. Contract. Once you will not roll back past that point, drop the old column or table in a later release.

Take renaming name to display_name:

Four steps for one rename. It is more work than ALTER TABLE ... RENAME COLUMN, and that single statement is exactly what breaks the instance that is still running the old code. You pay the cost once per breaking change, and in exchange the deploy never takes the site down.

Changes that are safe, and changes that are not

Where to run the migration

Rule of thumb: migrate before the switch, from one place, once. Never from two instances racing at startup.

Run migrations before the new release takes traffic, and before the old release stops, from one place that runs once. Three common choices:

• A separate step in your deploy pipeline, run by CI or by a wrapper around the deploy script, using the new release's binary (app migrate). It runs exactly once, and a failure stops the deploy before anything switches. This is our preference.
• At app startup, inside the new instance. Simple, but with two instances starting you need a lock, and a slow migration makes the readiness check time out and fail the deploy. If you do this, take a Postgres advisory lock (pg_advisory_lock) so only one process migrates, and keep migrations quick.
• By hand. Fine for a side project, if you write down the order.

The deploy kit does not touch your database. It gives you a safe slot to put this step in (before deploy.sh, while the old release is serving), and its README says plainly that both releases run side by side for a few seconds and your migrations must be backward compatible. The gate in deploy health checks that mean something is the right place to confirm that the new release can still talk to the migrated schema.

SQLite specifics

SQLite is simple and a little unforgiving. Its ALTER TABLE is limited, and the database is a single file shared by both instances.

What ALTER TABLE can do. ADD COLUMN is fast and safe (with restrictions: a NOT NULL column needs a non-null default, and a default cannot be a non-constant expression). RENAME COLUMN exists since 3.25 and DROP COLUMN since 3.35. Check yours with sqlite3 --version or SELECT sqlite_version(); and make sure the version linked into your app, not just the CLI on the server, is recent enough. For anything else (change a type, add a constraint, reorder), SQLite's documented procedure is to create a new table, copy the data, drop the old table and rename the new one, all in one transaction:

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL DEFAULT ''
);
INSERT INTO users_new (id, email, display_name)
  SELECT id, email, COALESCE(name, '') FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;
PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;

This is a hard switch, not an expand/contract. During the transaction the other instance is blocked, and afterwards it sees a different shape. Use it only when the app can handle the change, or when you can accept a short, planned pause.

Locking. SQLite allows one writer at a time. In rollback-journal mode a writer also blocks readers; in WAL mode (PRAGMA journal_mode=WAL) readers continue while a writer works. Two app instances on one host can safely share a WAL-mode database file, but a migration holding the write lock makes the other instance's writes wait, and without a timeout they fail instantly with SQLITE_BUSY. Set PRAGMA busy_timeout = 5000 (the value is milliseconds) on every connection so writers wait instead of erroring.

Stale reads across processes. Overlap means two processes with open connections and, possibly, cached prepared statements. After a schema change, old statements are recompiled by SQLite, which is usually fine. If your Go code caches anything derived from the schema, see go-sqlite stale reads for the traps.

Take a copy first. Before a table rebuild, make a consistent copy with sqlite3 app.db ".backup /var/backups/app-pre-migrate.db". That command uses SQLite's backup API and is safe while the app runs, unlike cp on a live file.

Postgres specifics

Postgres lets you do a lot online, but the dangerous operations look innocent.

The lock queue. Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock. If a long query is running on the table, your ALTER waits for it, and while it waits, every new query on that table queues up behind the ALTER. A statement that would take milliseconds can stop the site for as long as the oldest transaction runs. Defend with timeouts:

SET lock_timeout = '3s';
SET statement_timeout = '60s';
ALTER TABLE orders ADD COLUMN note text;

If the lock cannot be acquired within three seconds, the statement fails and nothing was blocked for long. Retry later. Failing quickly is the correct behaviour for a migration in a deploy.

Adding columns. ADD COLUMN with no default, or with a constant default, is a metadata change on Postgres 11 and later. A volatile default (DEFAULT random(), clock_timestamp()) rewrites the whole table under the exclusive lock. Add the column without a default, set the default separately for new rows, and backfill in batches.

Backfills in batches. One UPDATE orders SET new_col = old_col on a big table holds locks, bloats the table and generates a large burst of WAL. Do it in slices:

UPDATE orders SET display_total = total
WHERE id IN (
  SELECT id FROM orders WHERE display_total IS NULL ORDER BY id LIMIT 5000
);
-- repeat from the app or a script until 0 rows are updated, pausing between batches

Indexes. CREATE INDEX blocks writes for the duration of the build. CREATE INDEX CONCURRENTLY does not, but it cannot run inside a transaction block, so a migration tool that wraps each file in a transaction needs that file to opt out. If a concurrent build fails, it leaves an INVALID index behind; check with \d tablename, drop it, and try again.

Constraints. ALTER TABLE ... SET NOT NULL scans the table under an exclusive lock. On Postgres 12 and later it can skip the scan if a validated CHECK (col IS NOT NULL) constraint already exists. The pattern for large tables:

ALTER TABLE orders ADD CONSTRAINT orders_note_nn CHECK (note IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_note_nn;  -- scans, but does not block writes
ALTER TABLE orders ALTER COLUMN note SET NOT NULL;      -- fast, uses the validated constraint
ALTER TABLE orders DROP CONSTRAINT orders_note_nn;

Foreign keys work the same way: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID, then VALIDATE CONSTRAINT.

Transactions are your friend. Postgres DDL is transactional. A migration of several statements either applies fully or not at all, which is much easier to reason about than the half-applied state you can get in MySQL. Except for CONCURRENTLY operations, which must sit outside one.

Keep a way back that includes the data

Even disciplined changes can go wrong. Take a backup before any migration that rewrites data, and know how to restore it. For Postgres, a dump of the one affected table (pg_dump -t orders) is a fast safety net; for a whole-cluster story, read pg_dump vs physical backup vs PITR. A rollback of code is cheap; a rollback of data is a restore, and you want to have rehearsed it.

Mistakes we see

Renaming in one step. RENAME COLUMN is instantly visible to both releases, so one of them is wrong. Use expand and contract.

Shipping the drop with the code that stops using the column. If the old release is still running or you need to roll back, the column has to exist. Drop it one release later.

Migrating inside the readiness path. A slow migration makes the health check time out, the deploy fail and the instance restart, which tries the migration again. Run migrations as their own step.

No lock_timeout. The migration waits behind one long query and the whole table queues behind it. Always set a timeout and retry.

Backfilling in one statement. It works on your laptop with 1,000 rows. It stalls production with ten million. Batch it.

Trusting an ORM's auto-migrate. Some tools generate a table rebuild or a plain CREATE INDEX for you. Read the SQL they will run.

Assuming the old release is gone. For a few seconds it is not, and with KEEP_PREVIOUS=1 it can stay up much longer. Design for overlap as the normal case.

FAQ

Can I run database migrations with zero downtime?

Yes, if each migration is compatible with the code currently running and each release is compatible with the schema before its migration. Split breaking changes into expand, backfill, switch and contract steps across separate releases. The extra releases are the price of never taking the site down.

What is the expand and contract pattern?

You first add the new structure alongside the old one (expand), move the data and let the code use both, switch reads to the new structure, and only later remove the old one (contract). At each point the database works for the release before and the release after, which is what makes a rolling or blue/green deploy safe.

How do I add an index to a large Postgres table without blocking writes?

Use CREATE INDEX CONCURRENTLY. It takes longer and cannot run inside a transaction, but it does not block inserts and updates. If it fails it leaves an invalid index, which you drop and retry. Set a sensible statement_timeout for your session so that a stuck build does not run forever.

Is ALTER TABLE safe on a live SQLite database?

ADD COLUMN is, and RENAME COLUMN and DROP COLUMN are supported on current versions. Others require the create-copy-rename procedure inside a transaction, which blocks writers briefly. Use WAL mode and busy_timeout so that the other instance waits instead of failing, and take a .backup copy before you start.

Should the app run migrations on startup?

It can, for small apps, but with two instances starting during a deploy you need a lock so that only one runs them, and a slow migration will fail your readiness check. Running migrations as a separate step before the traffic switch is simpler and fails the deploy at the right place.

The Go + systemd deploy kit handles the release switch, the health gate and the rollback around this, and leaves the schema to your own migration tool, which is where the expand and contract steps above belong.

postgressqlitezero-downtime