The Dreaded AccessExclusiveLock

In PostgreSQL, seemingly benign operations like altering a column type from INTEGER to BIGINT or adding a foreign key constraint acquire an AccessExclusiveLock. This lock blocks all reads and writes on the table until the operation finishes.

On a table with 500 million rows, a table rewrite can take 45 minutes to several hours. For an enterprise SaaS platform, that level of downtime is unacceptable. Here is our battle-tested 4-phase Expand/Contract migration playbook.

Phase 1: Expand (Add Shadow Column & Dual-Writing Trigger)

Never modify an existing column in-place on high-velocity tables. Instead, add a new nullable shadow column with safe lock timeouts:

-- Set aggressive lock timeout to prevent queue pileup
SET lock_timeout = '2s';

-- Add new shadow column instantly (metadata-only operation)
ALTER TABLE user_ledger_entries ADD COLUMN balance_v2 NUMERIC(18, 4);

-- Attach lightweight row trigger to sync live writes
CREATE OR REPLACE FUNCTION sync_balance_v2()
RETURNS TRIGGER AS $$
BEGIN
    NEW.balance_v2 := NEW.balance::NUMERIC(18, 4);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_balance_v2
BEFORE INSERT OR UPDATE ON user_ledger_entries
FOR EACH ROW EXECUTE FUNCTION sync_balance_v2();

Phase 2: Historical Backfill in Controlled Batches

Backfill historical data in chunked keyset pagination transactions to avoid lock escalation and keep replication lag under 200ms:

-- Keyset batching script running in worker loop
UPDATE user_ledger_entries
SET balance_v2 = balance::NUMERIC(18, 4)
WHERE id >= 1000001 AND id < 1050000
  AND balance_v2 IS NULL;

Phase 3: Deploy Application Code Reading from New Column

Once backfill validation confirms 100% parity, deploy application updates that read and write directly to balance_v2.

Phase 4: Contract (Drop Trigger and Old Column)

Safely drop the legacy trigger and column after verifying stability across full peak traffic cycles.