Online schema and data migrations

An online migration changes a database’s schema, or moves its data to a new table or a new store, while the application keeps serving traffic. You never make one big, irreversible change. You split it into small steps, each compatible with the code running before and after it, and each with its own way back. Most of the danger is not the data copy: it is the locks, the replicas and the moment you switch over.

Picture it first: widening a busy road

The city wants to widen a two-lane road without closing it. Closing it for a night and digging it up is the plain ALTER TABLE: fine for a village lane, a disaster for the road to the airport. Instead you build new lanes beside the old ones (expand), shift traffic across a lane at a time with cones you can move back (migrate), and only dig up the old surface once everyone uses the new lanes (contract).

Every Bengaluru commuter knows the second lesson. A marshal stops all cars at a junction so one wide truck can turn, but the truck is stuck behind a broken-down bus. The marshal waits, and so does every car behind him. The turn takes ten seconds; the jam lasts half an hour.

Map it back: the road is a table, the cars are queries, the truck is a DDL statement that needs a moment of exclusive access, the bus is a long-running transaction, and the marshal’s patience is your lock timeout.

Why a plain ALTER TABLE hurts

A schema change on a live table can cost you in three ways.

• Locks. Most DDL needs an exclusive lock, at least for a moment. PostgreSQL’s ALTER TABLE takes ACCESS EXCLUSIVE unless the docs name a weaker mode for that form, and it conflicts with everything, even the ACCESS SHARE lock a plain SELECT takes.
• Rewrites. Some changes rebuild every row and index: changing a column’s type in PostgreSQL, or anything MySQL runs with ALGORITHM=COPY. The work grows with the table, and PostgreSQL warns that a rebuild can temporarily need twice the disk space.
• Replication. In MySQL a long online DDL starts on a replica only after it finishes on the source, and later writes wait behind it there.

Put a number on a rewrite: a 400 GB orders table rebuilt at 200 MB/s takes 400,000 MB ÷ 200 MB/s = 2,000 s, about 33 minutes of failed checkouts under an exclusive lock.

The lock-queue pile-up

The surprising failure hits changes that should be instant. PostgreSQL grants a lock only if it conflicts with neither the locks already held nor the requests already waiting (its lock manager README spells out the rule). MySQL’s manual says the same of metadata locks: a pending exclusive lock requested by DDL blocks subsequent transactions on the table.

1. At t = 0, a report opens a transaction that reads orders and holds ACCESS SHARE for 90 seconds.
2. At t = 5 s, a deploy runs ALTER TABLE orders ADD COLUMN gift_note text. It only touches the catalog, but needs ACCESS EXCLUSIVE, so it waits for the report.
3. Every new query on orders, even a plain SELECT, now conflicts with the waiting ALTER and queues behind it.

Say orders sees 2,000 queries a second through a pool of 200 connections. The pool is full in 200 ÷ 2,000 = 0.1 s, and checkout is down for the next 85 seconds, not because of the ALTER but because of what it waits for. Neither database protects you by default: PostgreSQL’s lock_timeout defaults to 0, meaning wait forever, and MySQL’s lock_wait_timeout defaults to 31,536,000 seconds, a year.

The fix costs nothing: give every DDL statement a short lock timeout, and retry.

SET lock_timeout = '2s';        -- stop waiting for the table lock after 2 s
SET statement_timeout = '30s';  -- and cap the statement as a whole
ALTER TABLE orders ADD COLUMN gift_note text;
-- On "canceling statement due to lock timeout": sleep with jitter and retry a few
-- times, then find the blocker in pg_stat_activity instead of forcing your way in.

Now the worst case is a 2-second stall per attempt instead of an 85-second outage. Retry with backoff and jitter; if the lock never comes, find the oldest open transaction rather than raising the timeout. Set these in the migration’s session only: PostgreSQL’s docs advise against putting lock_timeout in postgresql.conf, where it would hit every session. MySQL’s equivalent is SET SESSION lock_wait_timeout = 5.

PostgreSQL: what is safe, and how to make the rest safe

Two questions decide whether a PostgreSQL change is safe: which lock does it take, and for how long? A strong lock held for a millisecond is fine behind a lock timeout; one held while the table is scanned or rewritten is an outage.

