Database Migration Strategies in 2026: Zero Downtime

A zero downtime database migration is a change to your schema or data that is split into steps small enough that the running application never breaks: add the new structure, make the code work with both shapes, move the data, then remove the old structure. The pattern is called expand-and-contract, and it is the core of every production migration strategy. Everything else in this guide (online DDL, batching, tooling, rollback) exists to make those steps safe on large tables under real traffic.

Three kinds of database migration

The word "migration" covers three different problems. They need different plans.

Type	What changes	Typical risk	Main technique
Schema migration	Tables, columns, indexes, constraints	Locks, long DDL, breaking old code	Expand-and-contract, online DDL
Data migration	The rows themselves: backfills, reformatting, moving values between columns	Long transactions, replication lag, partial state	Batching, idempotent jobs, reconciliation
Platform migration	The engine or host: MySQL to PostgreSQL, on-prem to managed cloud, one major version to another	Semantic differences, cutover, data loss	Replication/CDC, dual writes, rehearsed cutover

Most releases only need the first two. A platform migration is a project, not a deploy step, and usually belongs with a broader effort like migrating legacy systems to modern architectures. This article focuses on schema and data migrations, which are what teams ship every week.

One rule applies to all three: the database must never depend on a specific application version being live. Old and new code will overlap during any rolling deploy, and a migration that assumes otherwise causes an outage at exactly the moment you cannot roll back cleanly.

Backward-compatible deploys: the constraint everything follows from

During a rolling or blue-green deploy there is a window where version N and version N+1 of the application both talk to the same database. If you use canary or rolling deployments, that window can last hours. The migration has to be compatible with both.

In practice that means:

• Additive changes ship first, in their own migration, before the code that uses them. New nullable columns, new tables, new indexes.
• Destructive changes ship last, after the old code is gone. Drops, renames, NOT NULL on an existing column, type changes.
• No migration renames or drops anything the current version reads. Renames are the classic mistake: ALTER TABLE ... RENAME COLUMN is instant, and it breaks every query in the old version instantly.
• The application tolerates both shapes for the whole transition: it writes both columns, or reads the new one with a fallback to the old.

If your ORM generates a rename or a drop automatically, treat that as a signal to stop and split the change. A feature flag lets you switch reads from old to new without a deploy, which makes the middle steps reversible.

Expand-and-contract, step by step

The pattern has four phases. Each is a separate deploy or a separate migration, never bundled.

1. Expand: add the new structure without touching the old.
2. Migrate code: the application writes to both, reads from the new (with fallback).
3. Backfill: copy existing data into the new structure, in batches.
4. Contract: switch reads fully, add constraints, drop the old structure.

Example 1: renaming a column

Goal: rename users.fullname to users.display_name on a table with millions of rows.

Step 1, expand (migration, before deploy):

ALTER TABLE users ADD COLUMN display_name text;

Adding a nullable column with no default is a metadata-only change in PostgreSQL and in modern MySQL (InnoDB instant ADD COLUMN). It does not rewrite the table.

Step 2, dual write (application deploy):

The code writes both columns on every insert and update, and reads display_name with a fallback to fullname. If you cannot change every write path (legacy jobs, other services), a trigger keeps them in sync until you can:

CREATE OR REPLACE FUNCTION sync_display_name() RETURNS trigger AS $$
BEGIN
  IF NEW.display_name IS NULL THEN NEW.display_name := NEW.fullname; END IF;
  IF NEW.fullname IS NULL THEN NEW.fullname := NEW.display_name; END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER users_sync_display_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_display_name();

Step 3, backfill (batched job, not a single UPDATE):

UPDATE users
SET display_name = fullname
WHERE id IN (
  SELECT id FROM users
  WHERE display_name IS NULL AND fullname IS NOT NULL
  ORDER BY id
  LIMIT 5000
);

Run it in a loop until it affects zero rows, with a short sleep between batches. Each batch is its own transaction, so locks are short and replication keeps up.

Step 4, contract (after the old code is fully retired):

ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;
DROP TRIGGER users_sync_display_name ON users;
DROP FUNCTION sync_display_name();
ALTER TABLE users DROP COLUMN fullname;

