1. The Expand and Contract Pattern
Traditional database migrations involve locking tables or taking the application offline to rename columns or alter types. The 'Expand and Contract' pattern achieves zero-downtime by breaking changes into 4 discrete, backward-compatible deployments: Expand (add new schema), Dual-Write (write to both), Migrate (backfill legacy data), and Contract (drop old schema).
-- 1. EXPAND: Add new column without enforcing NOT NULL
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- 2. MIGRATE: Backfill data in small batches to prevent table locks
UPDATE users SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL LIMIT 1000;2. Dealing with Replication Lag
When executing mass data backfills on high-availability PostgreSQL clusters, read-replicas will inevitably fall behind the primary write node. Applications must be engineered to selectively route read queries to the primary node for immediately-modified records to prevent users from seeing stale data during the replication window.
3. Live Cut-over Strategies and PgBouncer
For operations requiring exclusive locks (like dropping legacy tables during the final 'Contract' phase), connection poolers like PgBouncer are critical. By executing a brief `PAUSE` command in PgBouncer, incoming application requests are queued in memory rather than rejected, allowing the schema swap to occur seamlessly behind the scenes.