COMMIT Returned OK. After the Power Came Back, the Row Was Gone.

Every backend engineer knows the one-line version. You send COMMIT, the database says OK, and the data is safe. That line is true often enough that most teams never test it. It is also a summary of about twelve separate events, in four separate systems, and any one of them can be configured to lie. A commit is a promise. This post is about who makes that promise, what exactly they promise, and which parts of the stack have to keep their own promises for yours to hold.

The post uses PostgreSQL 18 as the main example. Function names and comments come from the REL_18_STABLE source branch. It compares MySQL 8.4 LTS with InnoDB where the comparison teaches something. It goes down through the kernel, the filesystem, the block layer and the drive, because the database's fsync() call is only a request. Then it comes back up to the application, because the most common "lost commit" in production never touched a disk at all. The incidents are composites. Each one is a pattern that database and SRE teams report repeatedly, with the details changed. Every claim about PostgreSQL, MySQL, Linux or a vendor comes from that project's own documentation or source, linked at the end.

The row that was paid for and did not exist

A payments service runs on PostgreSQL on a single virtual machine in a colocation rack. At 02:14 a customer pays ₹4,200. The service runs one transaction: insert the payment row, update the order status, COMMIT. PostgreSQL returns COMMIT. The service returns HTTP 200 and the app shows a green tick. At 02:14:09 the rack loses power. A failed transfer switch, the report says later. The machine comes back at 02:21. PostgreSQL logs database system was not properly shut down; automatic recovery in progress, replays WAL, and opens for connections. The support queue fills by 09:00. The customer has a bank SMS and a screenshot. The payments table has no row.

The first reaction in the incident channel is the wrong one: "PostgreSQL lost a committed transaction." It might have. But that sentence hides six different questions, and the investigation has to separate them before it can answer any of them.

1. Did the transaction commit at all? The service logged a 200. Did it log the database's COMMIT reply, or did it log the end of a function that swallowed an exception?
2. Was the commit record flushed to durable storage before the reply? That is what synchronous_commit decides. If someone set it to off, PostgreSQL replied before the WAL reached the disk, by design.
3. Did the kernel and the drive honor the flush? PostgreSQL asked for fdatasync(). Whether the bytes left the drive's volatile cache before the power died is a separate question.
4. Was the server configured to skip the flush entirely? fsync = off exists. So do hypervisor disk modes that ignore the guest's flush requests.
5. Is the data gone, or just not where the service looked? A failover to a lagging replica, or a restore from a backup, can remove rows that the old primary did commit.
6. Did the application mistake "I did not get a reply" for "it committed", or the reverse? A connection that drops during COMMIT leaves the client with no information. Some retry layers then make a decision for it.

Each question points at a different layer, and each layer has its own definition of "done". The rest of this post walks the row through those layers, so that when this happens to you, you know which question you are answering.

From INSERT to a durable byte

Here is one transaction on PostgreSQL 18, with every step that matters for durability. The sequence diagram shows the default path. The dashed box is the part that synchronous_commit controls.

Walk the twelve steps. At each one, ask three things: what state exists, what has actually reached durable storage, and what failure can still remove the row.

The commit path in the source

The function that decides what COMMIT promises is RecordTransactionCommit() in src/backend/access/transam/xact.c. Stripped to the durability decision, it reads like this in REL_18_STABLE:

/* xact.c, RecordTransactionCommit(), simplified */
wrote_xlog = (XactLastRecEnd != 0);

if (!markXidCommitted)
{
    /* no XID: we neither can, nor do we want to write a COMMIT record */
    if (!wrote_xlog)
        goto cleanup;
}
else
{
    XactLogCommitRecord(...);   /* builds the record, calls XLogInsert() */
}

if ((wrote_xlog && markXidCommitted &&
     synchronous_commit > SYNCHRONOUS_COMMIT_OFF) ||
    forceSyncCommit || nrels > 0)
{
    XLogFlush(XactLastRecEnd);               /* wait for WAL to reach disk */
    TransactionIdCommitTree(xid, nchildren, children);   /* now mark pg_xact */
}
else
{
    XLogSetAsyncXactLSN(XactLastRecEnd);     /* let the WAL writer do it */
    TransactionIdAsyncCommitTree(xid, nchildren, children, XactLastRecEnd);
}

if (wrote_xlog && markXidCommitted)
    SyncRepWaitForLSN(XactLastRecEnd, true); /* synchronous replication */

Three details in this function explain most of the behavior you see in production.

• A transaction that wrote no WAL does not flush. A read-only transaction, or one that only touched temporary or unlogged tables, skips the whole path. Its COMMIT costs nothing and promises nothing about disk, because there is nothing to promise.
• The order is flush first, then pg_xact. The source comment says why: "We must not immediately update the CLOG, since we didn't flush the XLOG. Instead, we store the LSN up to which the XLOG must be flushed before the CLOG may be updated." A transaction becomes visible to others only after its commit record is safe, in the synchronous case. In the asynchronous case the commit status carries the LSN, so a reader can still check whether the record has reached disk.
• Some commits are forced synchronous. nrels > 0 means the transaction will delete relation files. The comment explains: "we might delete the files before the COMMIT record is flushed to disk." So DROP TABLE waits for the flush even with synchronous_commit = off. PREPARE TRANSACTION is also always synchronous.

