Zero-Downtime Database Migrations for High-Traffic Applications
How to execute schema changes, column renames, and table partitions on live PostgreSQL and MySQL clusters without table locks, downtime, or connection spikes.
By Robin Singh · Published · Last reviewed · 5 min read
Executing a database migration in a high-traffic production system is one of the most hazardous operations an engineering team can run. A routine ALTER TABLE ADD COLUMN or an index creation executed naively on a table with 25 million rows can trigger an exclusive table lock (ACCESS EXCLUSIVE in PostgreSQL or metadata locks in MySQL). Within three seconds, incoming web requests saturate connection pools, thread starvation propagates upstream to API gateways, and your monitoring dashboard explodes with HTTP 504 Gateway Timeouts.
When we audit client infrastructure at Palmate Solutions, the mandate is non-negotiable: zero downtime, zero connection spikes, and rollback safety across every deployment.
This guide details the architectural patterns, locking mechanics, dual-writing techniques, and automated safety scripts required to run migrations on active transactional databases without interrupting customer operations.
1. The Locking Mechanics That Take Systems Down
Relational databases guarantee ACID properties through locking hierarchies. To change a schema safely, you must understand how different lock levels behave.
In PostgreSQL, most ALTER TABLE statements acquire an ACCESS EXCLUSIVE lock. This lock conflicts with every other lock mode, including simple SELECT queries (ACCESS SHARE). Even if an ALTER TABLE takes only 40 milliseconds to execute, it must wait in line behind every currently executing read query on that table. While it waits in the queue, every subsequent query behind it is blocked, rapidly exhausting backend connection pools (e.g., PgBouncer or RDS Proxy).
[ Active Queries ] ----> [ Running SELECT on orders (duration: 3s) ]
|
[ Migration Query ] ---> [ WAITING: ALTER TABLE orders ADD COLUMN ... ] (Blocks everything behind)
|
[ New Requests ] ------> [ BLOCKED: SELECT * FROM orders ... ] (Connection pool saturates)
The Three Golden Rules of Safe DDL
- Never run DDL without setting explicit lock and statement timeouts:
If a migration cannot acquire its required lock within 3 seconds, it must abort immediately rather than queuing incoming read traffic.SQL
SET lock_timeout = '3s'; SET statement_timeout = '15s'; - Never build indexes synchronously on production tables:
Always use
CREATE INDEX CONCURRENTLY(Postgres) orALGORITHM=INPLACE, LOCK=NONE(MySQL InnoDB). - Never add columns with non-null constraints and non-constant defaults in a single step:
In modern Postgres (v11+), adding a column with a constant
DEFAULTis metadata-only. However, adding volatile defaults (e.g.,DEFAULT gen_random_uuid()) or executing older schema engines forces a complete table rewrite.
2. The Expand and Contract Pattern
The core architectural pattern for zero-downtime database evolution is the Expand and Contract (or Parallel Run) pattern. Instead of modifying an active column or table in-place, the migration is split across three distinct software releases.
+-------------------------------------------------------------------------------+
| PHASE 1: EXPAND |
| Deploy DDL adding new nullable column / shadow table. |
| Deploy app code writing to BOTH old and new, reading from old. |
+-------------------------------------------------------------------------------+
|
v
+-------------------------------------------------------------------------------+
| PHASE 2: BACKFILL & SWITCH |
| Background batch worker backfills historical data into new column. |
| Deploy app code reading from new column, writing to both. |
+-------------------------------------------------------------------------------+
|
v
+-------------------------------------------------------------------------------+
| PHASE 3: CONTRACT |
| Deploy app code writing ONLY to new column. |
| Deploy DDL dropping old column or triggering cleanup. |
+-------------------------------------------------------------------------------+
Concrete Example: Renaming or Splitting a Column
Suppose your e-commerce platform needs to change users.full_name into users.first_name and users.last_name.
Step 1: Expand
Add the new columns as nullable fields using a strict lock timeout:
-- Step 1 DDL
SET lock_timeout = '2s';
ALTER TABLE users ADD COLUMN first_name VARCHAR(100);
ALTER TABLE users ADD COLUMN last_name VARCHAR(100);
Update your application logic (e.g., Prisma, TypeORM, or raw SQL queries) to implement dual-writing:
// Dual-writing logic during Phase 1
async function updateUserData(userId: string, fullName: string) {
const parts = fullName.trim().split(" ");
const firstName = parts[0] || "";
const lastName = parts.slice(1).join(" ") || "";
await db.query(
`UPDATE users
SET full_name = $1,
first_name = $2,
last_name = $3,
updated_at = NOW()
WHERE id = $4`,
[fullName, firstName, lastName, userId]
);
}
Step 2: Backfill Safely in Micro-Batches
Never execute a single UPDATE users SET first_name = ... across millions of rows. That causes table bloat, locks rows against concurrent checkout transactions, and generates gigabytes of WAL logs.
Instead, backfill in controlled chunks with sleep pauses to allow database vacuums and replicas to stay in sync:
-- Safe micro-batch backfilling script in SQL
DO $$
DECLARE
v_batch_size INT := 2000;
v_rows_updated INT;
v_last_id BIGINT := 0;
BEGIN
LOOP
WITH batch AS (
SELECT id, full_name
FROM users
WHERE id > v_last_id
AND first_name IS NULL
AND full_name IS NOT NULL
ORDER BY id ASC
LIMIT v_batch_size
)
UPDATE users u
SET
first_name = SPLIT_PART(b.full_name, ' ', 1),
last_name = NULLIF(SUBSTRING(b.full_name FROM POSITION(' ' IN b.full_name) + 1), '')
FROM batch b
WHERE u.id = b.id;
GET DIAGNOSTICS v_rows_updated = ROW_COUNT;
EXIT WHEN v_rows_updated = 0;
SELECT MAX(id) INTO v_last_id FROM (
SELECT id FROM users WHERE id > v_last_id ORDER BY id ASC LIMIT v_batch_size
) sub;
-- Yield execution to prevent replica lag
PERFORM pg_sleep(0.05);
END LOOP;
END $$;
Step 3: Switch Reads and Verify
Switch application read paths to query first_name and last_name. Keep writing to both columns for at least one complete billing or traffic cycle to guarantee fast rollback capability.
Step 4: Contract
Once telemetry confirms zero read traffic touches full_name, deprecate and drop the column safely:
SET lock_timeout = '2s';
ALTER TABLE users DROP COLUMN full_name;
3. Creating Indexes Concurrently Without Deadlocks
Building an index on a table containing tens of millions of rows with standard CREATE INDEX acquires a SHARE lock. While SHARE allows reads, it blocks all concurrent writes (INSERT, UPDATE, DELETE).
PostgreSQL provides CREATE INDEX CONCURRENTLY, which performs two table scans without blocking writes. However, concurrent index creation cannot run inside a transaction block. If it fails mid-execution (for instance, due to a unique constraint violation), it leaves an INVALID index behind that consumes disk space and degrades write performance.
// Migration runner handling CONCURRENT index creation and failure cleanup
import { Pool } from "pg";
export async function createIndexSafely(
pool: Pool,
indexName: string,
tableName: string,
columns: string
): Promise<void> {
const client = await pool.connect();
try {
console.log(`[Migration] Starting concurrent index creation: ${indexName}`);
// Check if index already exists and is invalid
const checkQuery = `
SELECT indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = $1;
`;
const res = await client.query(checkQuery, [indexName]);
if (res.rows.length > 0) {
if (res.rows[0].indisvalid === false) {
console.warn(`[Migration] Found invalid index ${indexName}. Dropping concurrently...`);
await client.query(`DROP INDEX CONCURRENTLY IF EXISTS ${indexName};`);
} else {
console.log(`[Migration] Index ${indexName} already exists and is valid. Skipping.`);
return;
}
}
// Set lock timeout to avoid queuing behind long transactions
await client.query(`SET statement_timeout = '0';`); // Let index build complete
await client.query(`SET lock_timeout = '5s';`);
const createSql = `CREATE INDEX CONCURRENTLY IF NOT EXISTS ${indexName} ON ${tableName} (${columns});`;
await client.query(createSql);
console.log(`[Migration] Successfully built index ${indexName}.`);
} catch (error) {
console.error(`[Migration Error] Failed to create index ${indexName}:`, error);
// Cleanup invalid artifact
await client.query(`DROP INDEX CONCURRENTLY IF EXISTS ${indexName};`);
throw error;
} finally {
client.release();
}
}
4. Handling Foreign Key Constraints
Adding a foreign key constraint naively runs a full table scan while holding an exclusive lock to validate that all existing child rows reference valid parent keys.
On high-write databases, split foreign key creation into two separate stages:
-- STAGE 1: Add the constraint without validating existing rows (takes <10ms)
SET lock_timeout = '2s';
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_product_id
FOREIGN KEY (product_id)
REFERENCES products (id)
NOT VALID;
-- STAGE 2: Validate existing data concurrently without blocking writes
ALTER TABLE order_items
VALIDATE CONSTRAINT fk_order_items_product_id;
Stage 1 immediately enforces referential integrity on all new writes. Stage 2 validates historical rows using a SHARE UPDATE EXCLUSIVE lock, which allows concurrent reads and writes to proceed unhindered.
5. Architectural Comparison: In-Place vs Expand-Contract
| Metric / Scenario | Standard In-Place DDL | Expand & Contract Pattern |
|---|---|---|
| Lock Level Required | ACCESS EXCLUSIVE (Full read/write block) | Short ACCESS EXCLUSIVE (<50ms) for nullable column |
| Rollback Strategy | Restore full DB backup / reverse DDL | Feature-flag rollback in application layer |
| Connection Pool Impact | High risk of pool exhaustion & 504 cascades | Minimal (bounded by strict 2s lock timeout) |
| Replication Lag | High (massive WAL stream spikes) | Flat and controlled via throttled micro-batches |
| Engineering Overhead | Single script, high operational risk | Multi-step deployments, automated verification |
6. Pre-Flight Production Checklist
Before applying any migration to high-traffic production databases, verify each requirement:
- Lock timeout defined: Script explicitly executes
SET lock_timeout = '2s';before every DDL statement. - No large table rewrites: Adding columns verifies default values are constant and nullable.
- Index builds are non-blocking: All indexes use
CONCURRENTLY(Postgres) orLOCK=NONE(MySQL). - Foreign keys split: Constraints applied with
NOT VALIDfollowed by asynchronousVALIDATE CONSTRAINT. - Replica lag monitoring active: Background backfill scripts monitor read replica delay before running next batches.
- Dry-run completed on staging clone: Migration executed against an RDS snapshot of production data to verify execution plans.
Authoritative References & Standards
- PostgreSQL Official Documentation: Explicit Locking Mechanics & Table Lock Modes
- IETF RFC 7231: Hypertext Transfer Protocol (HTTP/1.1): Semantics and Content (Idempotency and Timeout Recovery)
- Martin Fowler: Evolutionary Database Design and Parallel Run Patterns
- AWS Architecture Center: Amazon Aurora & RDS Best Practices for Minimizing Downtime During DDL
