vault

Asynchronous Commit in PostgreSQL

25 min read

Asynchronous Commit in PostgreSQL

Asynchronous commit is what you get by setting synchronous_commit = off: the backend writes its COMMIT record into the shared WAL buffers, marks the transaction committed in memory, and returns success to the client without waiting for the fsync. The documentation describes it as “an option that allows transactions to complete more quickly, at the cost that the most recent transactions may be lost if the database should crash” (PostgreSQL 18 §28.4). The sentence that matters most, and the one that is garbled more often than any other statement about PostgreSQL durability, is the next one: “The risk that is taken by using asynchronous commit is of data loss, not data corruption.” A crash under asynchronous commit loses whole recent transactions and leaves the database in a state indistinguishable from one in which those transactions had cleanly rolled back. It never leaves half a transaction, never tears a page, never corrupts an index. The exposure is bounded, too: because a background process flushes unflushed WAL on a timer, “the actual maximum duration of the risk window is three times wal_writer_delay” — 600 milliseconds at the 18.4 default of 200 ms. Asynchronous commit is therefore a recovery-point-objective decision, not a safety decision, and it can be made per transaction, because synchronous_commit is a PGC_USERSET parameter.

Point-in-time note. Pinned to PostgreSQL 18.4, the current release as of 2026-08-06 (released 2026-05-14, per versions.json). Source quotations were read from the REL_18_STABLE branch on the GitHub mirror on 2026-08-06 (git.postgresql.org rate-limits under fleet load).

The Guarantee, Stated Precisely

Three distinct durability settings are routinely conflated. Separating them is the single most valuable thing this note can do.

SettingDefault in 18.4What is given upWorst case after an OS/hardware crash
synchronous_commit = offonThe commit-time fsync wait, per transactionThe last ≤ 3 × wal_writer_delay of committed transactions vanish. The surviving state is self-consistent.
fsync = offonAll write ordering across WAL and data files, server-wide“Arbitrarily bad corruption of the database state.” The cluster may be unrecoverable.
full_page_writes = offonTorn-page protection for data pages, server-wide“Unrecoverable data corruption, or silent data corruption” from partially written 8 kB pages. See Full Page Writes and Torn Page Protection.

§28.4 draws the first contrast explicitly: “Asynchronous commit provides behavior different from setting fsync = off. fsync is a server-wide setting that will alter the behavior of all transactions. It disables all logic within PostgreSQL that attempts to synchronize writes to different portions of the database, and therefore a system crash … could result in arbitrarily bad corruption of the database state. In many scenarios, asynchronous commit provides most of the performance improvement that could be obtained by turning off fsync, but without the risk of data corruption.”

Why no corruption is possible. The argument is short and rests entirely on the fact that the WAL is a single ordered byte stream. On restart, PostgreSQL “will recover by replaying WAL up to the last record that was flushed” — and the flush point is a single LSN. Everything before it is replayed; everything after it never happened. Because “the transactions are replayed in commit order, no inconsistency can be introduced — for example, if transaction B made changes relying on the effects of a previous transaction A, it is not possible for A’s effects to be lost while B’s effects are preserved.”

That last clause is the load-bearing one and deserves restating in application terms. Asynchronous commit cannot produce a prefix violation. If you lose transaction n, you lose every transaction that committed after it. You will never find an orphaned child row whose parent vanished, a foreign key pointing at nothing, or an index entry for a row that does not exist — those are physical- or logical-consistency failures, and none of them is on the menu. What you will find is that the last few hundred milliseconds of acknowledged work is simply gone.

flowchart TB
    subgraph SYNC["synchronous_commit = on (default)"]
        S1["backend inserts COMMIT record<br/>into WAL buffers"] --> S2["XLogFlush(XactLastRecEnd)<br/>write + fsync, backend BLOCKS"]
        S2 --> S3["TransactionIdCommitTree()<br/>mark committed in pg_xact"]
        S3 --> S4["return 'COMMIT' to client"]
    end
    subgraph ASYNC["synchronous_commit = off"]
        A1["backend inserts COMMIT record<br/>into WAL buffers"] --> A2["XLogSetAsyncXactLSN()<br/>publish LSN, maybe poke WAL writer<br/>backend does NOT block"]
        A2 --> A3["TransactionIdAsyncCommitTree()<br/>mark committed in pg_xact<br/>AND record the LSN that must be<br/>flushed before pg_xact may be written"]
        A3 --> A4["return 'COMMIT' to client"]
        A4 -.->|"later, ≤ 3 × wal_writer_delay"| A5["WAL writer: XLogBackgroundFlush()<br/>write + fsync — NOW it is durable"]
    end

