Designing Zero-Downtime Database Migrations in Distributed Production Environments

Every ALTER TABLE Is a Potential Outage — Treat It Like One

Most migration incidents do not begin with a broken SQL statement. They begin with an assumption: that the database and every running service instance flip from the old schema shape to the new one at the same instant. In a distributed production environment that instant does not exist. During any rolling deployment you have at least three versions of your application alive at once — the version draining connections, the steady-state version, and the canary — all issuing queries against one shared schema.

A migration is therefore not a database operation. It is a distributed systems protocol between your schema and a fleet of independently-timed processes. Design it with the same rigor you apply to a public API contract change, because that is exactly what it is.

The N-1 Compatibility Invariant

State the rule plainly and enforce it in code review:

Every schema state must be readable and writable by at least two consecutive application versions (N and N-1).

This single invariant eliminates the overwhelming majority of downtime-causing migrations. It forbids RENAME COLUMN, DROP COLUMN, ALTER TYPE, and SET NOT NULL in the same deploy that introduces the new shape. It forces you to decompose every change into a sequence of individually safe states.

Why the Naive Approach Breaks Under Rolling Deploys

Consider a three-replica deployment where you add orders.fulfillment_state, deploy code that writes it, and immediately drop orders.status:

1. Pod A (new code) commits fulfillment_state = 'shipped' and stops writing status.
2. Pod B (old code, still draining) serves a request and reads status — it is NULL. It returns corrupt data or panics.
3. The migration runner executes DROP COLUMN status. Pod B now fails with column "status" does not exist.
4. Your error budget is gone before the rollout completes.

Nothing in that sequence was a database problem. It was an ordering problem.

The Expand–Contract Pattern (Parallel Change)

Expand–Contract — also called parallel change — is the canonical solution. You never transition between two incompatible states; you transition through a superset state that both versions understand.

The critical discipline is temporal: each phase ships in a separate deployment, separated by enough time for every N-1 process to terminate and every replica to catch up.

Classifying Schema Changes by Risk

• Additive (safe): new nullable column, new table, new index built concurrently, new constraint with NOT VALID.
• Transformative (requires backfill): splitting or merging columns, changing units or encodings, moving data between tables.
• Destructive (defer to Contract): dropping columns, dropping tables, narrowing types.
• Restrictive (defer to Contract): SET NOT NULL, adding a validated CHECK, adding UNIQUE, adding a foreign key on a hot table.

Only additive changes may ride along with an application deploy. Everything else gets its own scheduled window inside the protocol.

Hands-On: Adding a Required Column Without Downtime

We will migrate orders.status (NEW / SCHEDULED / DONE / VOID) to a typed orders.fulfillment_state column on PostgreSQL, with zero dropped requests.

Step 1 — Expand: Add a Nullable Column and an Unvalidated Constraint

-- Always bound DDL waits. An unbounded ALTER TABLE queues every query behind it.
SET lock_timeout = '3s';
SET statement_timeout = '30s';

ALTER TABLE orders
  ADD COLUMN IF NOT EXISTS fulfillment_state text;

-- NOT VALID takes only SHARE UPDATE EXCLUSIVE and does not scan the table,
-- so concurrent reads and writes continue uninterrupted.
ALTER TABLE orders
  ADD CONSTRAINT orders_fulfillment_state_chk
  CHECK (
    fulfillment_state IS NULL
    OR fulfillment_state IN ('pending', 'scheduled', 'shipped', 'cancelled')
  ) NOT VALID;

Source: PostgreSQL `ALTER TABLE`.

Step 2 — Dual Write and Dual Read in Application Code

During the Migrate window, the new code writes both columns and reads the new column with a fallback to the legacy one. This is what makes partial backfill safe.

package orders

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"
)

type FulfillmentState string

const (
	StatePending   FulfillmentState = "pending"
	StateScheduled FulfillmentState = "scheduled"
	StateShipped   FulfillmentState = "shipped"
	StateCancelled FulfillmentState = "cancelled"
)

