Database migrations: expand, backfill, validate, cut over, contract

Zero-downtime migration — это протокол изменения ownership, а не один SQL script. Главные риски: DDL lock, race backfill/online writes, dual-write divergence, stale router и rollback после несовместимого contract.

Корректная модель

• Expand before contract; deployed application versions define compatibility window.
• Dual writing two databases is not atomic unless a real distributed transaction exists; prefer one authoritative log/CDC path.
• Snapshot and stream require an explicit handoff position plus idempotent overlap.
• Cutover requires validation, fencing and a routing epoch; DNS alone is not a writer fence.

Границы и компоненты

Сценарии

Backward-compatible expand

Add nullable/new structures and deploy readers/writers that tolerate both representations before any destructive contract.

Проверяемый исход: Old and new application versions remain valid throughout the rollout window.

Consistent snapshot plus CDC handoff

The copy records log position P, scans a consistent snapshot, and then applies committed changes after P. A blind table scan followed by listening at current head can miss updates.

Проверяемый исход: Every source mutation is represented by either snapshot state or a later ordered change, with idempotent overlap allowed.

Validated and fenced cutover

Lag reaches the declared threshold, validators pass, source writes are fenced, and only then does the routing epoch move.

Проверяемый исход: There is never an interval with two unfenced authoritative writers.

Abort before contract or roll back safely

If validation fails, routing stays on source. After cutover, rollback is allowed only while schemas, log retention and reverse reconciliation remain compatible.

Проверяемый исход: Failure closes the gate; it does not silently continue with divergent databases.

Failure, concurrency и replay checklist

• Measure the actual DDL lock/rewrite plan on the deployed engine/version.
• Alert on CDC lag and source log retention before disk exhaustion.
• Checkpoint backfill ranges and compare source versions on upsert.
• Do not drop old columns or logs until rollback and restore gates expire.

Формулы, units и допущения

• Учебное допущение: 1,000,000,000 rows / 50,000 rows/s = 20,000 s = 5.56 h ideal scan time; retries, indexes, throttling and validation add time.
• Backlog growth = source change bytes/s − apply bytes/s. If positive, cutover never catches up without throttling or more apply capacity.
• RTO cutover включает fence + final drain + route propagation + verification; его нельзя приравнивать только к DNS TTL.

Числа выше — учебные inputs или размерностные формулы. Их нельзя выдавать за benchmark или SLA конкретного продукта.

Связанные темы

[CONCEPT]change-data-capture

[CONCEPT]wal-write-ahead-log

Первичные источники

• https://www.postgresql.org/docs/current/sql-altertable.html
• https://www.postgresql.org/docs/current/ddl-alter.html
• https://www.postgresql.org/docs/current/logical-replication-architecture.html
• https://debezium.io/documentation/reference/stable/connectors/postgresql.html

Scope note

Диаграмма показывает причинные границы и recovery contracts, а не скрытую реализацию конкретного managed-сервиса. Любая stronger guarantee действует только в явно названной transaction/checkpoint/acknowledgement boundary.