Fsync Cost and Journal Modes on Flash Storage

A commit is a promise about the future, and on a phone the promise is made through a storage stack with several layers that each interpret “written” differently. The application writes bytes to a file; the kernel copies them into the page cache; the filesystem decides when to issue them to the block layer; the block layer may reorder them; the flash controller has its own cache, its own write ordering, and its own mapping table. A flush request is the mechanism that collapses this chain of buffering into an ordering guarantee, and every durability setting in an embedded database is a decision about how many flushes to spend.

What fsync actually promises

POSIX requires that fsync return only after all data and metadata for the file has been transferred to the storage device in a way that survives a system crash. fdatasync is the weaker form: it guarantees the data and whatever metadata is needed to retrieve it, so a size change is flushed but an access-time update need not be. The distinction is worth roughly one metadata write per commit, which on a device that charges milliseconds per flush is not nothing.

What the standard does not promise is that the device honours the request. A flush command reaches the controller, which may acknowledge it from a volatile write cache. If the device is configured to report cache flushes, the kernel issues the flush and waits; if it is not, the kernel may treat the write as durable when it is only in the controller’s DRAM. Consumer flash devices vary in how they advertise this, and the practical consequence is that a commit acknowledged by the database may not survive a power loss even though every layer above behaved correctly.

The second thing fsync does not promise is ordering between two files. Flushing file A and then flushing file B guarantees that A’s bytes are durable before B’s are requested, but if the device reorders internally the guarantee can be weaker than it appears. Filesystems that support write barriers express the ordering themselves: a barrier forces all writes issued before it to reach the medium before any write issued after it. Without barriers, a filesystem must choose between flushing the whole device or accepting that ordering is not enforced.

On mobile this is further complicated by the fact that the “power loss” case is usually not a power loss at all. It is the process being killed by the platform while the device stays alive. In that case the page cache survives, the data is not lost, and the commit that was never flushed is still there, but only until the device does lose power, or until the filesystem is unmounted uncleanly. A durability setting that is adequate for process death is not adequate for battery removal, and the two failure modes are routinely conflated.

The rollback journal’s four-step commit

The original SQLite durability mechanism writes the old page images to a journal before modifying the database file, and it commits in a sequence whose cost is the reason write-ahead logging exists.

First it writes the original pages into the journal. Second it flushes the journal, because the journal must be complete before any database page is overwritten. If the flush is skipped, a crash can leave a database page half-written with no valid original to restore. Third it writes the changed pages into the database file. Fourth it flushes the database file, then deletes or truncates the journal, and that deletion is the commit point.

The ordering is what makes the design correct and what makes it expensive: two flushes per commit, at least. The first is the one that cannot be removed without losing crash safety, because it establishes that a valid before-image exists before any after-image is written.

The journal has a header with a magic number, a page count, a random nonce, the initial page count, a sector size, and a checksum. The nonce is generated fresh for each transaction so that a stale journal from an earlier crash cannot be mistaken for the current one. Recovery at open reads the journal header, validates the checksum, and copies the recorded pages back into the database.

What each synchronous level buys

SQLite’s synchronous pragma has four settings, and they map onto exactly which flushes are performed.

EXTRA flushes the journal, the database file, and the directory that holds the journal, so the journal’s deletion is itself durable. FULL flushes the journal and the database file but not the directory. NORMAL flushes the journal and skips the database flush, deferring it to a later checkpoint. OFF performs no flushes at all and leaves durability to the operating system.

In rollback-journal mode, NORMAL still flushes the journal before any database page is overwritten, but it does not flush the database file at commit. After a power loss the database may hold some pages of the transaction and not others, and recovery rolls the transaction back from the journal. The documented risk is that the journal deletion can outlive the database write it was protecting, leaving a partially written database with nothing left to recover from, so the safe choice for rollback mode is FULL.