var (
	ErrUnknownState = errors.New("orders: unknown fulfillment state")
	ErrNoState      = errors.New("orders: no fulfillment state present")
)

type Repository struct {
	db *sql.DB
	// dualWrite stays true until the contract migration has been fully rolled out.
	dualWrite bool
}

func NewRepository(db *sql.DB, dualWrite bool) *Repository {
	return &Repository{db: db, dualWrite: dualWrite}
}

// UpdateFulfillmentState writes the canonical column and, while any N-1
// process may still be alive, mirrors the value into the legacy column.
func (r *Repository) UpdateFulfillmentState(
	ctx context.Context, orderID int64, state FulfillmentState,
) error {
	legacy, err := legacyStatusFor(state)
	if err != nil {
		return err
	}

	ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()

	tx, err := r.db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}
	defer func() { _ = tx.Rollback() }()

	if r.dualWrite {
		_, err = tx.ExecContext(ctx,
			`UPDATE orders SET fulfillment_state = $1, status = $2 WHERE id = $3`,
			string(state), legacy, orderID)
	} else {
		_, err = tx.ExecContext(ctx,
			`UPDATE orders SET fulfillment_state = $1 WHERE id = $2`,
			string(state), orderID)
	}
	if err != nil {
		return fmt.Errorf("update order %d: %w", orderID, err)
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit order %d: %w", orderID, err)
	}
	return nil
}

func legacyStatusFor(s FulfillmentState) (string, error) {
	switch s {
	case StatePending:
		return "NEW", nil
	case StateScheduled:
		return "SCHEDULED", nil
	case StateShipped:
		return "DONE", nil
	case StateCancelled:
		return "VOID", nil
	default:
		return "", fmt.Errorf("%w: %q", ErrUnknownState, s)
	}
}

// Order carries both shapes so partially-backfilled rows remain readable.
type Order struct {
	ID               int64
	FulfillmentState sql.NullString // new column
	LegacyStatus     sql.NullString // deprecated column
}

// EffectiveState prefers the canonical column and falls back to the legacy one.
func (o *Order) EffectiveState() (FulfillmentState, error) {
	if o.FulfillmentState.Valid && o.FulfillmentState.String != "" {
		return FulfillmentState(o.FulfillmentState.String), nil
	}
	if !o.LegacyStatus.Valid {
		return "", fmt.Errorf("%w: order %d", ErrNoState, o.ID)
	}
	return stateFromLegacy(o.LegacyStatus.String)
}

func stateFromLegacy(s string) (FulfillmentState, error) {
	switch s {
	case "NEW":
		return StatePending, nil
	case "SCHEDULED":
		return StateScheduled, nil
	case "DONE":
		return StateShipped, nil
	case "VOID":
		return StateCancelled, nil
	default:
		return "", fmt.Errorf("%w: legacy %q", ErrUnknownState, s)
	}
}

Step 3 — Backfill in Throttled, Resumable Batches

The backfill is the highest-risk operation in the entire protocol. It must be bounded, resumable, and lag-aware. Never run UPDATE orders SET ... across an unbatched table.

const lockNotAvailable = "55P03"

type BackfillConfig struct {
	BatchSize     int
	Pause         time.Duration
	MaxLagSeconds float64
}

func (c BackfillConfig) validate() error {
	if c.BatchSize <= 0 {
		return errors.New("backfill: BatchSize must be > 0")
	}
	if c.MaxLagSeconds <= 0 {
		return errors.New("backfill: MaxLagSeconds must be > 0")
	}
	return nil
}

