Explain commit without flushing every data page
A database can preserve required log records before writing every changed data page, then use that log during recovery. WAL ordering prevents a changed page from reaching durable storage ahead of the log needed to recover it. Distinguish record generation, log synchronization, page writeback and commit acknowledgment when explaining the relationship between update cost and recovery.
The experiment creates a dedicated temporary PostgreSQL cluster, commits row one, leaves row two uncommitted, immediately stops database processes and restarts. It checks visible rows and recovery logs. This is not an OS or media power-loss test. File existence alone is insufficient evidence of recovery, but this small run also cannot certify every failure mode or restoration from backups. The target and fault boundary are deliberately limited.
Place log state and data writeback on one timeline
Generating a WAL record in memory is not necessarily enough for durability. Required records must reach the appropriate synchronized boundary before dependent page writeback. Pages can remain unwritten at commit when recovery can redo the changes, avoiding synchronization of every changed page on each transaction. The acknowledgment boundary still depends on configuration and promised durability.
The dedicated cluster explicitly enables fsync and synchronous_commit. Do not transfer its guarantee to a run with weaker settings. Local log retention also differs from replication or archiving that survives host or medium loss. Advancing a numeric log position alone does not prove arrival at every required destination. Identify which location was synchronized where, and preserve the corresponding configuration with the observation.
Use checkpoints without treating them as universal log deletion
A checkpoint helps define recovery position and coordinate required writeback. It does not mean every later or earlier log segment can simply be discarded; replication, backups and archives can impose additional retention requirements. The example checkpoints after table creation and then prepares committed and uncommitted rows, making the later redo messages easier to relate to a known stage.
Checkpoint frequency affects normal write work and potential recovery work, but this experiment does not optimize that setting. Compare recovery-time requirements, write bursts and retained storage before selecting it. Connect to the WAL/snapshot comparison while keeping base data, change logs and MVCC visibility snapshots distinct. Similar checkpoint vocabulary across layers is not evidence that those states are interchangeable or that one short restart measured every tradeoff.
Run only a fresh isolated cluster
Execute the Python script as a non-root user with PostgreSQL18 tools available. Override LAVACS_LAB_PGBIN if their location differs. Data and a Unix socket live inside a new temporary directory, and an empty listen_addresses disables TCP listening. Trust authentication is limited to this disposable local instance; the script does not modify an existing service or accept an existing data-directory target.
pg_ctl waits for startup. One separate INSERT commits row one; an open psql BEGIN retains row two uncommitted. The driver waits for UNCOMMITTED_READY before stopping rather than guessing the stage with sleep. Immediate shutdown targets only the newly created directory. Check tool paths and isolation before the exercise. Its fault-injection command belongs on this disposable instance, not on a valuable database being used for other work.
import os,pathlib,subprocess,tempfile
pgbin=pathlib.Path(os.environ.get('LAVACS_LAB_PGBIN','/usr/lib/postgresql/18/bin'))
with tempfile.TemporaryDirectory(prefix='wal-lab-') as directory:
root=pathlib.Path(directory);data=root/'data';log=root/'server.log';running=False;session=None
def run(tool,*args):return subprocess.check_output([str(pgbin/tool),*map(str,args)],text=True,stderr=subprocess.STDOUT)
def control(action,*args):return run('pg_ctl','-D',data,'-w',*args,action)
def query(sql):return run('psql','-X','-At','-v','ON_ERROR_STOP=1','-h',root,'-p','6544','-d','postgres','-c',sql).strip()
try:
run('initdb','-D',data,'--auth=trust','--no-locale')
control('start','-l',log,'-o',"-k "+str(root)+" -p 6544 -c listen_addresses='' -c fsync=on -c synchronous_commit=on")
running=True
query('CREATE TABLE recovery_lab(id int PRIMARY KEY);CHECKPOINT;')
query('INSERT INTO recovery_lab VALUES(1);')
session=subprocess.Popen([str(pgbin/'psql'),'-X','-q','-At','-h',str(root),'-p','6544','-d','postgres'],stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE,text=True,bufsize=1)
session.stdin.write('BEGIN;INSERT INTO recovery_lab VALUES(2);\n\\echo UNCOMMITTED_READY\n');session.stdin.flush()
assert session.stdout.readline().strip()=='UNCOMMITTED_READY'
control('stop','-m','immediate');running=False;session.communicate(timeout=5)
control('start','-l',log,'-o',"-k "+str(root)+" -p 6544 -c listen_addresses='' -c fsync=on -c synchronous_commit=on");running=True
rows=query('SELECT id FROM recovery_lab ORDER BY id;');assert rows=='1'
print('after immediate stop: committed1 visible; uncommitted2 invisible')
print(log.read_text())
finally:
if session is not None and session.poll() is None:session.kill();session.communicate()
if running:control('stop','-m','fast')
Read recovery logs together with visible rows
The recorded PostgreSQL18.6 log detected unclean shutdown, ran automatic recovery, reported redo start and completion, performed an end-of-recovery checkpoint and accepted connections. The query returned only id one. Assertions establish committed row visibility and uncommitted row invisibility, not physical absence of every uncommitted tuple from storage.
Interpret end-of-log messages with surrounding recovery and readiness events. Neither a naive keyword scan nor ignoring logs is sufficient. Combine final query results and exit status with the actual recovery sequence. Retaining the log distinguishes restarting the same data directory from creating a fresh empty instance and merely observing its initial contents. The provenance of the recovered state is part of the evidence, not an optional detail after a successful SELECT.
| Row | Before stop | SELECT after restart |
|---|---|---|
| 1 | Committed insert | Visible |
| 2 | Uncommitted inside BEGIN | Not visible |
Redo is not business-level undo
Recovery makes committed database state usable again. An erroneous order that was committed can therefore survive just like a correct one. WAL does not select only semantically desirable transactions. Point-in-time recovery before an error, business compensation and decisions about reapplying later valid work require separate procedures.
Database recovery also does not automatically synchronize external notifications or files to the same boundary. As with Outbox, define which records share a transaction and how external work is queried or retried. The two-row experiment does not cover multi-resource recovery. Distinguish process restart, database visibility, application business state and external effects whenever saying that the system recovered. One successful database query supports only part of that larger story.
Keep backup restoration outside this run’s claim
This run restarts from data and WAL retained in the same temporary storage. It does not restore a base backup or archived logs after losing that storage. Local crash recovery and medium-loss restoration need different information and procedures. A log on the same failed medium is not automatically an independent recovery copy.
A future restoration exercise should assemble a compatible base state, continuous logs, settings, extensions and permissions in another location and test application reads and writes. Backup-job completion, file presence, successful restoration and service recovery within a required time are separate milestones. The current evidence only covers immediate process shutdown and restart from the same directory. That boundary clarifies the next verification task instead of making a small successful experiment appear to certify the entire recovery strategy.
A connection can fail between commit and its response
Suppose the database flushes the required log and commits a change, but the connection fails before the caller receives success. The caller has no successful response, yet the row may survive restart. A caller-side timeout is not evidence that the database necessarily rolled the transaction back. The durable decision and the caller learning its result are separate events.
Blindly inserting the same order with a new identity can now create two orders. Connect a stable request identity to a way of inspecting the original attempt's outcome. If a subsequent read finds no row, retain which database and snapshot served that read; a lagging replica does not establish that no commit occurred.
The executed program synchronizes completion of row one's commit before stopping and does not inject a lost response. For the written exercise, arrange log flush, commit decision, response transmission and receipt as separate stages. Locate failures where the caller remains uncertain. Recovery correctness and an application's one-order-per-request contract are distinct questions, even when both rely on durable state. Keeping them separate explains the role of WAL without assigning it an application-level deduplication guarantee.
Exercise: state the observed committed/uncommitted outcome
After row one commits and row two remains inside BEGIN during immediate shutdown, the restart test establishes that SELECT sees one only and the log shows automatic recovery. It does not establish complete physical erasure of row two, reversal of external notifications or restoration after medium loss. Use terms matching the observed boundary.
Changing synchronous_commit creates another condition with potentially different acknowledgment durability; do not inherit the original run's guarantee without recording configuration and fault position. A checkpoint also does not alone justify deleting every WAL segment because replication and backups may require retention. Explain log generation, synchronization, page writeback, checkpointing and post-recovery visibility as separate events. That sequence is the durable lesson beyond this particular two-row output.