XLogFlush() in xlog.c does the waiting. It takes the WAL insert lock, checks whether someone else already flushed past the requested LSN, and if not calls XLogWrite(). XLogWrite() copies full WAL buffer pages into the segment file with pg_pwrite(), then calls issue_xlog_fsync(). That function is a switch on wal_sync_method. It calls fdatasync() on Linux by default, or fsync(). It calls nothing when PostgreSQL opened the file with O_DSYNC or O_SYNC, because then the write itself was synchronous. If the sync call fails, the function raises PANIC. Part 4 explains why a panic is the right response.

One more comment from XLogFlush() matters for performance, and we return to it in Part 11:

Three LSNs, three different places

PostgreSQL tracks where the WAL is with three positions, and the gap between them is exactly the gap between "PostgreSQL has it" and "the disk has it".

The documentation defines them precisely. The insert location "is the 'logical' end of the write-ahead log at any instant, while the write location is the end of what has actually been written out from the server's internal buffers, and the flush location is the last location known to be written to durable storage." In the source these are LogwrtResult.Write and LogwrtResult.Flush, declared as "last byte + 1 written out" and "last byte + 1 flushed".

You can watch the gap on a live system:

SELECT pg_current_wal_insert_lsn() AS insert_lsn,
       pg_current_wal_lsn()        AS write_lsn,
       pg_current_wal_flush_lsn()  AS flush_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), pg_current_wal_flush_lsn()) AS bytes_written_not_flushed;

On an idle server with synchronous_commit = on the three values are usually equal. Under load with synchronous_commit = off, the last column shows you, in bytes, the commits that clients have been told about and that a power cut would remove.

The five values of synchronous_commit

The parameter "specifies how much WAL processing must complete before the database server returns a 'success' indication to the client." It has five values, but only two of them mean anything on a standalone server. The docs are explicit: "If synchronous_standby_names is empty, the only meaningful settings are on and off; remote_apply, remote_write and local all provide the same local synchronization level as on."

Two facts about off are easy to get wrong in both directions. First, the loss window is bounded. The WAL writer wakes every wal_writer_delay (default 200 ms) and the docs explain that "the actual maximum duration of the risk window is three times wal_writer_delay because the WAL writer is designed to favor writing whole pages at a time during busy periods." Second, the risk is loss, not corruption: "an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly." Transactions replay in commit order, so you cannot end with transaction B but not transaction A that B depended on.

The setting is per session and per transaction. That makes the right use obvious. Keep the server default at on, and relax it for the transactions that can afford it:

BEGIN;
SET LOCAL synchronous_commit TO off;   -- this transaction only
INSERT INTO device_telemetry (device_id, ts, reading) VALUES ($1, $2, $3);
COMMIT;                                -- returns before the WAL flush

A telemetry reading the device will resend in ten seconds can take that trade. A payment cannot.

fsync, full_page_writes and the thing each one protects

Three settings get confused with each other, and they protect against three different failures.

The reason fsync = off corrupts while synchronous_commit = off only loses is the checkpoint. A checkpoint writes a record that says "every data page change before this point is on disk, so recovery can start here." With fsync = off the checkpointer never flushed those pages. It still writes the record. After a crash, recovery starts from a point where the data files do not match the claim. PostgreSQL already recycled the WAL that could have fixed them.

full_page_writes protects against a narrower problem and only in one place. The documentation describes the mechanism: PostgreSQL "writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint." Replay then overwrites a possibly torn page with a known-good image before applying later changes. The reliability chapter lists what this does not cover. pg_xact, pg_subtrans, pg_multixact and similar internal structures are "not directly checksummed, nor are pages protected by full page writes." A battery-backed RAID controller does not remove the need either, "unless they guarantee that data is written to the BBU as full (8kB) pages."

Checkpoints and what redo does after a crash

A checkpoint is "a point in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint." The checkpointer writes every dirty page in shared_buffers, fsyncs the files, and then writes a checkpoint record to WAL. By default this happens every checkpoint_timeout of 5 minutes or whenever max_wal_size of 1 GB is about to be exceeded.

Crash recovery then has one job. It reads pg_control to find the latest checkpoint, goes to that checkpoint's REDO location, and replays every WAL record from there to the end of valid WAL. The server log shows it:

LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 2/A3F0B2C8
LOG:  invalid record length at 2/A4123F80: expected at least 24, got 0
LOG:  redo done at 2/A4123F48 system usage: CPU: user: 0.21 s, system: 0.09 s, elapsed: 0.84 s
LOG:  last completed transaction was at log time 2026-10-23 02:14:08.912+05:30
LOG:  checkpoint starting: end-of-recovery immediate wait

The "invalid record length … got 0" line is not an error. It is how the reader finds the end of the WAL. The last record was followed by zeroed space, so recovery stops there. The "last completed transaction" line is the first thing to compare with your incident timeline. If the missing payment committed at 02:14:08.95 and recovery's last transaction is at 02:14:08.91, something between the commit record and durable storage did not happen.

Redo treats committed and uncommitted work the same way while replaying. It applies every heap change it finds, including changes from transactions that never committed. What makes an uncommitted transaction disappear is not the absence of its data. It is the absence of its commit record. After redo, pg_xact has no commit for that XID, so every tuple it wrote fails the visibility check and vacuum removes it later. That is why the commit record, and only the commit record, is the thing the flush must cover.

fsync is a request, not a receipt

When fdatasync() returns 0, PostgreSQL believes the commit record is durable and tells the client so. Everything below that call is outside PostgreSQL's control. The chain has five levels, and a write can be "complete" at any one of them while still being volatile.