// BackfillFulfillmentState walks the table in keyset order, pausing whenever
// replica lag exceeds the configured budget. It is idempotent: re-running it
// simply resumes where the previous run stopped.
func BackfillFulfillmentState(
	ctx context.Context, primary, replica *sql.DB, cfg BackfillConfig,
) error {
	if err := cfg.validate(); err != nil {
		return err
	}
	for {
		if err := ctx.Err(); err != nil {
			return nil // cancelled cleanly; the last committed batch is the checkpoint
		}

		lag, err := replicationLagSeconds(ctx, replica)
		if err != nil {
			return err
		}
		if lag > cfg.MaxLagSeconds {
			time.Sleep(2 * time.Second)
			continue
		}

		updated, err := backfillBatch(ctx, primary, cfg.BatchSize)
		if err != nil {
			return err
		}
		if updated == 0 {
			return nil // table drained
		}
		time.Sleep(cfg.Pause)
	}
}

func replicationLagSeconds(ctx context.Context, replica *sql.DB) (float64, error) {
	var lag sql.NullFloat64
	err := replica.QueryRowContext(ctx, `
		SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float8`,
	).Scan(&lag)
	if err != nil {
		return 0, fmt.Errorf("query replication lag: %w", err)
	}
	if !lag.Valid {
		return 0, nil // replica has not replayed any transaction yet
	}
	return lag.Float64, nil
}

func backfillBatch(ctx context.Context, db *sql.DB, limit int) (int, error) {
	res, err := db.ExecContext(ctx, `
		WITH batch AS (
			SELECT id FROM orders
			WHERE fulfillment_state IS NULL
			ORDER BY id
			LIMIT $1
			FOR UPDATE SKIP LOCKED
		)
		UPDATE orders o
		SET fulfillment_state = CASE o.status
			WHEN 'NEW'       THEN 'pending'
			WHEN 'SCHEDULED' THEN 'scheduled'
			WHEN 'DONE'      THEN 'shipped'
			WHEN 'VOID'      THEN 'cancelled'
		END
		FROM batch
		WHERE o.id = batch.id`, limit)
	if err != nil {
		return 0, fmt.Errorf("backfill batch: %w", err)
	}
	n, err := res.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("rows affected: %w", err)
	}
	return int(n), nil
}

Every iteration produces WAL, which is replayed on every read replica. Replication lag, not primary CPU, is the real constraint on backfill throughput. A backfill that drives a replica ten minutes behind violates read-your-writes for every user routed to it.

Step 4 — Contract: Enforce, Then Drop

Once the backfill is complete and every process speaks the new shape, tighten the schema. On PostgreSQL 12+, SET NOT NULL skips the full table scan if a validated CHECK (col IS NOT NULL) already exists.

SET lock_timeout = '3s';

-- 1. Add an unvalidated NOT NULL guard (no table scan).
ALTER TABLE orders
  ADD CONSTRAINT orders_fulfillment_state_nn
  CHECK (fulfillment_state IS NOT NULL) NOT VALID;

-- 2. Validate it. SHARE UPDATE EXCLUSIVE only; concurrent DML continues.
ALTER TABLE orders VALIDATE CONSTRAINT orders_fulfillment_state_nn;

-- 3. Promote to a real NOT NULL without a second scan (PostgreSQL 12+).
ALTER TABLE orders
  ALTER COLUMN fulfillment_state SET NOT NULL;

ALTER TABLE orders DROP CONSTRAINT orders_fulfillment_state_nn;

-- 4. Only in a LATER deploy, once N-1 is fully drained:
ALTER TABLE orders DROP COLUMN status;

Renaming Columns Without a Big Bang

ALTER TABLE ... RENAME COLUMN is a metadata-only operation in PostgreSQL and MySQL. It is also the single most common cause of migration outages, because it instantly breaks every running process that references the old name.

Treat a rename as a full Expand–Contract cycle: create the new column, dual-write, backfill, switch all reads, then drop. If you genuinely cannot touch the application code in time, insert a compatibility shim — a view exposing the old name over the new table, plus an INSTEAD OF trigger for writes:

CREATE VIEW orders_v1 AS
  SELECT id, customer_id, fulfillment_state AS status, created_at
  FROM orders;

CREATE OR REPLACE FUNCTION orders_v1_write() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE orders SET
    fulfillment_state = NEW.status,
    customer_id       = NEW.customer_id
  WHERE id = NEW.id;
  RETURN NEW;
