System Design Cases
WAL (Write-Ahead Log)
WAL (Write-Ahead Log) — universal durability mechanism. Append-only sequential file, fsync semantics, group commit, checkpoint, crash recovery. Used in Postgres, MySQL InnoDB, RocksDB, Kafka log, etcd, Spanner, ext4 journal. Concept page covering write path (modify in-memory → WAL append → fsync → ack → flush page later), crash recovery (replay WAL from last checkpoint), group commit batching, and synchronous_commit=off trade-off.
Write-ahead logging: commit durability, checkpoints and recovery
WAL не означает «сначала записали бизнес-строку в log table». Это physical/logical recovery protocol: redo information становится durable до data page, а commit ACK привязан к выбранной flush policy.
Корректная модель
- Write-ahead rule: relevant WAL reaches stable storage before its data-page changes.
- Synchronous and asynchronous commit choose different acknowledgement/RPO contracts.
- Checkpoint shortens recovery and bounds WAL recycling; it is not a backup or transaction boundary.
- Full-page images address torn-page risk and must not be disabled without proven storage guarantees.
Границы и компоненты
| Компонент | Ответственность |
|---|---|
| Transaction Client | Получает success, timeout или failure; timeout оставляет ambiguous outcome. |
| Transaction Engine | Формирует changes, WAL records и commit record. |
| WAL Buffer | Временная память; сама по себе не переживает crash. |
| Durable WAL | Ordered redo stream на проверенном non-volatile storage boundary. |
| Dirty Data Pages | Могут сбрасываться позже WAL и быть частично записанными при power loss. |
| Checkpointer | Продвигает recovery starting point; checkpoint не заменяет backup. |
| WAL Archive and Base Backup | Поддерживает PITR при непрерывной цепочке и проверенном restore. |
| Crash Recovery | Читает control/checkpoint state и replay WAL до consistent state. |
Сценарии
Synchronous commit ordering
The engine appends the transaction and commit record, flushes WAL to the configured durable boundary, and only then acknowledges. Dirty data pages need not be flushed at commit.
Проверяемый исход: A success response means the configured WAL durability contract was reached; data pages may still be only in buffer cache.
Asynchronous commit loss window
An async policy may acknowledge before the commit record is durably flushed. A crash in that interval can lose recent acknowledged transactions; the bound depends on implementation and load.
Проверяемый исход: The UI states the non-zero RPO and never invents a universal millisecond loss bound.
Checkpoint and torn-page recovery
A checkpoint flushes pages over time and writes recovery metadata. Full-page images after checkpoints protect the first modified page from partial-page writes on ordinary storage.
Проверяемый исход: Recovery starts from a valid checkpoint and uses WAL to restore page consistency; full_page_writes is not disabled by blanket advice.
Point-in-time restore
A base backup plus an unbroken archived WAL chain is restored into an isolated target and replay stops at the selected time/LSN.
Проверяемый исход: PITR is proven by restore testing; checkpoints or replicas alone are not called backups.
Failure, concurrency и replay checklist
- Test that the storage stack honors flush/barrier semantics.
- Treat client timeout after commit send as ambiguous and use idempotent operation identity.
- Monitor WAL generation, archive gaps, replication slots and available disk.
- Restore base backups plus WAL regularly in isolation.
Формулы, units и допущения
- Учебное допущение: WAL generation 5 MiB/s for one hour is 18,000 MiB ≈ 17.6 GiB before archive compression/overhead.
- Required WAL retention >= peak generation rate × maximum downstream outage, with safety margin; slots can retain more until consumers advance.
- Recovery time depends on bytes to replay, random page work and IO/CPU; checkpoint interval alone does not determine an exact RTO.
Числа выше — учебные inputs или размерностные формулы. Их нельзя выдавать за benchmark или SLA конкретного продукта.
Связанные темы
[CONCEPT]database-migration-strategies
Первичные источники
- https://www.postgresql.org/docs/current/wal-intro.html
- https://www.postgresql.org/docs/current/wal-reliability.html
- https://www.postgresql.org/docs/current/runtime-config-wal.html
- https://www.postgresql.org/docs/current/continuous-archiving.html
- https://dl.acm.org/doi/10.1145/128765.128770
Scope note
Диаграмма показывает причинные границы и recovery contracts, а не скрытую реализацию конкретного managed-сервиса. Любая stronger guarantee действует только в явно названной transaction/checkpoint/acknowledgement boundary.