hard Group Commit & fsync Durability

Why durability is a disk-flush problem

A database calls a transaction durable the moment its commit record in the write-ahead log (WAL) is safely on stable storage — not merely written, but fsync'd, so it survives an OS crash or power loss. That single guarantee sets a hard physical ceiling on how fast you can commit: a durable commit cannot be acknowledged until a disk flush completes. Everything about commit performance is a fight against the cost of that flush.

The problem: one fsync per commit is brutally slow

A single fsync to durable storage costs roughly 0.5–10 ms depending on the device (a 7200-rpm disk pays about a rotation; a consumer SSD without power-loss protection a few ms; an enterprise SSD or a battery-backed cache far less). If every transaction does its own fsync before returning, commit throughput is capped at one commit per flush:

ceiling = 1 / fsync_time
  fsync = 10 ms  → ~100   commits/sec
  fsync =  5 ms  → ~200   commits/sec
  fsync =  1 ms  → ~1000  commits/sec
  fsync = 0.5ms  → ~2000  commits/sec

So a naive per-transaction-fsync database tops out around ~100–2000 commits/sec regardless of how many CPU cores or client connections you throw at it. The bottleneck is not compute; it is the serialized wait on the disk. A hundred threads each doing their own 5 ms fsync still only get ~200 durable commits/sec between them, because the flushes queue up.

The fix: group commit batches many commits into one fsync

The insight: an fsync flushes everything currently in the WAL buffer, not just one transaction's record. So while one fsync is in flight, let other committing transactions append their commit records to the same buffer. When the flush returns, it has made all of them durable at once — and every one of them can be acknowledged. This is group commit.

Traced throughput arithmetic (fsync = 5 ms)

	Per-transaction fsync	Group commit
fsyncs per second	1000 ms / 5 ms = 200	1000 ms / 5 ms = 200
commits per fsync	1	~40 (whatever accumulated during the 5 ms window)
commit throughput	200 × 1 = 200 / sec	200 × 40 = 8,000 / sec
latency per commit	~5 ms (one fsync)	~5 ms (still one fsync)

The crucial property: group commit raises throughput without raising latency. Each transaction still waits about one fsync — it just no longer waits alone. And it is self-tuning: the busier the system, the more transactions pile into each batch, so throughput climbs exactly when you need it while latency stays pinned near one flush. This is why real databases (PostgreSQL, MySQL/InnoDB, and others) do group commit automatically — it is close to a free lunch, trading a few ms of extra commit latency under light load for order-of-magnitude throughput under heavy load.

The durability knob: trading a data-loss window for speed

Group commit keeps full durability. If even one fsync of latency per commit is too much, the next lever is to relax when the flush must happen. In PostgreSQL that is synchronous_commit=off: the commit returns to the client before the WAL is flushed, and the WAL writer flushes shortly after (within a small, bounded delay). The commit-side fsync disappears from the critical path, so throughput and latency both improve dramatically.

What you give up is precisely defined: a crash loses only the transactions committed in the last few hundred milliseconds — the ones acknowledged but not yet flushed. Critically, the database is not corrupted. The WAL is still written in order and recovery still produces a fully consistent database; you have simply lost the most recent tail of commits. This is a bounded data-loss window, and for many workloads (analytics ingest, metrics, "I can replay the last second") it is a fine trade.

fsync = off is a completely different, dangerous setting

Do not confuse relaxed sync with turning fsync off entirely. synchronous_commit=off still fsyncs the WAL — just slightly later — so ordering and consistency hold. Setting fsync=off tells the engine to never force pages to disk at all. The OS is then free to reorder and delay writes, so a crash can leave the data files and the WAL in mutually inconsistent states. The failure mode is no longer "lose the last second of commits" — it is a corrupted database that may not recover at all. The rule of thumb: relaxed sync risks recent-commit loss; fsync=off risks the whole database.

The same knob in MySQL/InnoDB

innodb_flush_log_at_trx_commit exposes the identical trade-off with three settings:

Value	Behavior	What a crash costs
1 (default)	Write + fsync WAL at every commit	Nothing — full durability
2	Write to OS cache at commit; fsync once per second	mysqld crash: nothing (OS still has it). OS/power crash: ~1 s of commits
0	Write + fsync only once per second	Any crash, including mysqld: ~1 s of commits

Settings 0 and 2 are the bounded-loss analogues of synchronous_commit=off; all three keep the database consistent. None of them is the "corruption" knob — that would be disabling innodb_flush_method's durability or lying hardware caches.

Pitfalls

• Lying hardware. A consumer disk with a volatile write cache can ACK an fsync while data still sits in RAM. Then even synchronous_commit=on can lose committed data on power loss. Use disks with power-loss protection or a battery-backed cache, and test it.
• Confusing loss with corruption. Reaching for fsync=off to "make it fast" is the classic disaster: it does not just risk the last second, it risks an unrecoverable database.
• Synchronous replication multiplies the wait. If a commit must also wait for a replica to acknowledge (PostgreSQL synchronous_commit=remote_apply, semi-sync MySQL), the network round-trip is added on top of the local fsync — group commit helps here too by batching the replication stream.
• Tiny transactions amplify the tax. Auto-committing each of 10,000 inserts pays 10,000 commit costs; wrapping them in one transaction (or letting group commit batch them) pays far fewer. Batch writes.

Judgment — durability vs throughput/latency

These are three levers on one trade-off, and they are not mutually exclusive — the senior move is to reach for the free one first:

• Group commit (default, keep it on). Costs nothing you care about — a few ms latency under light load — and buys order-of-magnitude throughput with zero durability loss. There is almost never a reason to defeat it. Use it always; it is the baseline, not a trade.
• Relaxed sync (synchronous_commit=off / innodb_flush_log_at_trx_commit=2). Choose when a bounded loss of the last sub-second of commits is acceptable and you need lower commit latency than one fsync allows — ingest pipelines, metrics, caches, anything replayable. Do not choose it for money movement or "the receipt said it's saved" semantics.
• Battery-backed write cache / power-loss-protected SSD (the hardware answer). Choose when you want both full durability and low latency and can spend on hardware. The fsync returns as soon as the WAL is in the protected cache (sub-0.1 ms), so per-commit durability becomes cheap — no software trade-off at all. This is how high-end OLTP gets thousands of durable commits/sec.

The decision: start with group commit (always on). If commit latency still hurts, the fork is can I lose the last few hundred ms? If yes → relaxed sync (cheap, software-only). If no → buy durable-cache hardware (keeps the guarantee, costs money). Reaching for fsync=off is never the answer — it trades a durability guarantee for a corruption risk, which is not a trade a serious system makes.

Takeaways

• Durability = the WAL commit record must be fsync'd to stable storage before the commit is acknowledged.
• One fsync is ~0.5–10 ms, so per-transaction fsync caps commit throughput at ~100–2000/sec; the disk, not the CPU, is the wall.
• Group commit batches many concurrent commit records into one fsync → throughput = fsyncs/sec × batch size, while each commit still waits only ~one fsync of latency. Near-free, keep it on.
• Relaxed sync (synchronous_commit=off) trades a bounded loss window for speed but stays consistent; fsync=off trades away the guarantee entirely and risks corruption — a categorically different, dangerous knob.

---

Synthesized from the PostgreSQL documentation (WAL Configuration, synchronous_commit, fsync), the MySQL InnoDB reference (innodb_flush_log_at_trx_commit), Gray & Reuter, Transaction Processing (group commit), and Kleppmann, Designing Data-Intensive Applications (durability, replication). Re-authored and diagrams hand-authored (SVG) for this guide.

I just read a lesson on **Group Commit & fsync Durability** (System Design) and want to truly understand it. Explain Group Commit & fsync Durability from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Group Commit & fsync Durability** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Group Commit & fsync Durability** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Group Commit & fsync Durability** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.