The two commit paths side by side. What it shows: the only structural difference is who performs the fsync and whether the client waits for it; both paths mark the transaction committed in pg_xact before returning, so other sessions see the same visibility either way. The insight to take: asynchronous commit does not weaken the WAL rule — it moves the flush off the client’s critical path and onto a timer. The extra bookkeeping in TransactionIdAsyncCommitTree() exists precisely to keep the rule intact for the commit log itself.

Mechanical Walk-through: RecordTransactionCommit()

Everything happens in one if in src/backend/access/transam/xact.c (REL_18_STABLE):

if ((wrote_xlog && markXidCommitted &&
     synchronous_commit > SYNCHRONOUS_COMMIT_OFF) ||
    forceSyncCommit || nrels > 0)
{
    XLogFlush(XactLastRecEnd);                 /* block until fsync completes */
    if (markXidCommitted)
        TransactionIdCommitTree(xid, nchildren, children);
}
else
{
    /* Asynchronous commit case */
    XLogSetAsyncXactLSN(XactLastRecEnd);       /* tell the WAL writer how far to flush */
    if (markXidCommitted)
        TransactionIdAsyncCommitTree(xid, nchildren, children, XactLastRecEnd);
}

Reading it symbol by symbol:

  • wrote_xlog is (XactLastRecEnd != 0) — did this transaction emit any WAL at all?
  • markXidCommitted is true only if the transaction was assigned a real transaction ID. Read-only transactions never get one.
  • synchronous_commit > SYNCHRONOUS_COMMIT_OFF exploits the ordering of the enum in src/include/access/xact.h: OFF < LOCAL_FLUSH < REMOTE_WRITE < REMOTE_FLUSH < REMOTE_APPLY, with #define SYNCHRONOUS_COMMIT_ON SYNCHRONOUS_COMMIT_REMOTE_FLUSH. So “greater than off” means “any level at all”, and every non-off level flushes locally — which is exactly what the parameter documentation says: “The local behavior of all non-off modes is to wait for local flush of WAL to disk.”
  • forceSyncCommit is set by ForceSyncCommit(), the “interface routine to allow commands to force a synchronous commit of the current top-level transaction.”
  • nrels > 0 means the transaction has pending physical file deletions. The comment explains why this cannot be deferred: “We must not allow asynchronous commit if there are any non-temp tables to be deleted, because we might delete the files before the COMMIT record is flushed to disk.” Temporary tables are exempt “since they are lost anyway if we crash.”

Three consequences fall out of that condition that surprise people:

  1. A transaction that wrote no WAL is always “asynchronous”, regardless of the setting — there is nothing to flush. This is why synchronous_commit costs read-only workloads exactly nothing.
  2. A transaction with an XID that only touched temporary or unlogged tables takes the async path too. The code comment is explicit: “In case of a crash, the loss of such a transaction will be irrelevant; temp tables will be lost anyway, unlogged tables will be truncated and HOT pruning will be done again later.”
  3. ROLLBACK is always asynchronous. RecordTransactionAbort() calls XLogSetAsyncXactLSN() unconditionally and never XLogFlush(), on the reasoning that “in event of a crash we’d be assumed to have aborted anyway.” It does publish the LSN, though, with an interesting justification: “This is important with streaming replication because if we don’t flush WAL regularly we will find that large aborts leave us with a long backlog for when commits occur after the abort.”

The Commit Log Problem, and How It Is Solved

There is a subtle trap hiding in the async path, and the way PostgreSQL escapes it is the most instructive part of the mechanism.

