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.

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

Сценарии

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

[CONCEPT]mvcc

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

• 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.