The Linux kernel's own block-layer documentation describes why level 4 is a trap: "Many storage devices, especially in the consumer market, come with volatile write back caches. That means the devices signal I/O completion to the operating system before data actually has hit the non-volatile storage."

What fsync and fdatasync promise

The fsync(2) man page defines the contract. The call "transfers ('flushes') all modified in-core data of (i.e., modified buffer cache pages for) the file referred to by the file descriptor fd to the disk device … so that all changed information can be retrieved even if the system crashes or is rebooted. This includes writing through or flushing a disk cache if present." The last sentence is the important one, and it has a history. The same page notes that "the fsync() implementations in older kernels and lesser used filesystems do not know how to flush disk caches." On a modern ext4 or XFS mount, fsync() does ask the device to flush. On an exotic filesystem, a network filesystem, or a container volume driver, check before you trust it.

fdatasync() is the cheaper sibling. It "does not flush modified metadata unless that metadata is needed in order to allow a subsequent data retrieval to be correctly handled." A changed file size counts as needed metadata. A changed modification time does not. PostgreSQL preallocates WAL segments at their full 16 MB, so writes into them change no size, and fdatasync() can skip the inode update. That is why it is the Linux default for wal_sync_method.

There is one more rule most application code forgets. "Calling fsync() does not necessarily ensure that the entry in the directory containing the file has also reached disk. For that an explicit fsync() on a file descriptor for the directory is also needed." PostgreSQL fsyncs the pg_wal directory when it creates or renames segments. If you write your own durable log, you have to do the same.

The journal, barriers and FUA

Between fdatasync() and the drive sits the filesystem. On ext4 in the default data=ordered mode, the kernel documentation says "all data are forced directly out to the main file system prior to its metadata being committed to the journal." The filesystem then has to make sure the device does not reorder those writes behind its cache. It does that with two request flags that the block layer documents:

• REQ_PREFLUSH "will make sure the volatile cache of the storage device has been flushed before the actual I/O operation is started."
• REQ_FUA, Forced Unit Access, "will make sure that I/O completion for this request is only signaled after the data has been committed to non-volatile storage."

Old documentation calls these "barriers". The kernel removed true barriers in 2.6.37 and replaced them with explicit flushes, but the ext4 mount option kept the name. barrier=1 is the default and "enables the use of write barriers in the jbd code." nobarrier turns them off, and some older tuning guides still recommend it for speed. On XFS the option is gone. The XFS maintainers deprecated nobarrier in 4.10 with the comment "We always perform integrity operations now, so these mount options don't do anything" and removed it in 4.19. If an ext4 PostgreSQL volume is mounted with nobarrier, every fdatasync() returns after the writes reach the device cache, and nothing asks the device to flush it.

The driver turns the flag into a device command. For NVMe it is the Flush command, which "requests that the contents of volatile write cache be made non-volatile." Whether the device has such a cache is in its identify data, the vwc field, and the host can enable or disable it through feature 0x06, Volatile Write Cache.

Drives that say yes

The last link is firmware, and firmware is not under your control. The PostgreSQL reliability chapter carries a long-standing warning about this: "Consumer-grade IDE and SATA drives are particularly likely to have write-back caches that will not survive a power failure. Many solid-state drives (SSD) also have volatile write-back caches." And: "If you use SSDs, be aware that many of these do not honor cache flush commands by default."

The SQLite project documents the same problem from the other side of the database world: "Disk drives will report that content is safely on persistent media as soon as it reaches the track buffer and before actually being written to oxide." Their conclusion applies to every database: "SQLite must believe whatever the operating system and hardware tell it about the status of sync requests. There is no way for SQLite to detect that either is lying."

The research matches. In the FAST '13 study "Understanding the Robustness of SSDs under Power Fault", the authors cut power to fifteen SSDs under write load and found that "thirteen out of the fifteen tested SSD devices exhibit surprising failure behaviors under power faults, including bit corruption, shorn writes, unserializable writes, metadata corruption, and total device failure." Drives have improved since 2013. The method of finding out whether yours did has not changed: pull the power and compare. The PostgreSQL wiki's Reliable Writes page keeps a list of known problem hardware. It records that first- and second-generation Intel SSDs "will write unsafely" and that the later 320 and 710 series fixed it by adding "a capacitor to provide battery-backup for flushing the drive cache when power drops."

The hardware answer is power-loss protection, PLP. Enterprise and data-center SSDs carry capacitors sized to write the cache to flash when power disappears. Intel's data sheet for the DC S3500 describes it as detecting power loss, after which "the drive saves all cached data in the process of being written prior to shutting down." With PLP, "acknowledged into cache" and "durable" are the same thing, and flushes become cheap. Without it, the drive's honesty about flushes is the whole guarantee.

# SATA/SAS: is the drive write cache on? (the * means enabled)
sudo hdparm -I /dev/sda | grep -i 'write cache'
# turn it off at the drive, if the drive honours it. Slower, but no volatile cache to lose.
sudo hdparm -W 0 /dev/sda

# NVMe: does the controller report a volatile write cache, and is it enabled?
sudo nvme id-ctrl /dev/nvme0 | grep -i vwc
sudo nvme get-feature /dev/nvme0 -f 0x6 -H

# What the kernel believes about the device cache behind a block device
cat /sys/block/nvme0n1/queue/write_cache     # "write back" or "write through"

Each of these tells you whether a cache exists and whether flushes are requested. None of them tells you whether the firmware honors the flush. Only a pull-the-plug test does. Part 9 describes one.