pg_xact (the commit log, or CLOG) is a data file like any other, and the WAL rule applies to it: its on-disk state must never get ahead of the WAL. Under synchronous commit that is automatic — the commit record is already flushed by the time TransactionIdCommitTree() runs. Under asynchronous commit it is not. clog.c states the problem in its header comment: “For synchronous transaction commits, the XLOG is guaranteed flushed through the XLOG commit record before we are called to log a commit, so the WAL rule ‘write xlog before data’ is satisfied automatically. However, for async commits we must track the latest LSN affecting each CLOG page, so that we can flush XLOG that far and satisfy the WAL rule.”

The tracking is deliberately coarse. CLOG stores a group LSN per block of 32 transactions:

#define CLOG_XACTS_PER_LSN_GROUP  32   /* keep this a power of 2 */
#define CLOG_LSNS_PER_PAGE (CLOG_XACTS_PER_PAGE / CLOG_XACTS_PER_LSN_GROUP)

TransactionIdSetPageStatusInternal() maintains group_lsn[lsnindex] = max(group_lsn, commit_lsn), and when the SLRU machinery later writes that CLOG page out, slru.c takes the maximum group LSN on the page and calls XLogFlush(max_lsn) inside a critical section (with a comment noting that an elog(ERROR) there would have to be a PANIC). So the commit log is never written to disk ahead of the WAL that justifies it — the flush is simply performed lazily, by whoever writes the page, rather than eagerly by the committing backend.

One more detail from the same file: when an async commit updates a CLOG page, SimpleLruReadPage() is called with write_ok = false, because “our update could reach disk in that write, which will not do since we mustn’t let it reach disk until we’ve done the appropriate WAL flush.” For synchronous commits the same page may be scribbled on while write-busy, “since we don’t care if the update reaches disk sooner than we think.”

sequenceDiagram
    autonumber
    participant C as Client
    participant B as Backend
    participant WB as WAL buffers
    participant CL as pg_xact (CLOG) in shared memory
    participant WW as WAL writer
    participant D as Disk
    C->>B: COMMIT
    B->>WB: insert COMMIT record at LSN L
    B->>WW: XLogSetAsyncXactLSN(L) — publish, maybe SetLatch
    B->>CL: TransactionIdAsyncCommitTree(xid, ..., L)
    Note over CL: status = COMMITTED (visible to everyone now)<br/>group_lsn[xid / 32] = max(group_lsn, L)
    B->>C: "COMMIT" — client is told it succeeded
    Note over C,D: RISK WINDOW OPENS — up to 3 x wal_writer_delay
    WW->>D: XLogBackgroundFlush(): write + fsync through L
    Note over C,D: RISK WINDOW CLOSES — transaction is now durable
    CL->>D: (much later) SLRU writes the CLOG page<br/>but first XLogFlush(max group_lsn) — WAL rule preserved

One asynchronous commit end to end, including the commit-log interaction. What it shows: the client is told “committed” at step 6, but durability arrives only at step 8, and the commit log’s own write is gated on a separate, lazier WAL flush at step 9. The insight to take: visibility and durability are decoupled here — other sessions can read the committed rows immediately, which is what makes asynchronous commit useful, and is also exactly why a crash inside the window loses work that other sessions already acted upon.

Quantifying the Window: Why Three Times wal_writer_delay

The bound comes from the WAL writer, an auxiliary process introduced in PostgreSQL 8.3 whose header comment in src/backend/postmaster/walwriter.c states the guarantee as a design property: “it guarantees that transaction commit records that weren’t synced to disk immediately upon commit (ie, were ‘asynchronously committed’) will reach disk within a knowable time --- which, as it happens, is at most three times the wal_writer_delay cycle time.” The file even warns future maintainers off adding work to it: “Because the walwriter’s cycle is directly linked to the maximum delay before async-commit transactions are guaranteed committed, it’s probably unwise to load additional functionality onto it.”

Two parameters govern the loop, both defaults verified against 18.4:

  • wal_writer_delay, default 200 ms. “After flushing WAL the writer sleeps for the length of time given by wal_writer_delay, unless woken up sooner by an asynchronously committing transaction.”
  • wal_writer_flush_after, default 1 MB (DEFAULT_WAL_WRITER_FLUSH_AFTER is (1024 * 1024) / XLOG_BLCKSZ = 128 blocks at the usual 8 kB block size). “If the last flush happened less than wal_writer_delay ago and less than wal_writer_flush_after worth of WAL has been produced since, then WAL is only written to the operating system, not flushed to disk.”

