Online schema migrations and data backfills
Changing database schemas and moving data without downtime: expand and contract, why ALTER TABLE can lock production, online schema change tools, backfills in batches, dual writes with verification, and migrating to a new database.
Reading is half of it. See this used in a real interview: walk through Design a Payment System →
Products change, so schemas change: new columns, split tables, a different shard key, a move from one database to another. On a small database you run a migration in a maintenance window. On a large one that serves traffic around the clock, a careless ALTER TABLE can lock a hot table for an hour, and a bad backfill can overload the primary. Senior interviews often include "how would you migrate this without downtime?"
The core pattern: expand and contract
Never change something in place that running code depends on. Instead:
- Expand: add the new structure (column, table, index) alongside the old one. Old code ignores it.
- Dual write: deploy code that writes to both old and new.
- Backfill: copy existing data into the new structure in batches.
- Verify: compare old and new for correctness.
- Switch reads to the new structure, gradually behind a flag.
- Contract: stop writing the old structure, then drop it.
Each step is independently deployable and reversible until the final drop. Code and schema change in separate deploys, so old and new application versions both work during a rolling deploy. See feature flags and A/B testing.
Which DDL is dangerous
Depends on the database and version, but generally:
| Operation | Risk |
|---|---|
| Add a nullable column without a default | usually instant |
| Add a column with a default | instant in modern Postgres and MySQL 8; a table rewrite in older versions |
| Add an index | locks writes unless built concurrently (CREATE INDEX CONCURRENTLY) |
| Change a column type | often rewrites the whole table |
| Add a NOT NULL or foreign key constraint | scans the table; add as not-validated, then validate separately |
| Rename or drop a column used by running code | breaks the application |
Also watch lock queues: even a brief exclusive lock waits behind long-running queries, and every query behind it waits too. Set a short lock timeout and retry.
Online schema change tools
For changes that would rewrite a large table, tools such as gh-ost and pt-online-schema-change (MySQL) or pg_repack-style approaches create a shadow copy with the new schema, copy rows in chunks, apply ongoing changes from the binlog or triggers, and then swap tables in a quick rename. Managed databases increasingly do this internally.
Backfills
Backfilling billions of rows is a production workload. Do it carefully:
- Batch by primary key ranges (1,000 to 10,000 rows), never one giant statement.
- Throttle based on replica lag and database load; pause automatically when lag grows.
- Idempotent and resumable: record progress so a crash restarts where it stopped; rerunning a batch must be harmless.
- Race with live writes: rows changed during the backfill must not be overwritten with stale data; write only if the target is still empty, or compare versions.
- Run as a background job with metrics and a kill switch.
Verification
Before switching reads, prove the new data is right:
- Row counts and checksums per key range.
- Shadow reads: read both old and new on live requests, return the old result, and log mismatches.
- Investigate mismatches until they reach zero (or are explained), then switch reads gradually.
Migrating to a new database or shard key
Moving data to a different database, or resharding, follows the same plan at a larger scale:
- Snapshot the old store into the new one.
- Stream ongoing changes with change data capture (or dual writes) to keep it current.
- Shadow-read and compare.
- Shift reads, then writes, gradually, per tenant or per percentage of keys.
- Keep the old store in sync in reverse for a while so you can roll back.
- Decommission.
See sharding and partitioning.
Interview example
"We need to move URL mappings from Postgres to a key-value store as traffic grows." Answer: dual write through the service, backfill with throttled batches, verify by shadow reads, shift reads per percentage of keys behind a flag, keep a rollback path, then retire the old table. See Design a URL Shortener. For payments, add that ledger data must reconcile exactly before cut-over. See Design a Payment System.
Checklist
- Expand and contract; schema and code changes deployed separately.
- Know which DDL locks or rewrites; use concurrent index builds and lock timeouts.
- Shadow-table tools for large rewrites.
- Batched, throttled, idempotent, resumable backfills.
- Verification by checksums and shadow reads before switching.
- Gradual cut-over with a rollback path.