When fsync fails: the 2018 surprise

There is a failure mode worse than a lying drive, and it lives in the kernel. In 2018 Craig Ringer reported to the PostgreSQL hackers list what LWN then described in "PostgreSQL's fsync() surprise". PostgreSQL assumed that "a successful call to fsync() indicates that all data written since the last successful call" had reached storage. Linux did not promise that. When writeback hit an I/O error, filesystems typically responded by "discarding the data in the affected pages and marking them as being clean." The next fsync() had nothing dirty to write, so it returned success. The data was gone. Worse, on kernels before 4.13 the error was reported only to the first process that asked, and "the checkpointer will not see any errors that happened before it opened the file."

Both sides changed. Linux 4.13 added errseq_t so that, as the man page now says, "errors from write-back will be reported to all file descriptors that might have written the data which triggered the error." PostgreSQL stopped retrying. The release notes for 11.2 and the other February 2019 minor releases say: "By default, panic instead of retrying after fsync() failure, to avoid possible data corruption." The reasoning follows: "If we reissue the fsync() request it will succeed, but in fact the data has been lost, so continuing risks database corruption. By raising a panic condition instead, we can replay from WAL, which may contain the only remaining copy of the data." The behavior is controlled by data_sync_retry, which defaults to off, meaning panic. The docs tell you to set it to on "only after investigating the operating system's treatment of buffered data in case of write-back failure."

The USENIX ATC '20 paper "Can Applications Recover from fsync Failures?" tested the aftermath across filesystems and found that "all three file systems mark pages clean after fsync fails, rendering techniques such as application-level retry ineffective." On ext4 and XFS the clean pages still hold the new data, which the kernel will never write. On Btrfs they hold the old data. A database cannot tell the difference from user space. A crash and a WAL replay can.

Virtual machines and the cloud

Most databases now run with at least one more layer between fdatasync() and the hardware: a hypervisor, a cloud block service, or both. Each has a setting that decides what a guest flush means.

QEMU and KVM are the common case for self-hosted VMs. The guest's flush becomes a request to QEMU, and QEMU's cache= mode decides what to do with it. The default, writeback, "will report data writes as completed as soon as the data is present in the host page cache. This is safe as long as your guest OS makes sure to correctly flush disk caches where needed." PostgreSQL does flush, so writeback is fine if the host does not lose power. cache=none bypasses the host page cache and still honors flushes. cache=unsafe is the one to look for in an incident. QEMU's documentation: "This option tells QEMU that it never needs to write any data to the disk but can instead keep things in cache. If anything goes wrong, like your host losing power, the disk storage getting disconnected accidentally, etc. your image will most probably be rendered unusable." The libvirt documentation says it more directly: with unsafe, "sync requests from guest are ignored." A guest with fsync = on, synchronous_commit = on and a flawless drive loses commits on cache=unsafe, and nothing inside the guest can detect it.

# On the KVM host: what cache mode does this guest's disk use?
virsh dumpxml payments-db-01 | grep -A3 '<driver'
# look for cache="unsafe" or cache="writeback"; also check the -snapshot flag,
# which QEMU documents as always using unsafe caching

Cloud block storage shifts the question but does not remove it. The published numbers are about device failure, not about flushes. Amazon EBS documents "99.8% - 99.9% durability (0.1% - 0.2% annual failure rate)" for gp3 and most volume types, and "99.999% durability" for io2 Block Express, with each volume "automatically replicated within its Availability Zone." Azure says that LRS managed disks "synchronously write data to three copies in a single data center." Google documents design durability targets for Persistent Disk by type. None of the pages we checked states, in so many words, that a write the service has acknowledged is durable against a power event in the facility. Treat that as an undocumented property of the service, not as a guarantee you have read. EC2 instance store, by contrast, is documented clearly: the data "does not persist if the instance is stopped, hibernated, or terminated", and a failed underlying disk takes the data with it. A WAL on instance store is durable against reboots and nothing else.

Aurora is the extreme case of moving durability out of the database. The SIGMOD 2017 paper describes "replicating each data item 6 ways across 3 AZs" with "a write quorum of 4/6", and states that "the only writes that cross the network are redo log records. No pages are ever written from the database tier, not for background writes, not for checkpointing, and not for cache eviction." The commit is durable when four storage nodes in three zones have the log record. Everything in Part 3 about checkpoints and dirty pages still exists, but inside the storage service, not inside the instance you can see.

What to investigate for each kind of failure

"The server crashed" is not one event. Each kind of crash removes a different set of layers from Part 2, so each one needs a different first question.

The commit nobody saw

The last row deserves its own section because it is the most common "lost commit" in production, and no disk is involved. The client sends COMMIT. The backend flushes WAL, marks pg_xact, releases locks and writes CommandComplete to the socket. The packet never arrives. A load balancer idle-timeout fired, a pod was rescheduled, a Wi-Fi handoff happened. The client sees a closed connection or a timeout and has to decide what the database did.

It cannot know. The transaction may have committed. It may have been rolled back because the socket closed before the backend read COMMIT. Both are consistent with what the client observed. A retry layer that treats a timeout as failure then does one of two wrong things. It retries and inserts the payment twice, or it reports failure to a customer whose money has moved. We covered the application-side design for this in the idempotency keys post. Three database-side tools help too.

Ask the database what happened. PostgreSQL has a function for exactly this case. Capture the transaction's ID before COMMIT, and after a lost connection ask on a new connection:

-- inside the transaction, before COMMIT; store this with the request
SELECT pg_current_xact_id();          --> 7391204

-- later, on a fresh connection, after the socket died during COMMIT
SELECT pg_xact_status('7391204'::xid8);   --> 'committed' | 'aborted' | 'in progress' | NULL

The documentation describes the use: "Applications might use this function, for example, to determine whether their transaction committed or aborted after the application and database server become disconnected while a COMMIT is in progress." Two limits. pg_current_xact_id() assigns an XID if the transaction has none, which costs a little for transactions that would otherwise stay read-only. And the status is kept only while the system retains it. If it has been discarded, the result is NULL, which means "ask the data, not the transaction."

Make the retry harmless. A unique constraint on the business key, (order_id, attempt_id) or an idempotency key column, turns a duplicate insert into a 23505 error the client can treat as success. INSERT … ON CONFLICT DO NOTHING RETURNING id makes the second attempt return the first row's identity.

Do not let the pool guess. Some connection poolers and ORM retry wrappers resend a statement on a broken connection. That is safe for a SELECT and dangerous for a COMMIT. Check what your driver does on a socket error during commit. The correct behavior is to raise an error that says "outcome unknown", not "failed".

It does not matter for durability. The commit record was flushed before the backend tried to reply, so the row survives whether or not the reply arrived. It matters only for the client's knowledge. Durability and acknowledgment are separate properties, and the matrix above separates them in every row.

Redo log write, redo log flush, and the binary log

InnoDB solves the same problem with the same shape: a sequential log that must reach disk before the commit returns, and data pages that follow later. The vocabulary and the knobs differ, and MySQL adds a second log that PostgreSQL does not have.

The redo log lives in the #innodb_redo directory as 32 files named #ib_redoN, sized together by innodb_redo_log_capacity (default 100 MB, since 8.0.30). Changes go first to the log buffer, innodb_log_buffer_size, default 64 MB. From MySQL 8.0 on, dedicated threads move them further. Paweł Olchawa's design post on the MySQL Server blog describes two of them. A log_writer thread "keeps writing the log buffer to the OS page cache" and advances write_lsn. A log_flusher thread is "responsible for reading write_lsn, invoking fsync() calls and updating flushed_to_disk_lsn." Two notifier threads wake committing transactions when their LSN has been written or flushed. The two LSNs are exactly PostgreSQL's write and flush positions, and SHOW ENGINE INNODB STATUS prints both as Log written up to and Log flushed up to.

What a committing transaction waits for is innodb_flush_log_at_trx_commit. The documentation is precise about each value.

The manual adds a caveat PostgreSQL users should recognize: "For settings 0 and 2, once-per-second flushing is not 100% guaranteed." DDL and internal activity flush more often, scheduling can flush less often, and the window moves with it. It also repeats the hardware warning in its own words: "Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not."

The binary log is a second commit

With binary logging enabled, which every replicated or point-in-time-recoverable MySQL has, a commit is a two-phase commit between InnoDB and the binary log. The manual: "Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done." InnoDB prepares the transaction, the server writes the transaction to the binary log, and only then does InnoDB commit. "InnoDB support for two-phase commit in XA transactions is always enabled" in 8.4, since the old innodb_support_xa switch was removed in 8.0.0.

That second log has its own flush setting, sync_binlog. With sync_binlog = 1, the server syncs the binary log "before transactions are committed", and the manual explains the recovery property this buys: "In the event of a power failure or operating system crash, transactions that are missing from the binary log are only in a prepared state. This permits the automatic recovery routine to roll back the transactions, which guarantees that no transaction is lost from the binary log." With sync_binlog = 0, the server leaves it to the OS, and "it is possible that the server has committed transactions that have not been synchronized to the binary log." Replicas then diverge from the source without any error.

Recovery ties the two logs together. At restart after a crash, "the MySQL server scans the latest binary log file to collect transaction xid values and calculate the last valid position in the binary log file. The MySQL server then tells InnoDB to complete any prepared transactions that were successfully written to the binary log, and truncates the binary log to the last valid position." If the binary log is shorter than InnoDB expected, the server prints The binary log file_name is shorter than its expected size, and the manual says this "should not happen if sync_binlog=1 and the disk/file system do an actual sync when they are requested to (some do not)."

Doublewrite instead of full-page writes

InnoDB attacks the torn-page problem from the other direction. PostgreSQL puts a full page image into the WAL on the first change after a checkpoint. InnoDB writes every flushed page twice: first to the doublewrite buffer files, #ib_16384_N.dblwr, in a large sequential chunk with a single fsync(), then to its real position. "If there is an operating system, storage subsystem, or unexpected mysqld process exit in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery." Since 8.0.30, innodb_doublewrite also accepts DETECT_ONLY. That mode writes only metadata, so recovery detects a torn page but cannot repair it. The value OFF disables the buffer, and the manual suggests it only "if you are more concerned with performance than data integrity, as may be the case when performing benchmarks."

Side by side

That last row is a real philosophical difference. PostgreSQL's documentation says that with a crashed synchronous standby, "transaction commits may never be completed" until an operator changes the configuration. MySQL's says that after the timeout "the source reverts to asynchronous replication." One chooses availability, the other chooses the guarantee. Neither is wrong. You have to know which one you are running.

These are composites. The pattern in each is one that database operators report repeatedly on mailing lists, in post-mortems and in support tickets. We invented the numbers and names. We did not invent the mechanisms.

