September 30, 2026
Zero-Downtime Database Migrations: How to Alter 50M-Row Tables Without Locking Production
A single naive ALTER TABLE can take down your entire platform. Here is how to navigate lock queues, phased rollouts, and safe backfills.

By Abul Kalam Azad
7 min read
Every senior engineer remembers their first catastrophic production migration.
It usually starts with a ticket that seems completely harmless: "Add an email_verified boolean flag to the accounts table, defaulting to false."
You generate a standard migration file using your ORM:
ALTER TABLE accounts
ADD COLUMN email_verified BOOLEAN DEFAULT false NOT NULL;ALTER TABLE accounts
ADD COLUMN email_verified BOOLEAN DEFAULT false NOT NULL;You deploy the migration during an off-peak window. The command starts running. Two seconds pass. Five seconds. Thirty seconds.
Suddenly, PagerDuty begins firing.
Every single API endpoint that touches the accounts table stops responding. Your connection pools hit their maximum limits. Web servers report 504 Gateway Timeouts.
You panic and try to cancel the migration query, but your terminal hangs. You restart the database container, only to trigger a crash recovery process that keeps the entire platform offline for forty-five minutes.
What went wrong? You ran a naive DDL (Data Definition Language) statement against a high-traffic, multi-million-row table without understanding lock queues and table-level locks.
Altering a table in production isn't just about syntax. It is a game of lock mechanics, connection queueing, and non-blocking rollout patterns.
The Silent Killer: Lock Queue Starvation
Why does an ALTER TABLE statement bring down an entire system even if the table scan itself takes only a few seconds?
The answer lies in lock queues.
In relational databases like PostgreSQL and MySQL, queries acquire different levels of locks. A standard SELECT query acquires an AccessShareLock (shared lock). Multiple SELECT queries can read the same table simultaneously without blocking each other.
However, an ALTER TABLE statement requires an AccessExclusiveLock. This lock conflicts with every other lock type.
Time | Active Queries | Status
โโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโ
T1 | SELECT * FROM accounts WHERE id = 42; | Running (Holds shared lock)
T2 | ALTER TABLE accounts ADD COLUMN ... | WAITING for T1 to finish
T3 | SELECT * FROM accounts WHERE id = 100; | BLOCKED behind ALTER TABLE
T4 | SELECT * FROM accounts WHERE id = 500; | BLOCKED behind ALTER TABLETime | Active Queries | Status
โโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโ
T1 | SELECT * FROM accounts WHERE id = 42; | Running (Holds shared lock)
T2 | ALTER TABLE accounts ADD COLUMN ... | WAITING for T1 to finish
T3 | SELECT * FROM accounts WHERE id = 100; | BLOCKED behind ALTER TABLE
T4 | SELECT * FROM accounts WHERE id = 500; | BLOCKED behind ALTER TABLEHere is the trap:
- At time
T1, a slow analytical query or unindexed read is running, holding a shared lock onaccounts. - At time
T2, your migration runs. It requests an exclusive lock. BecauseT1is still running, the migration must wait in line. - At time
T3andT4, routine user queries arrive at the database.
Logically, you might assume those fast SELECT queries would just execute alongside T1. They don't.
To prevent write starvation, database lock managers prioritize waiting DDL requests. Every single query that arrives after the migration is placed into a waiting queue behind the migration.
Within seconds, hundreds of incoming HTTP requests pile up behind the waiting ALTER TABLE. Your web servers run out of database connections, your health check endpoints fail, and your load balancer drops the entire cluster.
Rule 1: Never Run DDL Without a Lock Timeout
The most basic safeguard for any production migration is setting an aggressive lock timeout.
A lock timeout tells the database engine: "If you cannot acquire the required table lock within X milliseconds, abort the migration immediately instead of waiting in line and blocking traffic."
-- PostgreSQL: Abort if lock acquisition takes longer than 2 seconds
SET lock_timeout = '2s';
-- Set a statement timeout as well
SET statement_timeout = '5s';
-- Run the migration
ALTER TABLE accounts ADD COLUMN email_verified BOOLEAN DEFAULT false;-- PostgreSQL: Abort if lock acquisition takes longer than 2 seconds
SET lock_timeout = '2s';
-- Set a statement timeout as well
SET statement_timeout = '5s';
-- Run the migration
ALTER TABLE accounts ADD COLUMN email_verified BOOLEAN DEFAULT false;If a long-running read query blocks your migration, the statement fails harmlessly within two seconds. Your user-facing queries continue operating without disruption. Your deployment pipeline catches the error, waits a minute, and retries.
Rule 2: Adding Columns Safely (Postgres vs. MySQL)
How you add a column depends heavily on your database engine and version.
Modern PostgreSQL (v11+)
In PostgreSQL 11 and later, adding a column with a default value no longer rewrites the entire table:
-- Fast in Postgres 11+: Updates metadata only, zero row rewrite
ALTER TABLE accounts
ADD COLUMN email_verified BOOLEAN DEFAULT false NOT NULL;-- Fast in Postgres 11+: Updates metadata only, zero row rewrite
ALTER TABLE accounts
ADD COLUMN email_verified BOOLEAN DEFAULT false NOT NULL;Postgres stores the default value in the system catalog (pg_attribute). Existing rows are not updated on disk until they are mutated by future writes. The lock is held for only a few milliseconds.
Older Postgres (<11) and Legacy MySQL
In older database versions, adding a column with a default value forces the engine to rewrite every single block of the table on disk to fill in the default value, locking the table for minutes or hours.
To add a column safely in these environments, split the operation into three distinct steps:
-- Step 1: Add the column as NULLABLE without a default (instant metadata lock)
ALTER TABLE accounts ADD COLUMN email_verified BOOLEAN;
-- Step 2: Set the default for future rows (instant metadata update)
ALTER TABLE accounts ALTER COLUMN email_verified SET DEFAULT false;
-- Step 3: Backfill historical rows in small background batches (detailed below)-- Step 1: Add the column as NULLABLE without a default (instant metadata lock)
ALTER TABLE accounts ADD COLUMN email_verified BOOLEAN;
-- Step 2: Set the default for future rows (instant metadata update)
ALTER TABLE accounts ALTER COLUMN email_verified SET DEFAULT false;
-- Step 3: Backfill historical rows in small background batches (detailed below)Rule 3: Adding NOT NULL Constraints Without Table Rewrites
Adding a NOT NULL constraint to an existing column requires the database to verify that zero rows contain a NULL value.
On a 50-million-row table, a naive ALTER TABLE accounts ALTER COLUMN status SET NOT NULL runs a full table scan while holding an exclusive lock, blocking all traffic for the duration of the scan.
In PostgreSQL, you bypass this using a Two-Phase Constraint Validation:
-- Phase 1: Add a CHECK constraint, marked as NOT VALID
-- This acquires a very brief lock and does NOT scan existing data
ALTER TABLE accounts
ADD CONSTRAINT check_email_not_null
CHECK (email IS NOT NULL) NOT VALID;
-- Phase 2: Validate the constraint asynchronously
-- Scans the table with a weak lock that DOES NOT block concurrent reads or writes
ALTER TABLE accounts
VALIDATE CONSTRAINT check_email_not_null;-- Phase 1: Add a CHECK constraint, marked as NOT VALID
-- This acquires a very brief lock and does NOT scan existing data
ALTER TABLE accounts
ADD CONSTRAINT check_email_not_null
CHECK (email IS NOT NULL) NOT VALID;
-- Phase 2: Validate the constraint asynchronously
-- Scans the table with a weak lock that DOES NOT block concurrent reads or writes
ALTER TABLE accounts
VALIDATE CONSTRAINT check_email_not_null;Once validated, PostgreSQL knows no NULLs exist. You can now safely apply the native NOT NULL property with zero downtime.
Rule 4: Building Indexes Without Blocking Writes
A standard CREATE INDEX command locks out all writes (INSERT, UPDATE, DELETE) until the entire index is built across the dataset.
In PostgreSQL, you must always specify CONCURRENTLY:
-- Builds the index without blocking incoming reads or writes
CREATE INDEX CONCURRENTLY idx_accounts_email_verified
ON accounts(email_verified);-- Builds the index without blocking incoming reads or writes
CREATE INDEX CONCURRENTLY idx_accounts_email_verified
ON accounts(email_verified);The Catch with CONCURRENTLY:
CREATE INDEX CONCURRENTLYcannot run inside an explicit transaction block (BEGIN ... COMMIT). Most ORMs automatically wrap migrations in transactions, which causes this statement to crash. You must configure your migration runner to disable transaction wrapping for that migration.- If the index build fails (e.g., due to a duplicate key error or killed connection), PostgreSQL leaves an INVALID index behind.
An invalid index is never used by the query planner, but it still consumes disk space and continues to slow down every single write to that table!
Always check for invalid indexes after a failure:
SELECT relname, indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE indisvalid = false;
-- Clean up invalid indexes
DROP INDEX CONCURRENTLY idx_accounts_email_verified;SELECT relname, indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE indisvalid = false;
-- Clean up invalid indexes
DROP INDEX CONCURRENTLY idx_accounts_email_verified;The Master Pattern: The Expand/Contract Pattern
What happens when you need to rename a column, split a field, or change a data type from an integer to a UUID on a 50-million-row table?
You cannot simply run ALTER TABLE ... RENAME COLUMN.
Your deployment runs across dozens of rolling pods. If you rename the column in the database, old application pods running version 1.0 will immediately crash because they expect the old column name. If you deploy version 2.0 first, it crashes because the new column name does not exist yet.
To achieve zero downtime, you must use the Expand/Contract Pattern (Parallel Run) over four distinct releases.
Phase 1: EXPAND
Add the new column (keep old column). Deploy code that dual-writes to both.
Phase 2: BACKFILL
Run a background worker to copy historical data from old column to new column.
Phase 3: CONTRACT (Reads)
Deploy code that reads from the new column. Stop reading from the old column.
Phase 4: CONTRACT (Cleanup)
Stop writing to old column. Safely drop old column from the database.Phase 1: EXPAND
Add the new column (keep old column). Deploy code that dual-writes to both.
Phase 2: BACKFILL
Run a background worker to copy historical data from old column to new column.
Phase 3: CONTRACT (Reads)
Deploy code that reads from the new column. Stop reading from the old column.
Phase 4: CONTRACT (Cleanup)
Stop writing to old column. Safely drop old column from the database.Phase 1: Expand
Add the new column as nullable. Deploy an application update where all new INSERT and UPDATE operations write to both columns simultaneously:
// Application code dual-writes to both columns
async function updateEmail(userId: string, newEmail: string) {
await db.accounts.update({
where: { id: userId },
data: {
email: newEmail, // Old column
user_email: newEmail, // New column
},
});
}// Application code dual-writes to both columns
async function updateEmail(userId: string, newEmail: string) {
await db.accounts.update({
where: { id: userId },
data: {
email: newEmail, // Old column
user_email: newEmail, // New column
},
});
}Phase 2: Chunked Background Backfill
Now, copy historical data from the old column to the new column.
Never run an unconstrained batch update:
-- DISASTROUS: Locks millions of rows, blows up WAL logs, spikes replication lag
UPDATE accounts SET user_email = email WHERE user_email IS NULL;-- DISASTROUS: Locks millions of rows, blows up WAL logs, spikes replication lag
UPDATE accounts SET user_email = email WHERE user_email IS NULL;Instead, run your backfill in deterministic, keyset-paginated batches:
-- Safe: Updates 2,000 rows at a time by Primary Key
UPDATE accounts
SET user_email = email
WHERE id >= 1 AND id < 2000
AND user_email IS NULL;-- Safe: Updates 2,000 rows at a time by Primary Key
UPDATE accounts
SET user_email = email
WHERE id >= 1 AND id < 2000
AND user_email IS NULL;Sleep for 100 milliseconds between batches. This allows your replication stream to catch up and prevents connection starvation for live user requests.
Phase 3 & 4: Contract
- Switch application reads to target
user_email. Verify in monitoring that the old column receives zero read traffic. - Remove the dual-write logic from your application codebase.
- Drop the old column:
-- Set lock timeout first!
SET lock_timeout = '2s';
ALTER TABLE accounts DROP COLUMN email;-- Set lock timeout first!
SET lock_timeout = '2s';
ALTER TABLE accounts DROP COLUMN email;When Native Tools Aren't Enough: Ghost Tables
When tables grow past 100 million rows or run on MySQL engines with strict operational thresholds, even metadata locks can be risky.
At companies like GitHub and Shopify, large-scale migrations are handled using Online Schema Change (OSC) tools:
- gh-ost (GitHub Online Schema Transformations): Tails the MySQL binary log (binlog) to replicate live mutations while building a clone table in the background. It cuts over via an atomic rename without writing triggers.
- pt-online-schema-change (Percona Toolkit): Uses database triggers to capture live updates onto an alternate shadow table.
These tools build the entire altered table in parallel, copy data across in tiny increments, and swap the table names in a sub-millisecond atomic cutover.
The Zero-Downtime Migration Checklist
Before running any DDL statement in your production deployment pipeline, verify these five engineering rules:
- Always declare timeouts: Set
SET lock_timeout = '2s'andSET statement_timeout = '5s'before any DDL query. - Create indexes asynchronously: Always use
CREATE INDEX CONCURRENTLYin Postgres and verify that no invalid indexes remain after failures. - Avoid monolithic updates: Backfill historical data in batches of 1,000 to 5,000 rows based on primary keys with pause intervals to keep replica lag near zero.
- Use phased deployments for schema mutations: Never rename a column directly. Follow the Expand/Contract pattern across multiple independent deployments.
- Audit lock contention in staging: Replicate production data volumes in a staging environment to catch long-running exclusive locks before touching customer data.
Software architecture is not tested when everything is static; it is tested when systems must evolve under the continuous pressure of live production traffic.