Skip to content
Palmate Solutions

Business Systems

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

Share Architecture Note
PostLinkedIn

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).

CODE
[ 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

  1. Never run DDL without setting explicit lock and statement timeouts:
    SQL
    SET lock_timeout = '3s'; SET statement_timeout = '15s';
    If a migration cannot acquire its required lock within 3 seconds, it must abort immediately rather than queuing incoming read traffic.
  2. Never build indexes synchronously on production tables: Always use CREATE INDEX CONCURRENTLY (Postgres) or ALGORITHM=INPLACE, LOCK=NONE (MySQL InnoDB).
  3. 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 DEFAULT is 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.

CODE
+-------------------------------------------------------------------------------+ | 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:

SQL
-- 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:

TYPESCRIPT
// 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:

SQL
-- 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:

SQL
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.

TYPESCRIPT
// 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:

SQL
-- 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 / ScenarioStandard In-Place DDLExpand & Contract Pattern
Lock Level RequiredACCESS EXCLUSIVE (Full read/write block)Short ACCESS EXCLUSIVE (<50ms) for nullable column
Rollback StrategyRestore full DB backup / reverse DDLFeature-flag rollback in application layer
Connection Pool ImpactHigh risk of pool exhaustion & 504 cascadesMinimal (bounded by strict 2s lock timeout)
Replication LagHigh (massive WAL stream spikes)Flat and controlled via throttled micro-batches
Engineering OverheadSingle script, high operational riskMulti-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) or LOCK=NONE (MySQL).
  • Foreign keys split: Constraints applied with NOT VALID followed by asynchronous VALIDATE 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

  1. PostgreSQL Official Documentation: Explicit Locking Mechanics & Table Lock Modes
  2. IETF RFC 7231: Hypertext Transfer Protocol (HTTP/1.1): Semantics and Content (Idempotency and Timeout Recovery)
  3. Martin Fowler: Evolutionary Database Design and Parallel Run Patterns
  4. AWS Architecture Center: Amazon Aurora & RDS Best Practices for Minimizing Downtime During DDL
Share Architecture Note
PostLinkedIn