END;
$$;

CREATE TRIGGER orders_v1_update
  INSTEAD OF UPDATE ON orders_v1
  FOR EACH ROW EXECUTE FUNCTION orders_v1_write();

Shims are debt with an expiry date. Track them in your migration register and delete them at Contract.

Online DDL Tooling: Matching the Tool to the Engine

CREATE INDEX CONCURRENTLY and Its Failure Modes

-- Must run OUTSIDE a transaction block.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_fulfillment_state_idx
  ON orders (fulfillment_state, created_at DESC);

A failed concurrent build leaves an invalid index behind. It still consumes write amplification but is never used by the planner. Detect and clean up:

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

-- Drop concurrently, then retry the build.
DROP INDEX CONCURRENTLY IF EXISTS orders_fulfillment_state_idx;

Multi-Region, Sharded, and Event-Driven Topologies

Region Deploy Ordering

In an active-passive topology, the ExpAND migration must reach every region — including replica regions — before any Contract migration runs anywhere. A contract applied to the primary while a lagging replica still serves traffic produces a split-brain error pattern that is painful to diagnose. Codify the order in your pipeline: expand → all regions → soak → migrate → soak → contract.

Orchestrating Across Shards

For sharded fleets, treat migration as a per-shard state machine with a canary shard carrying ~1% of traffic. Requirements:

• Every migration statement is idempotent (IF NOT EXISTS, IF EXISTS).
• The runner records (shard_id, migration_version, applied_at, checksum) and refuses to proceed on checksum drift.
• Schema diff tooling (Atlas, sqldef) runs nightly across shards to catch manually-applied hotfixes.

Keeping Event Schemas Compatible Too

Your database schema is not the only contract. Kafka topics, Protobuf messages, and Avro records evolve in parallel. Register the new field as optional with a default, and set the subject to BACKWARD_TRANSITIVE compatibility so consumers on N-1 never fail deserialization. Never reuse or renumber field tags.

Safety Gates: Lock Timeouts, Retries, and Abort Conditions

Lock Queues Are the Actual Killer

An ALTER TABLE waiting on ACCESS EXCLUSIVE does not merely wait — it blocks every query that arrives after it, because the lock queue is FIFO. A two-second DDL wait becomes a two-second outage for every subsequent SELECT. The mitigation is a hard lock_timeout with bounded exponential retry:

var ErrLockTimeout = errors.New("migration: could not acquire lock")

// ExecDDLWithRetry bounds each lock acquisition attempt and retries with
// exponential backoff. PostgreSQL error 55P03 = lock_not_available.
func ExecDDLWithRetry(ctx context.Context, db *sql.DB, ddl string, attempts int) error {
	if attempts < 1 {
		return errors.New("migration: attempts must be >= 1")
	}
	for i := 0; i < attempts; i++ {
		if _, err := db.ExecContext(ctx, `SET lock_timeout = '3s'`); err != nil {
			return fmt.Errorf("set lock_timeout: %w", err)
		}

		_, err := db.ExecContext(ctx, ddl)
		if err == nil {
			return nil
		}

		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == lockNotAvailable {
			time.Sleep(time.Duration(1<<uint(i)) * time.Second)
			continue
		}
		return fmt.Errorf("exec ddl: %w", err)
	}
	return fmt.Errorf("%w: %s after %d attempts", ErrLockTimeout, ddl, attempts)
}

Abort Conditions Worth Automating

Rollback Strategy: Forward-Only With a Safety Net

Reversible migrations sound attractive but are a trap in distributed systems: a down-migration that drops the new column will crash every N process that already shipped. The production-grade posture is:

1. Forward-only migrations. Every migration has a down that is a no-op or a documented runbook step, never an automatic DDL reversal.
2. Feature-flag the read path. Switching reads from legacy to new column is a flag flip, not a deploy — reversible in seconds.
3. Keep the Expand state alive for one full release cycle. Rollback means reverting application code while the database stays in the superset state.
4. Snapshot before Contract. A logical dump or PITR marker before any destructive DDL buys you a defined recovery point.