That second parameter is the reason a single cycle is not enough. XLogBackgroundFlush() in xlog.c may legitimately write on a given cycle while declining to flush:

if (WalWriterFlushAfter == 0 || lastflush == 0)
    WriteRqst.Flush = WriteRqst.Write;                 /* limits disabled: always flush */
else if (TimestampDifferenceExceeds(lastflush, now, WalWriterDelay))
    WriteRqst.Flush = WriteRqst.Write;                 /* time-based: flush */
else if (flushblocks >= WalWriterFlushAfter)
    WriteRqst.Flush = WriteRqst.Write;                 /* volume-based: flush */
else
    WriteRqst.Flush = 0;                               /* no flushing this time round */

And the function’s own comment identifies the third cycle: “(When flushing complete blocks, we allow XLogWrite to write flexibly, meaning it can stop at the end of the buffer ring; this makes a difference only with very high load or long wal_writer_delay, but imposes one extra cycle for the worst case for async commits.)”

timeline
    title Worst-case exposure for one asynchronous commit (defaults: wal_writer_delay = 200 ms)
    t = 0 ms : COMMIT returns to the client : the WAL writer has just begun a 200 ms sleep, so this commit missed the cycle entirely
    t = 200 ms : WAL writer wakes (cycle 1) : it writes complete blocks, but lastflush was recent and less than 1 MB has accumulated, so it declines to fsync
    t = 400 ms : WAL writer wakes (cycle 2) : the time test now passes, so it flushes — but a flexible write may have stopped at the WAL buffer ring boundary, short of this commit's LSN
    t = 600 ms : WAL writer wakes (cycle 3) : the remaining bytes are written and fsynced. Upper bound reached — 3 x wal_writer_delay

The documented worst case, decomposed into the three WAL-writer cycles that produce it. What it shows: each cycle contributes for a different reason — missing the current sleep, the write-without-flush branch, and the flexible-write boundary. The insight to take: 600 ms is a genuine upper bound at defaults, not a typical value; in practice most async commits are durable far sooner, because XLogSetAsyncXactLSN() wakes the writer early whenever the writer is hibernating or when flushblocks >= wal_writer_flush_after. Halving wal_writer_delay halves the bound at the cost of more frequent fsync calls; raising it linearly widens your worst-case data loss. #uncertain

Uncertain

Verify: the per-cycle attribution above (which specific delay contributes at 0–200 ms, 200–400 ms and 400–600 ms). Reason: the total bound of 3 × wal_writer_delay is stated in three primary places — §28.4, the synchronous_commit parameter documentation, and the walwriter.c header comment — and the reason for the extra cycle (“flexible” writes stopping at the buffer-ring boundary) is stated in the XLogBackgroundFlush() comment. But no primary source spells out the three-way decomposition; the mapping of one cycle to each of {missed sleep, write-without-flush, flexible-write boundary} is my reading of the code, not a quotation. To resolve: instrument XLogBackgroundFlush() under WAL_DEBUG, or find the pgsql-hackers thread in which the 3× figure was first derived. #uncertain

Two refinements matter operationally. First, the writer hibernates when idle: after LOOPS_UNTIL_HIBERNATE (50) consecutive no-op cycles it multiplies its sleep by HIBERNATE_FACTOR (25), i.e. 5 seconds at defaults. That does not widen the risk window, because XLogSetAsyncXactLSN() checks the advertised WalWriterSleeping flag and sets the writer’s latch to “kick it to make it come out of low-power mode, so that this async commit will reach disk within the expected amount of time.” Second, the writer is explicitly not essential: “regular backends are still empowered to issue WAL writes and fsyncs when the walwriter doesn’t keep up.” But if it dies unexpectedly the postmaster treats that as a backend crash and forces a full recovery cycle.

Finally, the documentation’s own caution: “An immediate-mode shutdown is equivalent to a server crash, and will therefore cause loss of any unflushed asynchronous commits.” pg_ctl stop -m immediate, a SIGQUIT, a container SIGKILL after the grace period, and an OOM kill of the postmaster all land in this category. A fast or smart shutdown does not.

