CodeParkPostgreSQL uses a write-ahead log (WAL): a log file where every change is recorded first. Data pages are changed in shared memory. At commit, PostgreSQL only has to flush the WAL up to the commit record to disk. With the default synchronous_commit = on, it does not report the commit until that flush finishes.
The background writer writes the changed data pages to disk later, and so do checkpoints. After a crash, PostgreSQL replays the WAL from the last checkpoint. This restores every committed change.
Why is this faster? WAL writes are small and sequential. That is far cheaper than random writes of whole pages.
Setting synchronous_commit = off can lose the last few commits in a crash. But it does not corrupt data. The same WAL also feeds streaming replication and point-in-time recovery.
- Changes are logged to the WAL before data pages are written.
- Commit flushes the WAL to disk, not the data pages.
- Checkpoints and the background writer flush data pages later.
- Crash recovery replays the WAL from the last checkpoint.
synchronous_commit = offis faster but can lose the last few commits in a crash.