In PostgreSQL, SET NOT NULL scans the table. On a large table, first add CHECK (display_name IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT (which only takes a weak lock), and then SET NOT NULL uses the check to skip the scan.

Four steps across at least two releases for a rename. That is the cost of not going down.

Example 2: splitting a table

Goal: move address fields out of customers into a new customer_addresses table so a customer can have several addresses.

Expand:

CREATE TABLE customer_addresses (
  id          bigserial PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  kind        text NOT NULL DEFAULT 'primary',
  street      text,
  city        text,
  postal_code text,
  country     text,
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX CONCURRENTLY customer_addresses_customer_id_idx
  ON customer_addresses (customer_id);

Dual write: the application writes the address to both the old columns and the new table. Reads come from customer_addresses where a row exists, otherwise from the old columns.

Backfill, batched by customer id range:

INSERT INTO customer_addresses (customer_id, kind, street, city, postal_code, country)
SELECT id, 'primary', street, city, postal_code, country
FROM customers
WHERE id > :last_id AND id <= :last_id + 5000
  AND street IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM customer_addresses a
    WHERE a.customer_id = customers.id AND a.kind = 'primary'
  );

The NOT EXISTS makes the job idempotent: if it dies halfway, re-running it does not duplicate rows.

Verify, before contracting: count customers with an address in the old columns but no primary row in the new table. It must be zero.

Contract: switch reads to the new table only, remove the dual write, then drop the old columns in a later release.

Long-running migrations without locking production

The expand phase is cheap when the DDL is metadata-only. It stops being cheap when the engine has to rewrite or scan the table, or when a lock queues up behind a long transaction. These are the operations to watch.

PostgreSQL

• CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY avoid the write lock. They cannot run inside a transaction, so most tools need a per-migration flag to disable the transaction wrapper (-- atlas:txmode none, Alembic autocommit_block(), Rails disable_ddl_transaction!). If interrupted, the index is left INVALID; check pg_index.indisvalid and rebuild.
• Adding a column with a volatile default (e.g. DEFAULT now()) rewrites the table; a constant default does not.
• Foreign keys and check constraints: add with NOT VALID, then VALIDATE CONSTRAINT separately.
• Always set lock_timeout (a few seconds) in the migration session. An ALTER TABLE waiting for an ACCESS EXCLUSIVE lock blocks every query behind it; a timeout makes it fail fast instead of freezing the app. Retry in a loop.
• Changing a column type generally rewrites the table. Prefer adding a new column and going through expand-and-contract.

MySQL / MariaDB

• InnoDB supports ALGORITHM=INSTANT for adding columns and ALGORITHM=INPLACE, LOCK=NONE for many index operations. Specify them explicitly so the statement fails if the engine would fall back to a copy.
• For anything that still requires a table copy, use an external online schema change tool: gh-ost (binlog-based, throttles, no triggers) or pt-online-schema-change (trigger-based). Both create a shadow table, copy rows in chunks, and swap at the end. They cost extra disk and add replication load, so run them off-peak and watch lag.

Any engine

• Batch data migrations in chunks of a few thousand rows, keyed by primary key range, one transaction per chunk.
• Make backfill jobs resumable and idempotent. Store the last processed id.
• Throttle on replication lag, not on a fixed sleep. Pause when lag exceeds your threshold.
• Never run a backfill inside the schema migration tool's transaction. Ship it as an application job or a separate script with its own monitoring.

Migration tooling compared

Tools do two things: keep an ordered, versioned history of changes, and apply the ones a given database has not seen yet. Beyond that they differ in how migrations are written, whether they detect drift between the declared schema and the real database, and how they fit in CI.

Tool	Language / ecosystem	Versioning model	Rollback support	Drift detection	CI integration
Flyway	Java CLI, SQL-first, any stack	Versioned SQL files, checksums	Undo scripts (paid tier)	validate checks applied checksums, not live schema	CLI, Maven/Gradle, Docker image
Liquibase	Java CLI, XML/YAML/JSON/SQL changesets	Changelog with changesets, checksums	Built-in rollback per changeset, tags	diff against a reference DB	CLI, Maven/Gradle, Docker image
Alembic	Python / SQLAlchemy	Revision graph (DAG), branches	downgrade per revision	autogenerate diffs models vs DB	Python, runs in any pipeline
Prisma Migrate	TypeScript / Node	Declarative schema.prisma, generated SQL history	None built in (write a new forward migration)	Detects drift on migrate dev and migrate diff	migrate deploy in CI
Rails Active Record	Ruby	Timestamped Ruby DSL, schema.rb snapshot	down per migration, reversible DSL	Compares schema.rb; strong_migrations gem flags unsafe DDL	Rake tasks
Django migrations	Python	Dependency graph per app, autogenerated	Reverse operations, migrate app 000N	makemigrations --check fails CI on missing migrations	manage.py in pipeline
golang-migrate	Go CLI and library, SQL-first	Numbered up/down SQL pairs	down files	None	Small static binary, Docker image
Atlas	Go CLI, any stack, HCL/SQL/ORM as source	Declarative or versioned, checksummed dir	Planned via migrate down (state-aware)	Core feature: schema diff, schema inspect, linting for unsafe changes	GitHub Action, lint gate, Docker image

How to read the table:

• SQL-first vs declarative. Flyway, golang-migrate and Liquibase (in SQL mode) store what you wrote. Prisma and Atlas store the desired end state and generate the diff. Declarative is faster to author but will happily generate a DROP COLUMN, so it needs a lint gate.
• Rollback support is less useful than it looks. A down migration for ADD COLUMN is fine. A down migration for DROP COLUMN cannot restore data. See the next section.
• Drift detection is the feature most teams lack and most incidents involve: a hotfix applied by hand in production that no migration file knows about. Atlas, Liquibase diff and Alembic autogenerate catch it; Flyway and golang-migrate do not.
• Unsafe-DDL linting (Atlas lint, strong_migrations for Rails, squawk for PostgreSQL SQL files) turns the rules in the previous section into CI failures. Add one whichever tool you use.

Rollback and testing strategy

The honest position on rollback: additive schema changes can be reverted, data changes usually cannot. Plan around that asymmetry.

Prefer roll-forward. Because expand-and-contract never breaks the old version, "rollback" in the expand phase means redeploying the previous application version and leaving the new column in place. It is harmless. In the contract phase, rollback is a restore from backup, which is why contract runs last and only after verification.

Rehearse against a production-like copy. Restore last night's backup (anonymized if needed) into a throwaway instance and run the full migration set. Measure wall-clock time per migration and note any that exceed a few seconds. Synthetic fixtures with a hundred rows will not show a lock problem; a copy with the real row count and index cardinality will. This doubles as the backup restore test most teams never run.

Automate the checks in CI, alongside your normal CI/CD pipeline:

• Fresh database: apply all migrations from zero, then run the test suite.
• Existing database: apply only the new migrations on top of a snapshot of the current production schema.
• Down and up again for each new migration, to prove the down script at least runs.
• Lint for unsafe operations (renames, drops, NOT NULL without default, missing CONCURRENTLY).
• Fail the build if the ORM model and the migration history disagree (makemigrations --check, prisma migrate diff, atlas migrate lint).

Verify data, not just DDL. After a backfill, run reconciliation queries: row counts per side, nulls in the new column, foreign keys with no parent. Keep the queries in the repo next to the migration.

Watch the right signals during the apply: lock waits, replication lag, p99 latency of the hottest endpoints, error rate. Wire the migration runner into the same logging and alerting as the application so an abort is a decision, not a guess.

Production migration checklist

Before merging:

• The change is split into expand, code, backfill and contract, each in its own migration or release.
• No rename, drop, type change or NOT NULL on a column the current version reads.
• Every migration is idempotent or guarded (IF NOT EXISTS, NOT EXISTS in backfills).
• Indexes on large tables use CONCURRENTLY (PostgreSQL) or INPLACE, LOCK=NONE / gh-ost (MySQL).
• Constraints are added as NOT VALID and validated separately.
• lock_timeout and statement_timeout are set for the migration session.
• Backfills are batched, resumable and run outside the migration transaction.
• Reviewed by someone who owns the database, not only the feature.

Before applying to production:

• Rehearsed on a production-sized copy, with measured duration.
• Backup verified and point-in-time recovery confirmed for the window.
• Rollback path written down: which app version to redeploy, which migration to reverse, or which restore to run.
• Applied in the right order relative to the deploy: expand before, contract after.
• Runbook has the exact commands, the abort criteria and who is on call.

After applying:

• Reconciliation queries pass.
• No invalid indexes, no unvalidated constraints left behind.
• Contract migration scheduled for a later release, with a ticket, so the old column does not live forever.

When to choose each approach

• Small table, low traffic, short maintenance window acceptable: a single locking migration is often cheaper than four steps. Measure on a copy first; "small" means the DDL finishes in well under your lock_timeout.
• Large table or strict SLA: expand-and-contract with batched backfill, every time. The extra release is the price of staying up.
• MySQL with a table copy you cannot avoid: gh-ost or pt-online-schema-change, off-peak, watching lag.
• PostgreSQL index or constraint on a hot table: CONCURRENTLY and NOT VALID / VALIDATE, with the transaction wrapper disabled for that migration.
• Many services on one schema: triggers for dual write during the transition, because you cannot coordinate every writer's deploy.
• Changing the engine or the host: logical replication or CDC into the new target, dual writes for verification, rehearsed cutover with a hard go/no-go. That is a platform migration and belongs in its own plan.

Recommendation

Adopt expand-and-contract as the default for every schema change, not as a special procedure for big ones. Use the migration tool native to your framework if it has one, and add an unsafe-DDL linter and a drift check to CI regardless of the tool. Rehearse every release's migrations on a production-sized copy; it is the single practice that catches lock and duration problems before users do. Treat rollback as roll-forward plus verified backups, and keep the contract step as a tracked follow-up so schemas do not accumulate dead columns. At Arvucore we usually recommend starting with the linter and the rehearsal environment: they cost a day to set up and remove most of the risk from every migration after that.