Zero-Downtime Database Migrations: The Expand-and-Contract Pattern in High-Volume Production

1
+-----------------------------------------------------------------------------------+
2
|                  THE 5-PHASE EXPAND-AND-CONTRACT LIFECYCLE                        |
3
+-----------------------------------------------------------------------------------+
4
|                                                                                   |
5
|  Phase 1: EXPAND (Database)                                                       |
6
|  - Add new column/table without NOT NULL constraints or defaults.                |
7
|  - Old app continues reading and writing to old column.                           |
8
|                                                                                   |
9
|  Phase 2: DUAL-WRITE (Application Deployment v1)                                  |
10
|  - App writes to BOTH old and new columns. Reads strictly from old column.        |
11
|                                                                                   |
12
|  Phase 3: ASYNCHRONOUS BACKFILL (Background Worker)                               |
13
|  - Backfill historical rows in small, throttled cursor batches.                   |
14
|                                                                                   |
15
|  Phase 4: READ SHIFT (Application Deployment v2)                                  |
16
|  - App switches reads to the new column. Still writes to both for safety.         |
17
|                                                                                   |
18
|  Phase 5: CONTRACT (Cleanup)                                                      |
19
|  - Stop writing to old column (Deployment v3). Drop old column from database.     |
20
+-----------------------------------------------------------------------------------+

1
-- Safe migration preamble
2
SET lock_timeout = '2s';           -- If lock cannot be acquired within 2s, fail immediately
3
SET statement_timeout = '5s';      -- Prevent runaway table rewrites
4

5
-- If this fails due to lock timeout, your automated CI/CD pipeline catches it
6
-- and retries later without bringing down production traffic!
7
ALTER TABLE users ADD COLUMN phone_number VARCHAR(32);

1
-- STEP 1: Add column nullable (instant metadata operation)
2
ALTER TABLE users ADD COLUMN phone_number VARCHAR(32);
3

4
-- STEP 2: Add check constraint marked as NOT VALID (instant: does not scan table)
5
ALTER TABLE users ADD CONSTRAINT chk_phone_not_null
6
CHECK (phone_number IS NOT NULL) NOT VALID;
7

8
-- STEP 3: Validate constraint concurrently (scans rows with SHARE UPDATE EXCLUSIVE: reads/writes proceed!)
9
ALTER TABLE users VALIDATE CONSTRAINT chk_phone_not_null;

1
-- Concurrently scans the table twice, allowing live writes to proceed uninterrupted
2
CREATE INDEX CONCURRENTLY idx_users_phone_number
3
ON users (phone_number);

1
# Production zero-downtime backfill script using keyset cursor pagination
2
import time
3
import psycopg2
4

5
BATCH_SIZE = 2000
6
SLEEP_DELAY_SECONDS = 0.05
7

8
def backfill_users(cursor, connection):
9
    last_id = 0
10
    total_processed = 0
11

12
    while True:
13
        # Keyset pagination: O(1) index lookup, avoids slow OFFSET queries
14
        cursor.execute("""
15
            SELECT id, full_name
16
            FROM users
17
            WHERE id > %s
18
            ORDER BY id ASC
19
            LIMIT %s
20
        """, (last_id, BATCH_SIZE))
21

22
        rows = cursor.fetchall()
23
        if not rows:
24
            print("Backfill completed successfully.")
25
            break
26

27
        for user_id, full_name in rows:
28
            parts = (full_name or "").split(" ", 1)
29
            first_name = parts[0]
30
            last_name = parts[1] if len(parts) > 1 else ""
31

32
            cursor.execute("""
33
                UPDATE users
34
                SET first_name = %s, last_name = %s
35
                WHERE id = %s AND (first_name IS NULL OR last_name IS NULL)
36
            """, (first_name, last_name, user_id))
37

38
        connection.commit() # Commit transaction per batch
39
        last_id = rows[-1][0]
40
        total_processed += len(rows)
41
        print(f"Processed {total_processed} records. Current cursor: {last_id}")
42

43
        # Cooperative throttling: Give autovacuum and client traffic breathing room
44
        time.sleep(SLEEP_DELAY_SECONDS)

1
Rollback Safety Checklist:
2
+------------------------------------------------------------------------------------+
3
|  [Rule 1] Never delete a column in the same deployment that stops reading it.      |
4
|  [Rule 2] Never rename a column directly (always add new -> dual write -> drop).   |
5
|  [Rule 3] All new columns must be nullable or possess an immutable default value.  |
6
|  [Rule 4] Never remove code references until the migration is 100% contracted.     |
7
+------------------------------------------------------------------------------------+