What Can Never Be Asynchronous

§28.4 names two categories, and the code adds a third:

  • Utility commands that change the filesystem. “Certain utility commands, for instance DROP TABLE, are forced to commit synchronously regardless of the setting of synchronous_commit. This is to ensure consistency between the server’s file system and the logical state of the database.” Mechanically this is either ForceSyncCommit() or the nrels > 0 test.
  • Two-phase commit. “The commands supporting two-phase commit, such as PREPARE TRANSACTION, are also always synchronous.”
  • Anything above off on the enum ladder. local, remote_write, on and remote_apply all wait for the local flush; only off skips it.

The practical upshot is that a migration script full of DDL will not speed up under synchronous_commit = off, and neither will an application using XA-style distributed transactions.

Per-Transaction Control

synchronous_commit is registered PGC_USERSET in guc_tables.c and described as “Sets the current transaction’s synchronization level.” The documentation makes the intent unmistakable: “The user can select the commit mode of each transaction, so that it is possible to have both synchronous and asynchronous commit transactions running concurrently. This allows flexible trade-offs between performance and certainty of transaction durability.” And the binding rule: “the behavior for any one transaction is determined by the setting in effect when it commits.”

That last detail is what makes SET LOCAL the right tool — it reverts at the end of the enclosing transaction, so it cannot leak into the next statement on a pooled connection:

-- Cluster default is on. This one batch of telemetry does not need it.
BEGIN;
SET LOCAL synchronous_commit = off;      -- scoped to this transaction only
INSERT INTO events (ts, kind, payload) SELECT ... ;   -- 50k rows
COMMIT;                                   -- returns without waiting for fsync
 
-- The inverse: cluster default is off for throughput, but this one must be durable.
BEGIN;
SET LOCAL synchronous_commit = on;
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
INSERT INTO ledger (account, delta) VALUES (7, -100);
COMMIT;                                   -- blocks until the fsync completes

SET LOCAL “takes effect for only the current transaction. After COMMIT or ROLLBACK, the session-level setting takes effect again” — and note the footgun the same page records: “Issuing this outside of a transaction block emits a warning and otherwise has no effect” (SET reference). In autocommit mode there is no surrounding transaction block, so a bare SET LOCAL synchronous_commit = off; sent on its own line does nothing at all; it must be inside an explicit BEGIN … COMMIT. A plain SET avoids that trap but leaks: on a pooled connection it persists for the session and the next tenant inherits it. Prefer SET LOCAL inside an explicit transaction block.

Because it is USERSET, the setting can also be attached to a role or database with ALTER ROLE telemetry_writer SET synchronous_commit = off or ALTER DATABASE metrics SET synchronous_commit = off, which is often the cleanest deployment: no application change, and the durability policy lives next to the identity that needs it. A connection pooler with separate pools per service achieves the same separation at the connection level.

The documentation’s own framing of when the trade is acceptable is worth keeping verbatim, because it is a better test than any latency number: “asynchronous commit should not be used if the client will take external actions relying on the assumption that the transaction will be remembered. As an example, a bank would certainly not use asynchronous commit for a transaction recording an ATM’s dispensing of cash. But in many scenarios, such as event logging, there is no need for a strong guarantee of this kind.” The question is not “how important is this data?” but “does anything outside the database act on the acknowledgement?” Cash leaving a machine, an email being sent, a payment API being called, a message being published to a broker that has its own durability — all of these break, because the external effect survives while the database row does not.

The Adjacent Knob That Is Not Asynchronous Commit: commit_delay

§28.4 explicitly warns about the confusion: “commit_delay also sounds very similar to asynchronous commit, but it is actually a synchronous commit method (in fact, commit_delay is ignored during an asynchronous commit).”

commit_delay implements deliberate group commit. Inside XLogFlush(), the backend that wins WALWriteLock sleeps before flushing, so that other backends arriving in the meantime have their commit records swept into the same fsync:

