skip to content

In PostgreSQL, what does synchronous_commit = off risk for a click-tracking workload, and why is it safe where fsync = off is not?

level: middleimportance: must knowfreq 72%

answer

  1. loss, not corruption
  2. the WAL writer flushes on a timer
  3. three times wal_writer_delay
  4. SET LOCAL or ALTER ROLE

basics

~20 s

synchronous_commit = off lets COMMIT return before its WAL is flushed, so a crash can lose up to about the last 600 ms of acknowledged commits but leaves the database consistent; fsync = off can corrupt the whole cluster after an OS crash.

solid answer

~50 s

By default (`synchronous_commit = on`) a commit waits until its WAL is flushed to disk. With `off`, the backend returns as soon as the commit is logically complete and the **WAL writer** flushes in the background every `wal_writer_delay` (200 ms), so the risk window is at most **three times** that, about 600 ms. After a crash, recovery replays WAL up to the last flushed record: the lost transactions vanish as if they had aborted, in commit order, so nothing is corrupted. The setting is user-settable, so you scope it: `ALTER ROLE click_writer SET synchronous_commit = off` or `SET LOCAL` in one transaction, while the payments ledger keeps `on`. `fsync = off` is different in kind: it is server-wide and stops PostgreSQL forcing any write to disk, so an OS crash or power loss can leave unrecoverable corruption anywhere.

code

sql · 8 lines
sql
ALTER ROLE click_writer SET synchronous_commit = off;

BEGIN;
SET LOCAL synchronous_commit = off;
INSERT INTO click_events (page_id, clicked_at) VALUES (42, now());
COMMIT;

SHOW synchronous_commit;

go deeper

for a junior

Recall that synchronous_commit = off returns before the WAL flush and can lose the last fraction of a second of commits, but never corrupts data.

for a middle

Explain the WAL writer's role, the three-times-wal_writer_delay window, the five values, and how to scope the setting per role or per transaction with SET LOCAL.

for a senior

Show where you would and would not use it - click events yes, payments no - and why fsync = off and full_page_writes = off belong only on clusters you can rebuild.

for a principal

Argue durability per dataset: which tables tolerate losing 600 ms of acknowledged commits, how that is documented and enforced through roles, and what evidence justified each choice.

## What COMMIT waits for by default In PostgreSQL a transaction's changes are durable once its **commit record** in the write-ahead log (WAL) has been flushed to stable storage. With the default **`synchronous_commit = on`**, the committing backend writes its WAL and waits until it has been flushed with the method `wal_sync_method` selects; only then does the client see success. If synchronous standbys are configured in `synchronous_standby_names`, `on` also waits for them. For short transactions - one row inserted per click - that flush wait is most of the commit's cost, which is exactly what asynchronous commit removes. ## The values of `synchronous_commit` | Value | Local WAL flushed before success? | Waits for synchronous standbys to... | |---|---|---| | `off` | no | nothing | | `local` | yes | nothing | | `remote_write` | yes | write it to their operating system | | `on` (default) | yes | flush it to their disk | | `remote_apply` | yes | replay it so their queries see it | Without `synchronous_standby_names`, only `on` and `off` differ: `local`, `remote_write` and `remote_apply` all give the same local flush as `on`. Choosing standbys and quorum is part of streaming replication setup, not of this setting. ## What `off` actually risks - **The window.** Success is returned before the WAL reaches disk. The WAL writer flushes every `wal_writer_delay` (default 200 ms), and because it prefers whole pages when busy, the manual bounds the risk window at **three times `wal_writer_delay`**: about 600 ms with defaults. - **Loss, not corruption.** After a crash, recovery replays WAL up to the last flushed record. Transactions after that point disappear as though they had aborted cleanly. Replay follows commit order, so a later transaction that depended on an earlier one can never survive while the earlier one is lost. - **What counts as a crash.** A server crash, an OS crash, power loss, and also `pg_ctl stop -m immediate`, which the manual treats as equivalent to a crash. - **What stays synchronous anyway.** Some utility commands, for instance `DROP TABLE`, and the two-phase commands such as `PREPARE TRANSACTION`, always commit synchronously whatever the setting says. - **When not to use it.** If the client takes an external action on the strength of the commit - releasing a payment, sending a confirmation email - a lost commit is a real inconsistency between systems. That is why the payments ledger stays on `on`. ## Scoping it to the click tracker `synchronous_commit` has user context: any session can change it, and the value in force **when the commit begins** decides. That lets one cluster serve both workloads: ```sql -- Every session of the click tracker's role commits asynchronously ALTER ROLE click_writer SET synchronous_commit = off; -- Or only one transaction, leaving the session default alone BEGIN; SET LOCAL synchronous_commit = off; INSERT INTO click_events (page_id, clicked_at) VALUES (42, now()); COMMIT; ``` The ledger's role keeps the default `on`, so ledger commits still wait for the flush even while click inserts run beside them. Measure before and after: the gain is largest for many tiny transactions on storage with slow flushes. ## Not the same as `fsync = off` or `full_page_writes = off` | Setting | Scope and context | What a crash can do | |---|---|---| | `synchronous_commit = off` | per transaction, user-settable | lose roughly the last 600 ms of acknowledged commits; the database stays consistent | | `fsync = off` | whole server, reload | after an OS crash or power loss, unrecoverable corruption anywhere | | `full_page_writes = off` | whole server, reload | torn pages after an OS crash: unrecoverable or silent corruption | `fsync = off` disables all of PostgreSQL's effort to force writes to disk in order, so the WAL can no longer be trusted to be ahead of the data files. The manual allows it only where you could recreate the whole cluster: an initial load from a backup, a throwaway batch cluster, a frequently rebuilt read-only clone. High-quality hardware alone is not a justification, and switching it back on safely needs the kernel's dirty buffers flushed first (for example `initdb --sync-only`). In most cases asynchronous commit delivers most of the speed of `fsync = off` without the corruption risk. `commit_delay` sounds similar but is a synchronous technique: it pauses up to `commit_delay` microseconds before a flush (only when at least `commit_siblings`, default 5, other transactions are active) so one flush can serve several commits. It is ignored for asynchronous commits.

  • After a crash with asynchronous commits in flight, can a later transaction survive while an earlier one it read from is lost?
    No. Recovery replays WAL in order up to the last flushed record, so what survives is always a prefix of the commit sequence. If transaction B relied on A's effects, losing A means B is lost too; the database never shows B without A.
  • Does pg_ctl stop -m fast lose asynchronous commits?
    No: fast and smart shutdowns finish with a shutdown checkpoint, which flushes WAL. An immediate shutdown (`-m immediate`) is treated like a crash, so any asynchronous commits not yet flushed by the WAL writer are lost.
  • Is commit_delay a way to get asynchronous commit?
    No. `commit_delay` keeps commits synchronous: it waits up to the set number of microseconds before flushing, when at least `commit_siblings` (default 5) other transactions are active, so one flush covers several commits. It defaults to 0 and is ignored when `synchronous_commit = off`.

A shop hands you a receipt while the clerk copies sales into the ledger book every fraction of a second. If the lights go out, the last few receipts may be missing from the book, but every page in it is still correct. Turning fsync off is letting clerks write pages in any order: a blackout can leave the book itself unreadable.

  • synchronous_commit = off can corrupt the database after a crash.
  • fsync = off is a safe speed-up on good hardware.
  • synchronous_commit can only be changed server-wide with a restart.
  • With synchronous_commit = off, everything since the last checkpoint is lost in a crash.
  • remote_write and remote_apply change behaviour even with no synchronous standbys configured.