-- Where does the effective value come from?
SELECT name, setting, source, sourcefile, sourceline
FROM   pg_settings
WHERE  name IN ('synchronous_commit', 'fsync', 'full_page_writes',
                'wal_sync_method', 'data_sync_retry', 'synchronous_standby_names');

-- Per-role and per-database overrides
SELECT r.rolname, d.datname, s.setconfig
FROM   pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

SELECT application_name, sync_state, state,
       pg_wal_lsn_diff(pg_current_wal_flush_lsn(), flush_lsn) AS bytes_behind,
       flush_lag, replay_lag
FROM   pg_stat_replication;

Ten myths, and what is true

A proposed experiment

This is a procedure, not a report. We describe what to run and what each outcome would mean. We did not run it for this post, so there are no numbers below. Distrust any article that gives you numbers for this without describing the hardware, the hypervisor mode and the crash method.

Setup. One KVM guest with PostgreSQL 18 on a dedicated virtual disk. A second machine, the recorder, on the same network. The recorder matters. If the client runs inside the guest, its record of which commits were acknowledged dies with the guest. Brad Fitzpatrick's diskchecker.pl from 2005 used the same structure for the same reason. The machine under test reports every synced block to another machine over the network. The comparison happens after the power comes back.

The client. A loop on the recorder that inserts marker rows and logs each acknowledgment locally with a timestamp before it sends the next one:

CREATE TABLE durability_probe (
  seq        bigint PRIMARY KEY,
  xid        xid8   NOT NULL,
  client_ts  timestamptz NOT NULL
);

-- each iteration, one transaction:
BEGIN;
INSERT INTO durability_probe (seq, xid, client_ts)
VALUES ($seq, pg_current_xact_id(), clock_timestamp());
COMMIT;
-- on COMMIT reply: append "$seq acked" to the local log
-- on error or timeout: append "$seq unknown" to the local log

The crash. Three different crashes, run separately, because they remove different layers:

1. kill -9 of the postmaster inside the guest. A PostgreSQL crash. The page cache survives.
2. echo b > /proc/sysrq-trigger inside the guest. An immediate reboot without sync. The guest page cache is gone. The host page cache is not, so with cache=writeback this still does not test the device.
3. virsh destroy from the host with the disk in cache=none or cache=directsync, so the host page cache is not in the path. This is the closest a VM gets to a power cut without pulling a cable. For the real thing, use a physical machine and pull the cable.

The comparison. After recovery, join the recorder's log with the table:

What to expect. With synchronous_commit = on, zero rows in the "acked, missing" cell for all three crash types, if the storage path is honest. With off, zero for crash type 1 in most runs, because the WAL writer writes to the page cache within wal_writer_delay and the page cache survives. With off and crash types 2 or 3, a small number of rows in that cell, corresponding to the last few hundred milliseconds. If you see "acked, missing" with on, stop and find out which layer lied before you trust that machine with anything.

The checklist

Run these in order. Each step either explains the loss or rules out a layer.

1. Check that the transaction committed, from the server's side

# Recovery messages around the crash: when did redo start and end, and what was the last commit?
grep -E 'not properly shut down|redo (starts|done)|last completed transaction|consistent recovery' \
     /var/log/postgresql/postgresql-*.log

# Control file: the checkpoint recovery started from
pg_controldata "$PGDATA" | grep -E 'cluster state|checkpoint location|REDO location|REDO WAL file'

# Does a commit record for the suspected XID exist in the surviving WAL?
# -r Transaction limits output to commit/abort records, -x filters by XID
pg_waldump -r Transaction -x 7391204 -p "$PGDATA/pg_wal" 000000010000000200000A3

If pg_waldump finds the COMMIT record, the transaction was durable and the row's absence has another cause: a later delete, a wrong database, a replica. If it does not, and the application has the COMMIT reply logged, a commit was acknowledged and lost. Continue.

2. Check what the server was actually configured to wait for

SELECT name, setting, source, sourcefile, sourceline
FROM   pg_settings
WHERE  name IN ('synchronous_commit', 'fsync', 'full_page_writes', 'wal_sync_method',
                'wal_writer_delay', 'data_sync_retry', 'synchronous_standby_names');

SELECT r.rolname, d.datname, s.setconfig           -- per-role / per-database overrides
FROM   pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

SELECT * FROM pg_stat_io WHERE object = 'wal';      -- PG 18: WAL writes and fsyncs, with timing
                                                    -- if track_wal_io_timing is on

Then check the application and pooler for SET synchronous_commit in connection setup or ORM options. If the effective value for the lost transaction was off, you have your answer, and the fix is case 1 in Part 7.

3. Check the storage path for a layer that does not honor flushes

# Mount options on the WAL volume. Look for nobarrier (ext4) or data=writeback.
findmnt -T "$PGDATA/pg_wal"

# Kernel I/O errors before the crash. Any hit here changes the investigation.
journalctl -k -b -1 | grep -iE 'I/O error|blk_update_request|nvme.*(timeout|reset)|ata[0-9]+.*(failed|error)'