CREATE INDEX CONCURRENTLY lets reads and writes carry on, with fine print. It scans the table twice and waits for older transactions, so it is slower and still stalls behind a long transaction. It cannot run inside a transaction block, and many migration tools wrap each migration in one unless told not to. If it fails, say on a duplicate key, it leaves an INVALID index that queries ignore but every write still maintains: drop it and retry.

Constraints take two steps: enforce the rule for new rows at once, then check old rows under SHARE UPDATE EXCLUSIVE, a lock that lets reads and writes continue.

-- Foreign key: enforce for new rows now, check existing rows later
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;  -- no scan
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;         -- reads, writes continue

-- NOT NULL without an exclusive full-table scan (PostgreSQL 12+)
ALTER TABLE orders ADD CONSTRAINT orders_city_nn CHECK (city IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_city_nn;
ALTER TABLE orders ALTER COLUMN city SET NOT NULL;   -- proven by the check: no scan
ALTER TABLE orders DROP CONSTRAINT orders_city_nn;

-- Unique: build the index without blocking writes, then attach it
CREATE UNIQUE INDEX CONCURRENTLY orders_ref_uq ON orders (payment_ref);
ALTER TABLE orders ADD CONSTRAINT orders_ref_key UNIQUE USING INDEX orders_ref_uq;

Each statement still takes a strong lock for an instant, so each runs inside the lock-timeout-and-retry wrapper. Database indexes covers what the index itself buys you.

MySQL: online DDL and its limits

InnoDB chooses one of three algorithms for an ALTER TABLE:

• INSTANT changes only the data dictionary: adding, dropping or renaming a column, setting a default. MySQL 8.4 uses it wherever supported.
• INPLACE works inside InnoDB and usually allows concurrent writes, though some operations still rebuild the table: adding a secondary index doesn’t, adding a primary key does.
• COPY builds a new table and blocks writes until done. Changing a column’s data type works only this way.

Without an ALGORITHM clause, MySQL quietly falls back from INSTANT to INPLACE to COPY. Name the algorithm you expect, and an unsupported change fails instead of silently copying a 400 GB table:

SET SESSION lock_wait_timeout = 5;   -- seconds; the default is one year
ALTER TABLE orders ADD COLUMN gift_note VARCHAR(200), ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_city (city), ALGORITHM=INPLACE, LOCK=NONE;

The LOCK clause works the same way: NONE permits reads and writes, SHARED only reads, EXCLUSIVE neither, and asking for a level the operation can’t support fails the statement.

Three limits remain. Online DDL still needs an exclusive metadata lock for a moment to commit the new definition, so it suffers the same pile-up. Instant column changes are capped at 64 row versions per table in MySQL 8.4 (255 from 9.1.0), after which you must rebuild. And a long INPLACE rebuild can’t be paused or throttled; as GitHub’s engineers put it, it is only “online” on the server where it runs, while replicas fall behind.

Expand, migrate, contract

Some changes can’t be made compatible in one step: renaming a column, splitting a table, changing a type. The answer is parallel change, or expand and contract: add the new shape beside the old one (expand), move every reader and writer across (migrate), then remove the old shape (contract).

The reason is the rolling deploy. For several minutes, servers running versions N and N+1 share one database, and a rollback brings N back after N+1 has written data. So every schema state must work with two app versions, the one before it and the one after. Rename customers.phone to mobile in one statement and every server still on the old code fails the moment the rename commits.

The safe rename:

1. Expand. ADD COLUMN mobile text, nullable. Version N doesn’t know it exists. Way back: drop it.
2. Write both. Deploy N+1, which writes both columns but still reads phone. Rolling back to N is safe: phone is complete.
3. Backfill. Copy phone into mobile for old rows in throttled batches (see below).
4. Read new. Deploy N+2, which reads mobile and still writes both. Rolling back to N+1 is safe because phone is still written.
5. Write new. Deploy N+3, which stops writing phone. Rolling back to N+2 is fine, but going further back needs a reverse backfill. If your ORM caches column lists, mark phone as ignored now.
6. Contract. Drop phone in a later release, after a bake period. Only a restore undoes this, which is why it comes last.

Six deploys sounds like a lot, but they are small, reversible changes instead of one that can’t be undone.

Splitting a table follows the same shape: create order_addresses, write addresses to both places, backfill, switch reads, stop writing the old columns in orders, then drop them. A database trigger can do the “write both” step instead of app code, saving a deploy but hiding logic in the database and adding work to every insert.

Online schema change tools for MySQL

When InnoDB can’t make a change without blocking writes, or you need to pause and throttle a long rebuild, use a shadow-table tool. They share one plan: create an empty copy with the new schema, copy rows across in chunks, keep the copy in step with live writes, then swap the tables with an atomic RENAME TABLE. They differ in how they keep the copy in step.

pt-online-schema-change, from Percona Toolkit, adds AFTER INSERT, UPDATE and DELETE triggers to the original table, so every application write also writes the copy in the same transaction. It sizes chunks so each copy query takes about 0.5 s, pauses while any replica lags more than 1 s or Threads_running exceeds 25, and aborts above 50. It can re-point foreign keys from child tables.

gh-ost, released by GitHub in 2016, uses no triggers. It connects as if it were a replica, reads the row-based binary log, and applies the table’s events to the copy itself, interleaved with its own row copy over one connection. GitHub built it after trigger-based migrations caused near or complete lock-downs on busy tables: triggers run inside application transactions and compete for locks there, and they can’t be paused, because removing them mid-copy loses changes.

Because gh-ost issues every write itself, throttling really stops the migration. It throttles on replica lag (1.5 s by default), load, a query or a flag file, and takes new settings while running. It can postpone the cut-over until you delete a flag file, so a copy that finishes at 2 am waits for someone awake. The cut-over is an atomic rename; if it can’t get its locks within 3 s by default, the original table stays put and gh-ost tries again.

A sensible default: ALGORITHM=INSTANT whenever it works, gh-ost for rebuilding hot tables, pt-online-schema-change when foreign keys force your hand. Test first: gh-ost can run the whole migration on a replica and leave both tables for you to checksum. PostgreSQL has no such tool in core; there you rely on the concurrent and NOT VALID forms plus expand/contract.

Backfills without hurting production

A backfill writes data into existing rows: filling mobile from phone, computing a column, copying rows to a new table. The tempting version is one statement:

UPDATE customers SET mobile = phone WHERE mobile IS NULL;   -- on 200M rows: don't

On 200 million rows, that one transaction holds row locks on everything it has touched until it commits, so users editing their profile wait. In PostgreSQL it leaves 200 million dead row versions for vacuum. Replicas get one enormous burst of log, and a MySQL replica can’t start applying it until it commits. If it fails at 90%, all of it rolls back.

Instead, follow five rules:

• Batch by primary-key range, a few thousand rows per transaction, using id > :last AND id <= :next rather than OFFSET.
• Throttle. Sleep between batches; pause when replica lag or load crosses a threshold.
• Make it idempotent. Re-running a batch must change nothing: WHERE mobile IS NULL, or ON CONFLICT DO NOTHING.
• Make it resumable. Save the last key in the batch’s own transaction, so a crash restarts where it stopped.
• Start it after the dual-write deploy, so rows created meanwhile are already handled by version N+1.

import time

BATCH, MAX_LAG_S, PAUSE_S = 5_000, 1.5, 0.1

def backfill_mobile(db, replica, job="customers.mobile"):
    last_id = db.fetch_value("SELECT last_id FROM backfill_progress WHERE job = %s", [job])
    max_id = db.fetch_value("SELECT max(id) FROM customers")  # newer rows: written by N+1
    while last_id < max_id:
        while replica.lag_seconds() > MAX_LAG_S:   # throttle on replica lag
            time.sleep(5)
        upper = min(last_id + BATCH, max_id)
        with db.transaction():                      # short: at most 5,000 rows
            db.execute("""UPDATE customers SET mobile = phone
                          WHERE id > %s AND id <= %s AND mobile IS NULL""",
                       [last_id, upper])            # idempotent: skips done rows
            db.execute("UPDATE backfill_progress SET last_id = %s WHERE job = %s",
                       [upper, job])                # checkpoint in the same transaction
        last_id = upper
        time.sleep(PAUSE_S)                         # leave room for real traffic

The arithmetic: 200 million rows in batches of 5,000 is 200,000,000 ÷ 5,000 = 40,000 transactions. At about 100 ms of work plus a 100 ms pause each, that is 40,000 × 0.2 s = 8,000 s, a little over 2 hours, and no transaction holds anything for long. Bigger jobs need parallelism: Stripe notes that at one second per object, migrating 100 million objects one by one would take over three years (100,000,000 s ÷ 31.5 million s per year ≈ 3.2 years), so they found the objects with offline jobs over database snapshots and ran many processes in parallel.

Moving data between stores

Sometimes the data itself moves: orders leave the monolith’s database for a new service, or one database becomes a sharded cluster. The old store stays the source of truth until the new one has proven itself, and every step has a way back.

1. Capture changes. Send every new write to the new store too, by dual writes from the application or by change data capture (CDC) from the old store’s log.
2. Backfill. Copy historical rows from a snapshot or in batches. Step 1 covers writes made during the copy, so the copy must never overwrite a newer value with an older one: compare a version or updated_at first.
3. Verify. Compare counts and checksums per key range, then run shadow reads: serve from the old store, read the new one in the background, log every mismatch.
4. Move reads behind a flag: 1%, 10%, 50%, 100%, watching mismatches and latency.
5. Move writes. The new store becomes the source of truth. Keep the old one updated for a while (reverse CDC or dual writes), so rollback is a flag flip, not a data recovery.
6. Clean up. Remove the old path and flags; archive and drop the old data.

Stripe’s “Online migrations at scale” moved hundreds of millions of subscription objects in four phases: dual writing, changing all read paths, changing all write paths, removing old data. Worth copying: they ramped dual writes by percentage, compared old and new reads in production with GitHub’s Scientist library, and made the old field raise an error when read, so forgotten code paths announced themselves.

With dual writes, keep the old store primary: write it first, treat the new write as best effort, never fail a user’s UPI payment because the new store hiccuped, and let a reconciliation job repair drift. The transactional outbox gives CDC-like guarantees from application code.

The riskiest instant is step 5: writes in flight may not have reached the new store when the flag flips. The usual fix is a short write freeze per shard or tenant: stop writes for a few seconds, wait for CDC lag to reach zero, flip, resume.

Migration tooling and process

• Versioned migrations in the repository. Flyway (V42__add_mobile.sql), Liquibase and Rails-style migrations apply numbered changes in order and record what has run in a history table, so every environment converges on the same schema.
• Test on production-sized data. A migration that takes 40 ms on a 10,000-row CI table can take 40 minutes on 400 million rows. Time it on a recent copy and note which lock it takes.
• Lint for dangerous operations: type changes, non-concurrent indexes, constraints without NOT VALID, renames, missing lock timeouts.
• Ship schema changes separately from code. Expand before the code that needs it; contract after the code that stopped using it. Flyway’s docs suggest migrating at application start-up; with 50 pods and a slow change, one pipeline step is easier to watch and stop.
• Roll forward, mostly. A “down” migration rarely restores data. Your real rollback is the previous app version, which expand/contract keeps compatible; see deployment strategies.

Put the plan in the pull request: each step, its lock, its measured duration, the metric you’ll watch and how you’d undo it.

Going deeper: what goes wrong

Long transactions block everything

An idle-in-transaction session or a three-hour report is the usual villain. It blocks DDL through the lock queue, stalls CREATE INDEX CONCURRENTLY, and in PostgreSQL holds back vacuum. Check for old transactions before migrating, and set idle_in_transaction_session_timeout for application roles so forgotten sessions close themselves.

Replica lag from big changes

A rebuild or backfill writes as fast as the primary allows, and replicas replay it, often more slowly. Readers on replicas see stale data, and failing over to a lagging replica means waiting or losing writes. Throttle every job on replication lag.

Triggers double the write cost

Trigger-based tools and trigger-based dual writes turn every write on a hot table into two inside one transaction: longer transactions and more lock contention exactly where the table is busiest. GitHub saw the triggers’ load bring a primary down even while the row copy was throttled.

Races at the cut-over

During a flip or a table swap, a write can land on the side you just left. Keep both sides in sync across every flip, read the flag once per request, and swap tables atomically: a two-step rename leaves a gap in which the table doesn’t exist.

Forgetting to contract

The team moves on, and the old column, dual-write code and flag live forever. The ParallelChange article warns this can leave you worse off than where you started. Raise the contract ticket when you expand.

Running out of integers

A signed 32-bit id stops at 2,147,483,647. A table taking 100 inserts a second uses that up in 2,147,483,647 ÷ 100 ≈ 21.5 million s, about 8 months. Converting to bigint rewrites the table in PostgreSQL and needs ALGORITHM=COPY in MySQL, so it becomes a full expand/contract migration on your hottest table. Use bigint keys from day one.

Cheat sheet

How to say it in the interview

“Say we need to rename a column on a 300-million-row orders table with no downtime. I wouldn’t do it in one ALTER: the rename is instant, but every server still on the old code breaks the moment it commits, and the ALTER can pile queries up behind a long transaction. So it’s expand and contract. I add the new column with a short lock timeout and retries, deploy code that writes both, and backfill in 5,000-row primary-key batches, throttled on replica lag and checkpointed. Then I switch reads, stop writing the old column, and drop it a release later. At every step the previous app version still works, so rollback is just a deploy. Moving data to a new database follows the same shape: CDC, backfill, shadow reads, then flags for reads and writes.”

Why not just run the ALTER at 3 am? Off-peak shrinks the blast radius but doesn’t remove it: the lock queue still forms behind a long transaction, a rewrite takes as long, and replicas still lag. And a UPI payments system has no quiet hour.

How do you roll back a migration? Mostly you roll back the code instead. Expand/contract keeps the previous app version compatible at every step, and destructive steps come last, after a bake.

Dual writes or CDC? CDC when you can: every committed change, in order, from any writer. Dual writes when you need domain logic in the transformation or have no CDC pipeline, with the old store primary and a reconciliation job for drift.

How do you know the new store is right before cutting over? Counts and checksums per key range, plus shadow reads comparing old and new on live traffic. Move reads by percentage once mismatches are zero or explained, keeping the old store in sync so the switch can be reversed.

gh-ost or pt-online-schema-change? gh-ost for hot tables: no triggers, a true pause, live tuning and a cut-over you choose. pt-online-schema-change when foreign keys point at the table or you can’t use row-based binary logs.

Where to go next

• Deployment strategies: rolling deploys, flags and why rollback needs compatible data.
• Migrating a legacy system: the same incremental idea for whole systems.
• Change data capture: reading a database’s log to feed another store.
• Sharding a relational database: the most common reason to move data between stores.
• MVCC: why a giant UPDATE leaves a dead copy of every row.
• Serialization and schema evolution: backward-compatible changes for messages and APIs.

References and further reading

1. PostgreSQL documentation: ALTER TABLEpostgresql.orgLock level per subcommand, which forms rewrite the table, metadata-only ADD COLUMN with a non-volatile default, NOT VALID plus VALIDATE CONSTRAINT, and SET NOT NULL proven by a CHECK constraint.
2. PostgreSQL documentation: CREATE INDEX (building indexes concurrently)postgresql.orgPlain index builds block writes; CONCURRENTLY scans twice, waits for older transactions, cannot run in a transaction block and can leave an INVALID index behind.
3. PostgreSQL documentation: Explicit lockingpostgresql.orgThe table-level lock modes, which statements take them, and the conflict table showing that ACCESS EXCLUSIVE blocks even a plain SELECT.
4. PostgreSQL documentation: Client connection defaults (lock_timeout)postgresql.orglock_timeout, statement_timeout and idle_in_transaction_session_timeout: what each bounds, the default of zero, and why not to set them globally.
5. MySQL 8.4 Reference Manual: Online DDL operationsdev.mysql.comWhich column, index and key changes are INSTANT, INPLACE or COPY, which rebuild the table, and the 64 row-version limit for instant column changes.
6. MySQL 8.4 Reference Manual: Online DDL performance and concurrencydev.mysql.comThe LOCK clause, the three phases of an online DDL and their metadata locks, and how a pending exclusive metadata lock blocks later transactions.
7. gh-ost: GitHub’s online schema migration tool for MySQL (GitHub blog, 2016)github.blogWhy trigger-based tools hurt busy tables, how gh-ost tails the binary log instead, and its throttling, pausing, live reconfiguration, postponed cut-over and test-on-replica modes.
8. Percona Toolkit documentation: pt-online-schema-changedocs.percona.comTrigger-based shadow copy, chunk sizing by --chunk-time, the --max-lag and --max-load pauses, atomic RENAME TABLE swap and foreign-key handling.
9. ParallelChange (Danilo Sato, martinfowler.com)martinfowler.comExpand, migrate and contract as a pattern for backward-incompatible changes, its use in database refactoring, and the risk of never contracting.
10. Online migrations at scale (Stripe engineering blog)stripe.comThe four-phase dual-writing pattern used to move hundreds of millions of Subscriptions objects, with offline backfill discovery and Scientist read comparisons.