if (CommitDelay > 0 && enableFsync &&
    MinimumActiveBackends(CommitSiblings))
{
    pg_usleep(CommitDelay);          /* let followers queue up behind us */
    insertpos = WaitXLogInsertionsToFinish(insertpos);   /* re-check how far we can flush */
}

The parameters, with 18.4 defaults:

ParameterDefaultUnitMeaning
commit_delay0 (no delay)microsecondsHow long the group-commit leader sleeps before initiating the flush. Superuser or SET privilege required.
commit_siblings5transactionsMinimum number of other active transactions required before the delay is taken at all — “a delay is only performed if at least commit_siblings other transactions are active when a flush is about to be initiated.”

Three facts about it are easy to get wrong. It applies to all WAL flushes, not just commits, and only since 9.3: “In PostgreSQL releases prior to 9.3, commit_delay behaved differently and was much less effective … Beginning in PostgreSQL 9.3, the first process that becomes ready to flush waits for the configured interval, while subsequent processes wait only until the leader completes the flush operation.” It is skipped entirely if fsync is off, since there is no flush cost to amortise. And its resolution may be much coarser than the microsecond unit suggests: “on some platforms, the resolution of a sleep request is ten milliseconds, so that any nonzero commit_delay setting between 1 and 10000 microseconds would have the same effect.”

The documentation gives an unusually concrete tuning recipe, which is rare enough to be worth following: measure with pg_test_fsync, and “a value of half of the average time the program reports it takes to flush after a single 8kB write operation is often the most effective setting for commit_delay”. Use higher commit_siblings on fast storage and lower values on high-latency media.

sequenceDiagram
    autonumber
    participant T1 as Txn A (leader)
    participant T2 as Txn B
    participant T3 as Txn C
    participant L as WALWriteLock
    participant D as Disk
    T1->>L: LWLockAcquireOrWait — acquired, becomes leader
    T1->>T1: MinimumActiveBackends(commit_siblings)? yes
    T1->>T1: pg_usleep(commit_delay)
    T2->>L: LWLockAcquireOrWait — blocks (follower)
    T3->>L: LWLockAcquireOrWait — blocks (follower)
    T1->>T1: WaitXLogInsertionsToFinish() — insertpos now covers B and C
    T1->>D: XLogWrite + issue_xlog_fsync — ONE flush for three commits
    T1->>L: release
    Note over T2,T3: on waking, both re-check LogwrtResult.Flush<br/>find their LSN already flushed, and skip their own write

Deliberate group commit under commit_delay. What it shows: the leader trades its own latency (one commit_delay sleep) for the followers’ throughput, and XLogFlush()’s LWLockAcquireOrWait() loop lets followers discover their record was already flushed rather than queueing another fsync. The insight to take: this is the opposite trade from asynchronous commit — it keeps full durability and pays in latency, where asynchronous commit keeps latency and pays in durability. They are not alternatives to each other and can be used together, though commit_delay is ignored for any transaction that actually commits asynchronously.

Even at the default of zero, some grouping happens for free: “it is still possible for a form of group commit to occur, but each group will consist only of sessions that reach the point where they need to flush their commit records during the window in which the previous flush operation (if any) is occurring. At higher client counts a ‘gangway effect’ tends to occur … and thus explicitly setting commit_delay tends to help less.”

Interaction with Synchronous Replication

synchronous_commit is a single parameter doing two jobs: it sets the local flush requirement, and — when synchronous_standby_names is non-empty — it also sets how far the WAL must travel into the standbys. The full treatment of the replication side, including quorum syntax, the wait queue, and the failure modes of a dead synchronous standby, lives in Synchronous Replication in PostgreSQL; what belongs here is only how the local side composes with it.

LevelLocal flush before returning?Standby requirementRelevance to asynchronous commit
offNononeThe subject of this note. Window ≤ 3 × wal_writer_delay.
localYesnone — replication wait explicitly skippedThe “synchronous locally, asynchronous to replicas” escape hatch.
remote_writeYesstandby has write()n the recordSurvives a standby PostgreSQL crash, not a standby OS crash.
on (default)Yesstandby has flushed to durable storageTrue 2-safe replication.
remote_applyYesstandby has flushed and applied itAdds standby query visibility; costs a replay wait.

