When you commit a row and Postgres is killed a second later, the row is still there after restart. But it was never in the table when Postgres died. Something else saved it.
When COMMIT returns, the natural assumption is that the row is now in the table on disk. It usually isn’t yet.
I checked this two ways: watched the table file while committing, and killed the database mid-write to see what came back.
The table is not written at commit
To change a row, Postgres works on its 8KB page in memory. That page eventually has to be written back to its exact spot on disk. If every commit forced that write, the disk would spend its time seeking between scattered pages. Random writes are slow.
Postgres writes to the write-ahead log first. The WAL is one file you only ever append to. Every change goes on the end, in order. Appending to the end of a file avoids the seeking, so it is far faster than scattered writes to the table.
At commit, the log record has to reach disk. The table gets updated later asynchronously.
You can watch the two files. With one row already inserted and a checkpoint forced so the table file starts clean, insert another row, commit, then fingerprint the table file with md5sum; if a single byte changed, so does the fingerprint. Also read pg_current_wal_flush_lsn(), which reports how far the log has been flushed to disk. Take three snapshots: at rest, after the commit, and after a forced checkpoint.
heap (table file) wal_flush (log)
baseline 9af30520f622cb2f9a786162ce7e51e8 0/7F73648
after commit 9af30520f622cb2f9a786162ce7e51e8 0/7F73790
after checkpoint ed6a6115e10297d4bc2e52beda5fa9ea 0/7F73860Baseline to after commit: the table fingerprint is identical. 9af30520 before, 9af30520 after. The row committed and the table file did not change. But the log position moved, 0/7F73648 to 0/7F73790.
After commit to after checkpoint: now the table fingerprint changes, 9af30520 to ed6a6115. This is when the row reached the table file, and it only happened because a checkpoint was forced. A checkpoint is Postgres writing its dirty pages out to the table files. Until then, the table file did not have the row.
The log is what survives the crash
The checksum shows the table stays stale. To test durability, crash Postgres and count.
A Node script inserts rows one at a time: id 1, 2, 3, and so on. It waits for each commit to come back, then writes that id to a file. Every id in that file is one Postgres told the client had committed.
await c.query('INSERT INTO t (id) VALUES ($1)', [id]);
fs.writeSync(fd, id + '\n');Because it is fs.writeSync, the id is not waiting in Node’s buffer when Postgres is killed.
Then kill -9 Postgres mid-write, with no chance for a clean shutdown. Restart it, let it recover, and compare the highest id in the table against the last id in the file; if nothing was lost, they match.
docker kill --signal=SIGKILL fsync-pg >/dev/null
...
docker start fsync-pg >/dev/null
...
DB=$($PSQL -tAc "SELECT coalesce(max(id),0) FROM t" | tr -d '[:space:]')
LOG=$(tail -1 results/confirmed.log 2>/dev/null | tr -d '[:space:]')Run it three times. Then change one setting and run it three more. The setting is synchronous_commit.
synchronous_commit = on is the default. Commit waits for the log record to reach disk before it returns.
synchronous_commit = off does not wait. Commit returns as soon as the log record is in memory, and the WAL writer flushes it to disk a fraction of a second later. Faster, because you skip the wait. In that window Postgres has returned “committed” while the log record is still only in memory.
echo "==> setting synchronous_commit=$MODE"
$PSQL -c "ALTER SYSTEM SET synchronous_commit = '$MODE';" >/dev/null
docker restart fsync-pg >/dev/null
sleep 5With on:
on run 1: db=87828 log=87828 lost=0
on run 2: db=85657 log=85657 lost=0
on run 3: db=88503 log=88503 lost=0The kill interrupted runs that had confirmed 85,657 to 88,503 commits. None were lost.
With off:
off run 1: db=130175 log=131093 lost=918
off run 2: db=130859 log=131601 lost=742
off run 3: db=126724 log=127252 lost=528The lost confirmed commits ranged from 528 to 918, over runs of about 15 seconds at roughly 8,500 commits per second.
The only difference was whether commit waited for the log to reach disk. The WAL flush prevented the loss.
Where the rows came from
The restart log shows recovery replaying WAL:
database system was not properly shut down; automatic recovery in progress
redo starts at 0/2367BC0
redo done at 0/315ACE8
checkpoint complete: wrote 627 buffers (3.8%)redo starts and redo done are two positions in the log, and subtracting them gives about 14MB. That is how much log was replayed to rebuild what was in flight. wrote 627 buffers is 627 pages that were changed in memory but had not reached the table file when it crashed, so they came back from the log. The table file was behind, and the log had the missing data.
Why it works this way
A log flush is cheap because it’s one sequential write. A single flush can cover many transactions committing at once. Flushing every changed table page instead would scatter writes all across the disk on every commit. The scattered writes happen later, during a background checkpoint.
The table is for efficient reads afterward, and it can lag behind.
I mostly thought of WAL as a recovery tool. Turns out, when you commit, durability comes from the log.
The one assumption left
This assumes flushing to disk works. You call fsync(), the kernel writes the bytes, you get back success, and the log record is durable.
On working hardware this holds fine, and everything above depends on it holding. In 2018 the Postgres community found a case where it breaks: on certain disk errors the kernel discards the data and still reports the flush as successful. If the WAL record never reached disk, the commit is not durable. That bug became known as fsyncgate.
In the normal case, commit flushes WAL, crash recovery replays it, and the table catches up later.
The crash test and the checksum experiment are in a repo you can run: postgres-commit-durability. Docker and Node, a couple of minutes. Every number here came out of it.