Zero-Downtime Database Migrations: Our Rollback Strategy

The principle

Every migration should be reversible within one deploy cycle. That means backward-compatible schema changes in phase 1, cleanup in phase 2.

Phase 1: Additive changes

-- Safe: new column with a default
ALTER TABLE users ADD COLUMN display_name TEXT NOT NULL DEFAULT '';

-- Safe: new table
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    action TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Safe: new index (concurrently, no lock)
CREATE INDEX CONCURRENTLY idx_audit_user ON audit_log(user_id);

Phase 2 (next deploy): Destructive changes

-- Now safe: no code reads the old column
ALTER TABLE users DROP COLUMN old_name;

The golden rule

Never combine a destructive schema change with a code change that depends on it. Two deploys, minimum.

What I learned

The “expand-contract” pattern sounds obvious but is hard to enforce. We added a CI check that any migration with DROP or ALTER COLUMN must be at least 24 hours old before it runs.