NexusBlog

Introduction

Performing schema migrations on a PostgreSQL database with hundreds of millions of records and thousands of write transactions per second requires strict avoidance of blocking table locks.

The Dangerous Lock Queue

Even a simple ALTER TABLE ADD COLUMN ... DEFAULT ... or CREATE INDEX can acquire an ACCESS EXCLUSIVE lock. If a long-running read query is currently active, the migration query gets blocked in the lock queue—subsequently blocking all succeeding SELECT, INSERT, and UPDATE queries!

The Expand and Contract Pattern

To avoid locking tables and breaking running application instances during rolling deployments, we utilize the phased Expand-Contract (Parallel Run) strategy:

text

1234567stateDiagram-v2
    [*] --> Phase1_Expand: Add nullable column or index CONCURRENTLY
    Phase1_Expand --> Phase2_DualWrite: Deploy App v2 (Writes to both old and new columns)
    Phase2_DualWrite --> Phase3_Backfill: Asynchronous batch backfill historical rows
    Phase3_Backfill --> Phase4_Contract: Deploy App v3 (Reads only new column)
    Phase4_Contract --> Phase5_Cleanup: Drop deprecated old column asynchronously
    Phase5_Cleanup --> [*]

Safe vs Dangerous PostgreSQL DDL Commands

Lock Duration & Impact on 50M Row Table

Benchmarked Live

Comparison of traditional vs non-blocking migration patterns.

1.Standard CREATE INDEX

48s table lock

100% blocked queries

2.CREATE INDEX CONCURRENTLY

0ms table lock

Zero read/write blocking

3.Lock Timeout Guard

2000ms max

Instant abort on lock wait

Safe DDL Script Template

Always set a strict lock timeout and statement timeout before running DDL:

text

12345678910-- Set aggressive lock timeout to fail fast if table is busy
SET lock_timeout = '2s';
SET statement_timeout = '30s';

-- Step 1: Add new column without default
ALTER TABLE users ADD COLUMN phone_normalized VARCHAR(32);

-- Step 2: Create index concurrently outside transaction block
-- Note: CONCURRENTLY cannot run inside a multi-statement transaction
CREATE INDEX CONCURRENTLY idx_users_phone_normalized ON users(phone_normalized);

Zero-Downtime PostgreSQL Schema Migrations at Scale

System Design: From Zero to Production