Five Levels of Database Write Success: Memory, SSD, Object Store, Majority, Cross‑AZ

Returning success immediately after copying data to memory is the fastest path, but it turns the write into a reliability gamble; every database must decide which side of this trade‑off to stand on.

The real secret of write speed lies in when success is reported. Whether a write waits for a local SSD flush, an object‑storage upload, or a multi‑node majority commit, each path offers a different durability guarantee, and moving the success point farther out improves data survivability at the cost of latency.

Fast not always good : if the database reports success while the data resides only in memory, a power loss erases the write.

The Linux system call fdatasync() forces data to the physical disk, protecting against process or kernel crashes; by contrast, write() returns when data is still in the page cache, which is lost on reboot.

NVMe is merely an interface standard; a local SSD that survives a process crash still cannot survive disk damage or a whole‑machine failure.

Accurate latency measurement depends on the anchor point: memory copy, SSD flush, or remote storage acknowledgment. Without a clear anchor, any latency figure is misleading.

One flush serves many writes : invoking fdatasync() per request is simple but caps performance at roughly a thousand syncs per second, regardless of the disk’s raw throughput.

Batching groups hundreds of writes and performs a single fdatasync(), dramatically raising throughput while increasing tail latency because early requests wait for the batch to finish.

Batch limits—maximum bytes, request count, time, and queue length—are essential; an unbounded queue can exhaust memory, and a full batch that cannot be flushed before the next batch arrives only adds delay.

Device cost correlates with the actual persisted write size: moving 1 GB/s with 256 KB blocks needs ~4 k IOPS, while 16 KB blocks require ~65 k IOPS.

Data leaves the machine to be robust : returning after a local SSD flush hides the object‑storage upload from the client, but the latest WAL resides on a single SSD; if that machine fails, the database can only recover to the previous upload.

This makes the database stateful; the scheduler cannot arbitrarily move it without ensuring the SSD’s survival, accepting a window of possible data loss, or replicating the WAL before acknowledging success.

Including a remote PUT in the success condition closes the window but adds HTTP latency; batching reduces request count but forces early requests to wait for the batch to fill.

Object‑storage conditional writes (e.g., If-None-Match: *, If-Match:) guarantee creation‑only or version‑matched updates, solving single‑object concurrency but not multi‑object atomicity or cross‑key access control.

When PUT latency is acceptable, the database can offload persistence to the storage service; otherwise, it must replicate the WAL across multiple database nodes and wait for a majority of nodes to flush before returning.

WAL replication is a coordination game : the client sends a PUT to the leader, which appends the WAL locally and forwards it to two followers. Success is reported once the leader and any follower have flushed, tolerating the loss of one server.

Compared with a single‑node SSD, replication adds a network hop and an extra disk flush. Majority flushing in the same rack can be faster than a remote object‑storage request, while geographically dispersed replicas increase fault tolerance at the cost of higher latency.

Coordination also involves write permission and commit tracking; this logic can be external to the storage engine, with a separate service assigning monotonically increasing identifiers and rejecting stale writes.

Raft records the current term and log position on the leader; followers replicate the entry, and the entry is committed once a majority have flushed. Disk and network operations overlap, so typical latency approximates the slower of the two, not their sum.

A common pitfall is returning after an in‑memory acknowledgment; the original Raft paper requires a flush before acknowledging, otherwise a simultaneous reboot would lose all “successful” writes.

Replication introduces extra burdens: leader election, log repair after failures, catch‑up for lagging nodes, full‑snapshot creation for fast restarts, safe node addition/removal, and monitoring of replication lag—none of which appear in a single‑write latency number.

Fast reads need index and cleanup : replaying the WAL alone cannot serve low‑latency reads; a separate index (hash, B‑tree, or LSM) maps keys to the latest value or disk location.

Hash indexes give exact lookups without ordering; B‑trees support ordered scans but incur higher maintenance cost; LSM trees buffer new keys in memory and sorted files, merging them later.

LSM and MVCC address different concerns and can coexist: LSM manages key flow between memory and sorted files, while MVCC controls version visibility. Background merges and blob‑storage compaction eventually reclaim space occupied by tombstones.

Deleting a key of size O(key bytes) adds a tombstone; actual space reclamation requires reading the index, copying live values, switching to a new file, and finally deleting the old file.

Big‑O hides the truth : regardless of the durability path, a PUT has complexity O(key bytes + payload bytes). This tells how work grows with data size but not the latency differences caused by device flushes, remote HTTP calls, or combined network‑disk delays.

The complexities of GET and DELETE also omit the cost of scanning the entire WAL or full data set; understanding latency requires knowing the success anchor and percentile distribution.

Backend tasks must be bounded: WAL files need a maximum size, cleanup must run with byte‑size limits and progress checkpoints, and the pending‑write queue must be capped to prevent unbounded memory growth.

Large language models can generate uploader, cleanup loop, and log‑replication code quickly, but they cannot decide how much unfinished work the system can tolerate, when to throttle writes, or which crash scenarios cause data loss. Those decisions must be encoded around fdatasync(), version‑switch logic, and majority‑replication logic and verified repeatedly.

Tracing the write path—from memory to local SSD, to remote storage, to a replica cluster—clarifies the trade‑offs: faster paths do less work and tolerate fewer failures; pushing the success point later buys reliability but forces the database to shoulder coordination, repair, and cleanup responsibilities.

In summary, moving the success acknowledgment farther out increases durability but also latency and system complexity; every “ultra‑fast” write metric must first answer: fast for whom, and who bears the loss if something goes wrong?