Operational Checklist

• Migration is decomposed into Expand / Migrate / Contract, each in its own deploy.
• Every DDL statement is idempotent.
• lock_timeout and statement_timeout set on every connection running DDL.
• Backfill is keyset-paginated, SKIP LOCKED, resumable, and lag-aware.
• Dual-write is guarded by a flag that is removed at Contract.
• Read path falls back to the legacy column until backfill is verified complete.
• Zero legacy readers confirmed via pg_stat_statements or MySQL performance_schema before dropping anything.
• Canary shard / canary region applied first, with automated abort thresholds.
• Rollback runbook reviewed by on-call; PITR snapshot taken pre-Contract.

Common Pitfalls to Avoid

• `ADD COLUMN ... DEFAULT now()` on PostgreSQL < 11. Rewrites the entire table under ACCESS EXCLUSIVE. On 11+ use a non-volatile default, or add the column nullable and backfill.
• Batching by `OFFSET`. Degrades linearly and skips rows when concurrent writes mutate ordering. Use keyset pagination on the primary key.
• Forgetting the `NOT VALID` → `VALIDATE` sequence. A validated CHECK added in one statement takes ACCESS EXCLUSIVE and scans the whole table.
• Running `CREATE INDEX CONCURRENTLY` inside a transaction. It fails outright, and the leftover invalid index is easy to miss.
• Dropping the legacy column in the same deploy as the Contract constraint. You have not yet proven N-1 is gone.
• Ignoring the outbox / CDC stream. If Debezium is replicating orders, a column rename silently changes the event payload for downstream consumers.

Sources & Technical References

1. PostgreSQL Global Development Group. ALTER TABLE — PostgreSQL 16 Documentation. https://www.postgresql.org/docs/current/sql-altertable.html
2. PostgreSQL Global Development Group. CREATE INDEX (CONCURRENTLY semantics and invalid index handling). https://www.postgresql.org/docs/current/sql-createindex.html
3. PostgreSQL Global Development Group. Explicit Locking — lock modes and queue behavior. https://www.postgresql.org/docs/current/explicit-locking.html
4. PostgreSQL Global Development Group. Client Connection Defaults — lock_timeout, statement_timeout. https://www.postgresql.org/docs/current/runtime-config-client.html
5. PostgreSQL Global Development Group. Monitoring Database Activity — pg_stat_statements, pg_stat_activity. https://www.postgresql.org/docs/current/monitoring-stats.html
6. Oracle Corporation. Online DDL Operations — MySQL 8.0 Reference Manual. https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
7. GitHub Engineering. gh-ost: GitHub's Online Schema-Migration Tool for MySQL. https://github.com/github/gh-ost
8. Percona. pt-online-schema-change — Percona Toolkit Documentation. https://docs.percona.com/percona-toolkit/pt-online-schema-change.html
9. Fowler, M. ParallelChange — martinfowler.com bliki. https://martinfowler.com/bliki/ParallelChange.html
10. Debezium Project. Debezium Documentation — change data capture and schema history topics. https://debezium.io/documentation/
11. Confluent. Schema Evolution and Compatibility — Schema Registry documentation. https://docs.confluent.io/platform/current/schema-registry/fundamentals/schema-evolution.html
12. Atlas. Declarative and Versioned Database Schema Migrations. https://atlasgo.io/
13. Google SRE Team. Site Reliability Engineering, Chapter 8 — Release Engineering. O'Reilly Media. https://sre.google/sre-book/release-engineering/
14. Kubernetes Documentation. Deployments — Rolling Update Strategy. https://kubernetes.io/docs/concepts/workloads/controllers/deployment/#rolling-update-deployment
15. Kleppmann, M. Designing Data-Intensive Applications, Chapter 4 — Encoding and Evolution. O'Reilly Media, 2017.