# Device write cache state
cat /sys/block/*/queue/write_cache
sudo hdparm -I /dev/sda | grep -i 'write cache'
sudo nvme id-ctrl /dev/nvme0 | grep -i vwc

# If this is a VM, on the host:
virsh dumpxml "$VM" | grep -A3 '<driver'       # cache="unsafe"? -snapshot?

# How fast is a flush on this host? Compare with a machine you trust.
pg_test_fsync -f "$PGDATA/pg_wal/test.sync"

4. Check replication and failover

-- On the current primary
SELECT application_name, sync_state, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM   pg_stat_replication;

-- On a standby: what has it received and flushed?
SELECT status, received_lsn, latest_end_lsn, flushed_lsn FROM pg_stat_wal_receiver;

If a failover happened, compare the old primary's last flushed LSN with the new primary's position at promotion. The difference is the lost window. If sync_state was async for every standby, the loss is explained.

5. Check the application's commit handling

• Does the service log the database's COMMIT reply, or the end of a function?
• What does the driver do when the socket closes during COMMIT? Error, retry, or silent success?
• Does the pooler or ORM retry writes on connection errors?
• Is there a unique constraint that would have turned a wrong retry into an error?

6. For MySQL, the equivalents

SHOW GLOBAL VARIABLES
WHERE Variable_name IN ('innodb_flush_log_at_trx_commit', 'sync_binlog',
                        'innodb_flush_log_at_timeout', 'innodb_flush_method',
                        'innodb_doublewrite', 'rpl_semi_sync_source_wait_point');

SHOW ENGINE INNODB STATUS\G
-- in the LOG section:
--   Log sequence number   = latest LSN assigned in the log buffer
--   Log written up to     = written to the OS (write_lsn)
--   Log flushed up to     = fsync'd (flushed_to_disk_lsn)
--   Last checkpoint at    = where crash recovery would start

# MySQL error log, around the crash
grep -E 'Starting crash recovery|was not shutdown normally|shorter than its expected size|Crash recovery finished' \
     /var/log/mysql/error.log

What the flush costs, and what hides the cost

A synchronous commit pays one WAL flush. On a drive with power-loss protection, a flush is a short write to protected cache and completes in tens of microseconds. On a consumer SSD that honors flushes, it can be hundreds of microseconds to milliseconds, because the drive has to move the cache contents to flash. On network block storage it is a round trip plus whatever the service does. On a spinning disk it is a seek. Commit latency has a floor at that number, and nothing in PostgreSQL's configuration lowers the floor except not waiting.

Throughput is a different story, because flushes are shared. XLogFlush() flushes "as far as we can", so a backend that acquires the flush lock writes and syncs every commit record that arrived while it waited. The documentation describes the effect: "when the server is processing many small concurrent transactions, one fsync of the WAL file may suffice to commit many transactions." That is group commit, and it happens with no configuration. At higher concurrency the docs describe a "gangway effect" where followers pile behind the leader and each flush carries more commits. The result is that commits per second scale with concurrency until the drive's flush rate, not the per-commit latency, becomes the limit.

commit_delay makes the grouping deliberate. It "adds a time delay before a WAL flush is initiated", in microseconds, so that more backends can join the group. It applies only when at least commit_siblings other transactions are active (default five). On storage where a flush is slow, a delay of a few hundred microseconds can raise throughput. On a PLP drive where the flush is already fast, the same delay only adds latency. The docs say to measure with pg_test_fsync and start with half the reported flush time. The setting does nothing when synchronous_commit = off, because no one is waiting.

The WAL writer handles the asynchronous side. With synchronous_commit = off, each commit returns at once and the WAL writer flushes every wal_writer_delay or every wal_writer_flush_after bytes, default 1 MB. One flush covers everything since the last one, so under load the flush count drops to a few per second regardless of commit rate. That is why off helps most for workloads of many tiny transactions on storage with slow flushes. It helps least for a few large transactions on fast storage, where the flush was never the bottleneck.

Tail latency is where the trade-off becomes visible. Every synchronous commit inherits the flush's p99, and storage p99 is often ten times the median. A write-heavy service that reports p99 commit latency of 20 ms on cloud storage is usually reporting the storage's flush tail. Options, cheapest first: move the WAL to a volume with a better flush tail, relax synchronous_commit for the transactions that can afford it, add a synchronous standby only where the business needs it, and accept that the remaining tail is the cost of the guarantee. Measure before and after each. The claim that off is "always faster" and the claim that on is "too slow for high throughput" are both wrong often enough that neither deserves to be assumed.

Seven questions, answered directly

1. What does a successful commit mean? The database inserted a commit record into its log. Unless configured not to, it then waited until the operating system reported that the log through that record had reached the device. It then marked the transaction committed and replied. It means nothing about data pages, and it means "durable" only to the extent that every layer below honored the flush.
2. Why can data pages remain unwritten after a commit? Because the log is the durable copy. Redo replays the log from the last checkpoint and rebuilds every page change, so the page itself can stay dirty in memory until the checkpointer gets to it. Writing the page at commit time would turn one sequential flush into many random writes for no additional safety.
3. When can an acknowledged transaction be lost? When the reply was sent before the flush (synchronous_commit = off, innodb_flush_log_at_trx_commit 0 or 2, sync_binlog = 0). When the flush was requested and a layer did not honor it (nobarrier, cache=unsafe, volatile drive cache with dishonest firmware). When the flush failed and the error was swallowed (kernels and PostgreSQL versions before 2019). When the durable copy was on a device that died and the replica that took over was behind.
4. What does the database rely on the OS and the device for? Three things. fdatasync() returns only after the data, and the metadata needed to read it, are on non-volatile storage. A write-back error reaches the caller, and the kernel does not silently discard the data. Writes of a page are either atomic or detectable. PostgreSQL trusts all three, and compensates for the third with full-page writes and checksums.
5. What changes with synchronous replication? The commit also waits for one or more standbys to report that they wrote, flushed or applied the record, depending on the level. The guarantee becomes "lost only if the primary and all synchronous standbys lose their storage." The cost is that a missing standby blocks commits in PostgreSQL, or triggers a fallback to asynchronous in MySQL's semisynchronous mode.
6. How should an application handle an ambiguous commit? As ambiguous. Not as failed, not as succeeded. Ask the database with pg_xact_status() or a lookup on the business key. Design the write so that a wrong guess is an error rather than a duplicate, with unique constraints and idempotency keys.
7. What should you verify before trusting a durability configuration? The effective settings from pg_settings, including role and database overrides. The mount options. The hypervisor cache mode. The drive's cache and protection status. A pg_test_fsync result that is consistent with the hardware. The replication mode and the lag. And at least once, a power-pull test on the hardware class, with the recorder on another machine.

Rules to carry away

1. A commit is durable when its log record is flushed, not when its page is written. Design, debug and monitor around the WAL flush position.
2. Keep synchronous_commit = on as the server default. Relax it per transaction with SET LOCAL, for data you can afford to lose and can regenerate.
3. Never run fsync = off or full_page_writes = off in production. The first corrupts after any crash. The second corrupts after a torn write. Neither failure is recoverable from WAL.
4. In MySQL, both knobs must be 1. innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1. One of them alone leaves a window in the other log.
5. Verify the layers you do not own. Mount options, hypervisor cache mode, drive cache and PLP. Each can turn an honored flush into an ignored one without any error.
6. Treat an impossibly fast flush as a warning. If pg_test_fsync on a SATA SSD without PLP reports a few microseconds, something is answering for the drive.
7. A replica is durable only if the commit waited for it. Check synchronous_standby_names and sync_state, not the existence of a replica.
8. "No reply" means "unknown". Build the client, the pool and the API around a third state, and make retries harmless with constraints.
9. Test with the recorder on another machine, and pull the power. kill -9 tests one layer. Only a power cut tests all of them.

Test yourself

What it gives you, and what it costs

The takeaway

Back to the incident in Part 1. In the composite, the answer was case 1: a role-level synchronous_commit = off from an old performance ticket. Fifty milliseconds of acknowledged payments were in WAL buffers when the power died, exactly as the setting promised. PostgreSQL did not lose a committed transaction. It did what it was told, and nobody remembered telling it.

That is the pattern in almost every real version of this incident. The database's promise is narrow and well documented. The loss comes from a layer that promised less than the application assumed. A setting, a mount option, a hypervisor flag, a drive, a replica that was never synchronous, or a client that turned "unknown" into "failed". When you investigate the next one, do not start by asking whether the database lost data. Start by asking what each layer was configured to promise, and then find the layer whose promise was smaller than yours.

Sources

PostgreSQL references are to the version 18 documentation and the REL_18_STABLE source branch. MySQL references are to the 8.4 reference manual.

• PostgreSQL docs: Write Ahead Log settings (synchronous_commit, fsync, wal_sync_method, full_page_writes, wal_writer_delay, commit_delay), Reliability (write caches, hdparm, torn pages), Asynchronous Commit, WAL introduction, WAL Configuration (checkpoints, group commit), Synchronous Replication, synchronous_standby_names, data_sync_retry, WAL LSN functions, pg_xact_status, pg_stat_replication, pg_stat_io, pg_stat_wal_receiver, pg_test_fsync, pg_waldump, initdb (checksums on by default), release notes 18.0 and release notes 11.2 (panic on fsync failure).
• PostgreSQL source, REL_18_STABLE: xact.c (RecordTransactionCommit), xlog.c (XLogFlush, XLogWrite, issue_xlog_fsync, XLogSetAsyncXactLSN), xlogrecovery.c (recovery log messages).
• PostgreSQL wiki: Fsync Errors and Reliable Writes.
• LWN: PostgreSQL's fsync() surprise (2018), Improved block-layer error handling (2017, errseq_t), The end of block barriers (2010).
• Linux: write(2), fsync(2), open(2) (O_SYNC, O_DSYNC, O_DIRECT), Explicit volatile write back cache control (REQ_PREFLUSH, REQ_FUA), ext4 mount options, XFS mount options (nobarrier removed in 4.19), hdparm(8).
• Papers: Zheng et al., Understanding the Robustness of SSDs under Power Fault (FAST '13). Rebello et al., Can Applications Recover from fsync Failures? (ATC '20). Pillai et al., All File Systems Are Not Created Equal (OSDI '14). Verbitski et al., Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases (SIGMOD '17).
• SQLite: How To Corrupt An SQLite Database File and Atomic Commit In SQLite.
• Virtualization: QEMU invocation (cache= modes) and libvirt domain XML (disk driver cache).
• Cloud: Amazon EBS volume types (durability figures), EC2 instance store lifetime, Azure managed disk redundancy, Aurora high availability.
• Hardware: Intel, SSD DC S3500 product brief (power-loss data protection). Brad Fitzpatrick, diskchecker.pl (2005).
• MySQL 8.4 docs: innodb_flush_log_at_trx_commit, sync_binlog, The Binary Log (crash recovery with InnoDB), Redo Log, Doublewrite Buffer, Checkpoints, InnoDB Standard Monitor, Semisynchronous Replication, innodb_flush_method.
• MySQL Server blog: Paweł Olchawa, MySQL 8.0: New Lock free, scalable WAL design (2018).