Two consequences of the composition are worth stating plainly. First, if synchronous_standby_names is empty, remote_apply, remote_write and local are all just on — the documentation says “the only meaningful settings are on and off”. A cluster with the default synchronous_commit = on and no standby names configured is doing nothing but a local fsync. Second, the local flush is never skipped by any level except off, so you cannot ask for “replicate synchronously but don’t fsync locally”; the enum comparison in RecordTransactionCommit() makes that combination unrepresentable. Note also the code ordering in xact.c: the local XLogFlush() and the CLOG update happen before SyncRepWaitForLSN(), so a transaction waiting on a standby is already locally durable and already visible to other backends on the primary.

Failure Modes and Common Misunderstandings

“Async commit means the transaction might not be visible yet.” No. TransactionIdAsyncCommitTree() marks the XID committed in the in-memory CLOG before the client is told, so the rows are visible to every other session immediately. Visibility and durability are independent axes here.

“We lost half a transaction.” Not possible from asynchronous commit. If you observe a partially-applied transaction after a crash, asynchronous commit is not the cause — look at fsync = off, full_page_writes = off, a lying storage device (§28.1 warns about drives that “falsely report a successful write to the kernel”), or application-level non-transactional writes.

“Setting it to off fixed our replication lag.” It reduces WAL flush frequency, not WAL volume. Replica lag driven by volume — the usual case after a checkpoint, thanks to full page images — is unaffected. See Full Page Writes and Torn Page Protection.

“600 ms of loss, so we might lose about 600 ms of transactions.” The bound is on time, so what you lose is everything committed in that window — at 20 000 TPS that is up to 12 000 transactions, not “a few”. Size the RPO in transactions, not milliseconds.

“It’s safe because we have a synchronous standby.” Only if synchronous_commit is not off; off skips the replication wait too. local is the setting that means “durable here, asynchronous to replicas” — and conversely, the standby is what makes off genuinely reasonable on the primary only if you accept that a primary crash plus failover loses the same window.

“Turning it off eliminated our commit latency.” It eliminates the fsync component. If your commits are slow because of lock waits, WALWriteLock contention, or wal_buffers_full evictions, nothing changes; those are visible as wait events in pg_stat_activity and as wal_buffers_full in pg_stat_wal.

Measuring and Choosing

There is no counter that says “transactions currently at risk”, so the exposure has to be derived. The useful primitives, all available in 18.4:

-- How far ahead of the durable point is the WAL right now?
-- The difference is, in bytes, what an immediate crash would discard.
SELECT pg_current_wal_insert_lsn()               AS inserted,
       pg_current_wal_flush_lsn()                AS durable,
       pg_current_wal_insert_lsn()
         - pg_current_wal_flush_lsn()            AS bytes_at_risk;
 
-- What the WAL writer is doing. In 18.4 pg_stat_wal has exactly five columns;
-- the read/sync timing columns moved to pg_stat_io (PG 18 release notes).
SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full FROM pg_stat_wal;
 
-- Flush cost, if track_wal_io_timing is on.
SELECT writes, write_time, fsyncs, fsync_time
FROM pg_stat_io WHERE object = 'wal';

pg_test_fsync is the right tool for the offline half of the decision: it reports the average time for a single WAL flush, which is simultaneously the per-commit latency you are buying back with synchronous_commit = off and the input to the commit_delay recipe above.

The decision itself reduces to three questions. Does anything outside the database act on the commit acknowledgement? If yes, stop — use on. Is the workload commit-rate-bound rather than CPU- or lock-bound? If no, off will not help. Can the business absorb losing up to 3 × wal_writer_delay of acknowledged writes on an unplanned reboot? If yes, apply off at the narrowest possible scope — a role, a database, or a SET LOCAL around the specific transactions — rather than in postgresql.conf, so that the blast radius matches the analysis you actually did.

Two workloads are near-unambiguous wins: bulk loads and ETL, where the whole batch is replayable from source and a crash means re-running it anyway; and high-volume append-only telemetry, where the marginal value of the last 600 ms of rows is approximately zero. Two are near-unambiguous losses: anything with an external side effect, and anything already bottlenecked somewhere other than commit.

See Also

Graph View