Performance: durable group commit / fsync coalescing

Summary

With durable commits enabled, each acknowledged transaction currently pays for its own device sync. That gives the strongest acknowledgement semantics, but it also makes small independent transactions fsync-bound.

#2918 measured roughly:

• 200 individual durable inserts: ~210 ms on the tested NVMe (~1 ms/device sync)
• 200 inserts in one explicit transaction: ~2–4 ms
• 200 individual inserts with DurableCommits=false: ~5.5–7.8 ms

Those numbers are storage-dependent, but they show a large gap between durability and throughput that batching at the application layer currently has to bridge.

Proposal: add group commit / durable-flush coalescing so several concurrently committing transactions can share one durable log flush, while each transaction is still acknowledged only after the flush that covers its confirm record has completed.

Related: #2818, #2918, #2935, #2849.

Goals

• Keep DurableCommits=true semantics: a successful commit is not returned before its WAL confirmation is durably flushed.
• Under concurrent commit load, allow N transactions to share one Flush(true) / equivalent device sync.
• Preserve transaction ordering, snapshot visibility and recovery semantics.
• Improve throughput without requiring callers to merge logically independent operations into one explicit transaction.
• Keep the uncontended path cheap and predictable.

Possible design

One possible implementation is a small commit barrier/coordinator around the durable log flush:

1. A transaction appends its WAL pages and confirmation record.
2. It registers the highest WAL position/version that must become durable.
3. One committer becomes the flush leader.
4. Other committers that arrive before/during that flush join the same durability generation instead of issuing their own device sync.
5. The leader flushes through the highest joined confirmation.
6. All transactions covered by that flush are released and may return success.

The exact mechanism does not need to be timer-based. A queue/generation barrier that naturally coalesces already-contending commits may be enough and avoids adding latency to isolated commits.

A short configurable batching window could be evaluated separately, but should not be required for the first implementation.

Correctness requirements

• A transaction returns success only after a durable flush that includes its confirmation record.
• No transaction may observe another transaction as committed before the existing visibility rules allow it.
• A flush failure must wake every participant in that durability generation with a consistent failure outcome; none may report success.
• Recovery after process termination and after simulated power loss must preserve all transactions whose commit returned success.
• Encrypted and unencrypted files must have identical durability semantics.
• DurableCommits=false stays an explicit opt-out and should not silently participate in a durable group.
• Explicit transactions, auto-commit writes and Shared mode need coverage.
• No on-disk format change should be necessary for group commit itself.

Performance acceptance

Add a benchmark with many independent concurrent small transactions and record:

• commits/sec
• p50/p95/p99 commit latency
• number of durable flushes
• WAL bytes written
• CPU/allocation overhead

Useful comparisons:

1. current durable commit: approximately one device sync per transaction
2. group commit with 2/4/8/16 concurrent writers
3. one large explicit transaction (upper batching reference)
4. DurableCommits=false (non-durable throughput reference)

The main success metric is durable flushes per acknowledged transaction falling substantially under contention, without weakening acknowledgement semantics.

Tests

A scripted storage double should be able to:

• count durable flushes,
• block/release a flush while more committers arrive,
• fail a flush,
• terminate between WAL append, confirmation and durable flush,
• verify every waiter is completed exactly once.

Stress coverage should also verify that checkpointing and the snapshot/checkpoint work in #2936 do not introduce a lock-order cycle with the commit coordinator.

Why v6

#2918 intentionally makes durability explicit and measurable. v6 is a good point to avoid making "safe by default" synonymous with "one fsync per logical operation" for concurrent workloads.