karawaci.kode

2026-09-06 · 11 min

Database Migration Zero-Downtime Strategy in Production

Your migration script worked fine in staging. You run it in production at 2 AM. Forty seconds in, the orders table is locked, your application is throwing 500s, and your phone starts lighting up with Slack alerts from the team in Karawaci who just noticed the payment flow is broken. This is not a hypothetical.

Zero-downtime migration is not about running ALTER TABLE at night. It’s an architectural discipline that starts before you write the first line of SQL.

Why Most “Safe” Migrations Still Cause Downtime

The most common mistake is treating a database migration as a single atomic event. You run a migration file, the schema changes, the application picks it up. That works in development. In production, it fails because:

  • Your application has multiple instances running simultaneously. During a deployment, you have old code and new code talking to the same database at the same time.
  • DDL operations in PostgreSQL acquire locks. An ALTER TABLE ... ADD COLUMN NOT NULL without a default acquires AccessExclusiveLock, blocking every read and write on that table until it completes.
  • Table rewrites on large tables can take minutes. A table with 50 million rows representing transactions from merchants across Tangerang Selatan, BSD, and Serpong does not rewrite in seconds.

The fundamental insight is this: a migration must be compatible with both the current and the next version of your application. If it isn’t, you need a deployment window. If it is, you don’t.

The Expand-Contract Pattern

Expand-contract splits every breaking schema change into three phases, each deployable independently with no coordination required between phases.

Phase 1 — Expand: Add new schema elements without removing anything. The old code keeps working because nothing it depends on has changed.

Phase 2 — Migrate: Update application logic to read from the new structure and write to both old and new. Backfill existing data. Both versions of the application now work correctly.

Phase 3 — Contract: Remove the old schema elements. At this point, no running application code references them.

Here is what this looks like concretely. You need to split a full_name column into first_name and last_name.

Phase 1: Expand

-- Migration 001: add new columns, keep full_name
ALTER TABLE users
  ADD COLUMN first_name TEXT,
  ADD COLUMN last_name TEXT;

This is instant in PostgreSQL. No lock contention, no table rewrite. Deploy this migration and the old application version still works — it reads and writes full_name and ignores the new columns.

Phase 2: Migrate

Deploy new application code that:

  1. Writes to first_name and last_name on every insert/update.
  2. Reads from first_name/last_name with a fallback to full_name for rows not yet backfilled.

Then run the backfill as a script, not as a single UPDATE:

# backfill_names.py — run as a one-off job, not inside migration runner
import psycopg2
import time

BATCH_SIZE = 5000
SLEEP_BETWEEN_BATCHES = 0.1  # seconds

conn = psycopg2.connect(DATABASE_URL)
cur = conn.cursor()

while True:
    cur.execute("""
        WITH batch AS (
            SELECT id FROM users
            WHERE first_name IS NULL
              AND full_name IS NOT NULL
            LIMIT %s
            FOR UPDATE SKIP LOCKED
        )
        UPDATE users u
        SET
            first_name = split_part(full_name, ' ', 1),
            last_name   = CASE
                            WHEN position(' ' IN full_name) > 0
                            THEN substring(full_name FROM position(' ' IN full_name) + 1)
                            ELSE ''
                          END
        FROM batch
        WHERE u.id = batch.id
        RETURNING u.id
    """, (BATCH_SIZE,))

    updated = cur.rowcount
    conn.commit()

    if updated == 0:
        break

    time.sleep(SLEEP_BETWEEN_BATCHES)

cur.close()
conn.close()

SKIP LOCKED is critical here. It allows concurrent application writes to proceed without waiting for your backfill batch to release row locks. Without it, your backfill becomes a lock-contention event.

Phase 3: Contract

Only after Phase 2 is fully deployed and the backfill is complete:

-- Migration 002: remove full_name (deployed separately, weeks later)
ALTER TABLE users DROP COLUMN full_name;

Drop the fallback logic from the application in the same deployment. Now the migration is done and the schema is clean.

Adding a NOT NULL Column on a Large Table

This is the operation that trips up most teams. PostgreSQL pre-11 required a full table rewrite for ADD COLUMN ... NOT NULL DEFAULT. PostgreSQL 11+ made constant defaults metadata-only, which is instant. But many teams still hit problems by combining a default with a NOT NULL constraint incorrectly, or by running on slightly older versions.

The safe four-step sequence:

-- Step 1: Add column as nullable with a default (instant in PG11+)
ALTER TABLE orders ADD COLUMN payment_provider TEXT DEFAULT 'midtrans';

-- Step 2: Add constraint as NOT VALID (skips existing rows — no table scan)
ALTER TABLE orders
  ADD CONSTRAINT orders_payment_provider_not_null
  CHECK (payment_provider IS NOT NULL) NOT VALID;

-- Step 3: Run backfill script for any existing NULLs (if needed)
-- (same batched pattern as above)

