System Design Cases
Database Migration Strategies
Database Migration Strategies — concept page covering online schema change (gh-ost, pt-online-schema-change), expand-contract pattern, dual-write Mongo→Postgres replatform with CDC, and big bang vs incremental migration tradeoffs. Four scenarios + 2 ADRs (online vs maintenance window, gh-ost vs pt-osc vs pg_repack).
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.
Границы и компоненты
| Компонент | Ответственность |
|---|---|
| Version-Tolerant Application | Работает со старой и новой schema в expand window. |
| Versioned Write Router | Выбирает единственный authoritative writer по migration epoch. |
| Source Database | Остаётся source of truth до fenced cutover. |
| WAL or CDC Position | Несёт committed changes после snapshot position; retention ограничена. |
| Idempotent Backfill | Копирует ranges с checkpoints и conditional upsert. |
| Target Database | Принимает snapshot и ordered changes, но не становится writer преждевременно. |
| Range Validator | Сверяет counts, hashes, invariants и lag перед gate. |
Сценарии
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 конкретного продукта.
Связанные темы
Первичные источники
- 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.