In write-ahead log mode the mapping changes entirely, and this is the part most often misunderstood. With FULL, the log is flushed at every commit, so a committed transaction survives a power loss. With NORMAL, the log is flushed before each checkpoint rather than at each commit, so every transaction committed since the last checkpoint can be lost when power fails; the database itself stays structurally sound because recovery discards the uncommitted tail of the write-ahead log. OFF in WAL mode skips even the checkpoint flush, and the documentation warns that a power loss can then corrupt the file rather than merely lose recent commits.

The practical reading is that WAL with NORMAL keeps the file structurally sound without per-commit durability, and WAL with FULL adds per-commit durability at the cost of one flush per commit. The gap between them is the largest performance lever in an embedded database on flash.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;    -- crash-safe, not power-loss-safe
PRAGMA synchronous = FULL;      -- one flush per commit
-- Durability of the log on a per-commit basis is what FULL adds in WAL mode.

How much a flush costs on flash

A flash erase block is typically a few hundred kilobytes, and a page within it a few kilobytes. Writing a page that has already been written requires erasing the whole block first, copying the surviving pages elsewhere, and rewriting them: write amplification. The controller hides this behind a mapping table, so the host sees page-sized writes, but the cost is real and is paid in latency when the cache is full and the controller must erase before it can accept the next write.

A flush request forces the controller to drain its write cache to the medium. That is a round trip through the controller’s firmware and, if the cache is being held for write coalescing, it ends the coalescing early. A device that could have absorbed a hundred small writes in one erase-block programming operation is asked to stop after each one. This is why a workload with a flush per commit can be an order of magnitude slower than the same workload with flushes batched, and why the cost is not proportional to bytes written but to the number of barriers.

The number of barriers is under the application’s control in a way that the number of bytes often is not. A thousand inserts in one transaction cost a handful of flushes; the same thousand inserts in a thousand transactions cost a thousand. The trade is atomicity granularity: one transaction means all-or-nothing for the whole batch, which is usually what a bulk import wants anyway.

Batching without lying

The temptation is to set synchronous = OFF during a bulk operation and restore it afterwards. That works, and it is honest as long as the application accepts that a crash during the import loses the import, which it must be able to detect and redo. The dishonest version is to leave it off permanently because the difference was noticeable, and then discover the problem when a user force-quits the app during a save.

A middle path is to reduce the number of transactions rather than the number of flushes. A write queue that drains on a timer, committing once per interval, converts N flushes into one per interval and bounds the amount of work lost to the interval’s worth of commits. The bound is real and can be stated: with a 200 ms drain interval, a power loss costs at most 200 ms of writes, and those writes are recoverable if the queue is persisted alongside the data.

The other middle path is to move the durability boundary outward. If the authoritative copy lives on a server, a local commit need not be durable at all; it needs only to be replayable. That is the reasoning behind outbox and change-log designs where the local database is a cache with a record of pending mutations, and it makes synchronous = NORMAL a defensible permanent setting rather than a temporary expedient.

Failures that look like bugs

A commit that returns success and is later lost is the classic symptom of a flush the device ignored. It is not detectable from above; the only defence is to avoid devices and configurations that do not honour flushes, which on a phone means the platform’s own storage, not removable media.

A database that fails to open after a crash, reporting a malformed file, is usually the opposite problem: the journal was flushed but the database was not, and recovery found a journal whose header did not match the database’s page count. The nonce and checksum exist to make this detectable rather than silently wrong, which is why recovery refuses rather than guessing.

A commit that succeeds slowly, with latency spikes at irregular intervals, is the controller doing garbage collection, the same cost that makes storage pressure a performance problem rather than only a capacity one. The spike is not caused by the transaction’s own bytes; it is caused by the accumulated cost of previous writes. Batching transactions reduces the number of barriers and therefore the frequency of these spikes, and it is the reason a database that writes once per user action can feel worse than one that writes once per screen.

Finally, a checkpoint that runs on the writer’s thread can turn a small commit into a large one when the log crosses its threshold. The flush at the end of a checkpoint writes the whole copied prefix, so the commit that happens to trigger the checkpoint pays for pages written many transactions ago. Setting the checkpoint threshold and running checkpoints from a context that is not on the critical path separates those costs.