-- Step 4: Validate constraint (ShareUpdateExclusiveLock, not AccessExclusiveLock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_payment_provider_not_null;

-- Step 5: In PG12+, this is now instant because a validated CHECK exists
ALTER TABLE orders ALTER COLUMN payment_provider SET NOT NULL;

The VALIDATE CONSTRAINT step takes a ShareUpdateExclusiveLock. That lock is compatible with concurrent reads and writes — it only blocks other DDL operations on the table. On a high-traffic table, this is the difference between zero user impact and a 503 cascade.

Building Indexes Without Locking

Creating an index on a large table blocks writes unless you use CONCURRENTLY:

-- This blocks writes for the entire build duration — never do this in production
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- This allows concurrent reads and writes during the build
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

CREATE INDEX CONCURRENTLY takes longer (it scans the table multiple times to handle concurrent writes) but does not block DML. One catch: if it fails midway, it leaves an invalid index that you must drop manually before retrying:

-- Check for invalid indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

-- Drop and retry
DROP INDEX CONCURRENTLY idx_orders_user_id;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

Add this check to your post-deployment validation script. An invalid index has the same query-blocking behavior as the full lock, so leaving it in place is worse than having no index.

Static Analysis in CI: Catch Dangerous Migrations Before They Run

squawk is a PostgreSQL migration linter that integrates into CI. It flags operations known to cause lock issues or data loss risks.

npm install -g squawk-cli
# .github/workflows/migration-check.yml
name: Migration Safety Check

on:
  pull_request:
    paths:
      - 'migrations/**'

jobs:
  squawk:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Run squawk on changed migrations
        run: |
          git diff --name-only origin/main...HEAD -- migrations/ | \
          xargs squawk --exclude=prefer-text-field

Rules squawk enforces by default include:

  • add-column-with-volatile-default — catches the pre-PG11 table rewrite trigger
  • require-concurrent-index-creation — enforces CONCURRENTLY on all CREATE INDEX
  • ban-drop-not-null — flags SET NOT NULL without a prior validated CHECK constraint
  • constraint-missing-not-valid — requires NOT VALID on large-table constraint additions

You can configure exceptions per rule. The --exclude=prefer-text-field in the example above disables the opinion about using TEXT vs VARCHAR(n) — reasonable to suppress on codebases with existing conventions.

Migration Runner Configuration That Matches Reality

Most teams use Flyway, Liquibase, or a framework-native runner like Alembic. The configuration that matters for production:

# flyway.toml (production profile)
[environments.production]
url = "${DATABASE_URL}"
lockRetryCount = 10
connectRetries = 5
outOfOrder = false          # never allow — strict ordering only
validateOnMigrate = true    # checksum validation on every run
baselineOnMigrate = false   # never auto-baseline in production
mixed = false               # disallow DDL+DML in same transaction

mixed = false is the non-obvious one. PostgreSQL wraps DDL in transactions, which is great — if a migration fails, it rolls back cleanly. But some DDL (like CREATE INDEX CONCURRENTLY) cannot run inside a transaction. Mixing transactional and non-transactional DDL in one migration file creates undefined rollback behavior. Split them: transactional DDL in one file, CONCURRENTLY operations in a separate file marked as non-transactional.

In Flyway, mark a file non-transactional by prefixing with B_ (baseline) or using disableChecksum. In Alembic, set transaction_per_migration = false at the op level.

Testing Migrations Against Production-Scale Data

The most common staging trap: your staging database has 50,000 rows, production has 80 million. The migration runs in 3 seconds on staging and times out in production.

Before running any migration that touches a table above 1 million rows:

  1. Restore a pg_dump snapshot of the production table (anonymized if it contains PII) to a dedicated test instance.
  2. Run EXPLAIN (ANALYZE, BUFFERS) on the migration’s equivalent query to see actual lock wait times and row estimates.
  3. Run the migration against this instance with statement_timeout = '30s' set — if it exceeds 30 seconds, it will likely cause timeouts on production under real load.
-- Test migration impact under simulated load
SET statement_timeout = '30s';
SET lock_timeout = '5s';

BEGIN;
-- your migration here
ROLLBACK; -- or COMMIT after review

lock_timeout is the one most teams forget to set. A migration that is waiting for a lock does not look like a problem until 300 application threads are queued behind it.

The Honest Trade-off

Expand-contract makes schema evolution safe, but it costs you speed and simplicity. A single-step migration that would take 10 minutes of planning becomes a three-PR process that spans weeks. Teams under delivery pressure will skip phases, especially Phase 3 (contract), because “we’ll clean it up later.”

Phase 3 rarely happens without external forcing. The way to enforce it: create a tracking issue at the time you merge Phase 2, and put the Phase 3 migration into the same PR as the issue link, in a pending-contract/ directory. Make the existence of files in that directory a CI check that fails after 30 days. Engineers feel the deadline; it gets done.

Zero-downtime migration is not a single technique. It is a set of habits: think in phases, validate at scale, lint in CI, and treat the database schema as a contract that two versions of your application must honor simultaneously. The alternative is 2 AM alerts.

Ditulis oleh Reza Pradipta