Franck PachotDatabase Developer Advocate
All minibooks
Database field guide · 12
From commit records to crash recovery
How PostgreSQL WAL and Oracle redo turn memory changes into durable, recoverable database state.
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.01
A database can acknowledge a commit before every changed data block reaches its final location only because the recovery log reaches durable storage first.
02
A commit record joins transaction semantics to storage ordering. Group commit amortizes one flush across several sessions, while synchronous replication can extend the boundary to another failure domain.
03
A checkpoint establishes how far recovery may need to scan and creates pressure to write dirty buffers. Aggressive checkpoints shorten replay but increase write bursts and full-page logging.
04
PostgreSQL full-page images and Oracle block recovery mechanisms ensure that a crash cannot leave a partially written page that logical redo cannot repair.
05
Crash recovery repeats logged changes and treats transactions without a durable commit as aborted. Instance recovery, media recovery, and point-in-time recovery start from different material but share ordered redo.
06
One logical row change can update heap blocks, indexes, transaction metadata, and replicas. Log volume is therefore a physical workload signal, not merely a backup concern.
07
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
PostgreSQL reports generated WAL directly in EXPLAIN and exposes WAL positions for interval measurements. Compare equivalent business operations rather than raw bytes per second.
SELECT pg_current_wal_lsn() AS before_lsn;
-- execute one representative transaction
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), $1));pg_stat_wal separates records, full-page images, bytes, write calls, flush calls, and cumulative times when track_wal_io_timing is enabled. High generation and slow flush require different remedies.
SELECT wal_records, wal_fpi, wal_bytes, wal_write, wal_sync,
wal_write_time, wal_sync_time
FROM pg_stat_wal;Frequent requested checkpoints, long write phases, or large backend buffer writes indicate that checkpoint pacing and buffer capacity need joint analysis.
SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time,
checkpoint_sync_time, buffers_checkpoint, buffers_backend
FROM pg_stat_bgwriter;Oracle cumulative statistics and log history distinguish generation rate, switches, and recovery retention. A log switch is an operational boundary, not a commit flush.
SELECT name, value FROM v$sysstat
WHERE name IN ('redo size','redo writes','redo write time');
SELECT sequence#, first_time, next_time FROM v$log_history
ORDER BY sequence# DESC FETCH FIRST 20 ROWS ONLY;08
Optional deep dives with the complete experiments and product-version context behind this guide.