Zero-Downtime Database Migrations in Distributed PostgreSQL

The Danger of Naive Database Migrations

In high-concurrency PostgreSQL deployments, running standard DDL migrations such as ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '0000' on a table with 20 million rows will acquire an ACCESS EXCLUSIVE lock.

While the table rewrite executes, every single read and write query against the users table is queued behind the lock. Within seconds, database connection pools are saturated, leading to catastrophic full-site outages.

In this technical breakdown, we look at the Expand-Contract (Parallel Run) Pattern for completely risk-free database migrations.

The Four Golden Rules of Postgres Migrations

1.Always Set a Short Lock Timeout: Ensure that if a lock cannot be acquired within 1.5 seconds, the migration aborts rather than backing up incoming traffic:

```sql

SET lock_timeout = '1500ms';

```

2.Never Add Indexes Synchronously: Always utilize CREATE INDEX CONCURRENTLY.

3.Never Add Columns with Volatile Defaults: In modern Postgres (v11+), adding a column with a constant default is instantaneous metadata-only, but adding volatile functions will trigger full rewrites.

4.Decouple Code Deployments from Database Schema Changes: Never expect the new code and new schema to deploy at the exact same microsecond.

The Expand-Contract Workflow

Suppose you need to rename a column or split user names into first_name and last_name:

Phase 1: Expand

Add the new columns as nullable without modifying existing columns. Deploy code that dual-writes to both the old and new columns, but reads from the old column.

sql

ALTER TABLE accounts ADD COLUMN given_name VARCHAR(100);
ALTER TABLE accounts ADD COLUMN family_name VARCHAR(100);

Phase 2: Backfill

Run a background batch script backfilling older historical rows in chunks of 5,000 to prevent long-lived transactions:

sql

UPDATE accounts
SET given_name = split_part(full_name, ' ', 1),
    family_name = split_part(full_name, ' ', 2)
WHERE id >= $1 AND id < $2 AND given_name IS NULL;

Phase 3: Contract (Switch Reads)

Deploy a new code release that reads from given_name and family_name. Dual-writing can now be disabled.

Phase 4: Clean Up

Drop the deprecated full_name column safely during an off-peak maintenance window.

Conclusion

Resilient engineering means treating database schema migrations as gradual transitions rather than abrupt atomic updates. With lock timeouts and expand-contract migrations, you can deploy daily with zero downtime.