System Design the Agentic Way — Phase 3: Data Storage Trade-offs

Phase 3: Data Storage Trade-offs

Phase 2 leaned on a shared store and waved at durability — "the data survived a Redis restart" — then admitted the caveat: a graceful shutdown snapshotted to disk, an ungraceful one wouldn't have. Phase 3 goes inside that store and asks the question the caveat was hiding: when you tell a client saved, what exactly did you promise, and what does keeping that promise cost?

---

Table of Contents

• The Question
• The Building Blocks: What "Saved" Actually Costs
  • Where a Write Actually Goes: The Four Layers
  • write() vs fsync and the fsync Tax
  • Process Crash vs Power Loss
  • The Durability Window
  • The Write-Ahead Log
  • Indexes and Write Amplification
  • B-tree vs LSM: The Storage-Engine Fork
  • The Trade-off Matrix
• Durability Is Not Availability to Reads
• The Fix Reveals the Lesson
• What This Phase Actually Teaches
• Where I'm Still Fuzzy
• Try It Yourself
• Next

---

The Question

Here's a question that sounds trivial until you try to answer it precisely: when your database replies 200 OK, where is your data at that exact moment? On the disk? In memory? Somewhere in between — a place a power cut would quietly erase a half-second later? Most engineers can't say, and the ones who found out the hard way have a war story about it.

Chase that question and it forks into three: How do I not lose data on a crash? How do I make writes fast? How do I make reads fast? Every storage engine on Earth is just a particular answer to those three — and they pull against each other, so no engine wins all three. The tension between them is the entire phase, and by the end you'll be able to answer the opening question for any system, including the one you build here.

                    DURABILITY
              (don't lose acked writes)
                        /\
                       /  \
                      / .  \        every storage engine
                     /  .   \       lives somewhere inside
                    /   .    \      this triangle — push
                   /    .     \     toward one corner and
                  / .   .    . \    you give up the others
                 /______.______\
          WRITE SPEED         READ SPEED
        (cheap appends)     (indexes, sorted)

A war story sets the stakes. An analytics SaaS, Series B, ran an ingestion service that accepted event writes, buffered them in memory, and flushed to disk every 200 ms in a batch — because "fewer, bigger writes are faster." The client got a 200 OK the instant the write hit the in-memory buffer. Everyone was proud of the p99.

Then a routine node recycle during autoscaling sent SIGKILL to a pod holding ~200 ms of un-flushed writes. At 3:47 AM no alert fired; the service came back healthy. Days later a customer proved from their own logs that ~4,000 events they'd received a 200 OK for simply weren't in any report. The team blamed the query layer, then the cache, then a "customer clock issue." The truth was simpler: the writes were acknowledged before they were durable. A crash inside the 200 ms window was invisible data loss — no error at write time, no error at read time, just missing records.

The lesson is a single question — when you tell a client "saved," what have you actually promised? — and the rest of this article is what it takes to make that promise honest.

---

The Building Blocks: What "Saved" Actually Costs

Where a Write Actually Goes: The Four Layers

A write() does not put data on disk. It passes through four layers, and only the last one is non-volatile:

your string
   │  write()               microseconds
   ▼
① App buffer      (process RAM)          dies on power loss AND on process crash
   ▼
② OS page cache   (kernel RAM, dirty)    dies on power loss — SURVIVES a process crash
   ▼
③ Disk controller cache (RAM on the SSD) dies on power loss
   ▼
④ NAND / platter  (non-volatile media)   survives everything  ← the only durable layer

Layer	Location	Volatile?
① App buffer	Process RAM	dies on power loss (and process crash)
② OS page cache	Kernel RAM (dirty pages)	dies on power loss
③ Disk controller cache	RAM on the SSD	dies on power loss
④ NAND / platter	Non-volatile media	survives ← durable

NAND is the array of non-volatile flash chips inside an SSD (the HDD equivalent is a magnetic platter). Getting bytes from layer ① to layer ④ is the whole job. Everything above ④ is a form of RAM that is lying to you about durability until proven otherwise.

write() vs fsync and the fsync Tax

Two syscalls, two very different guarantees:

• write() returns → your bytes are in the OS page cache (②). They survive a process crash, because the kernel outlives the process. They do not survive power loss.
• fsync(fd) returns → the kernel has flushed those pages down to NAND (④) and blocked until the disk confirmed. Now the write survives everything.

The gap between them is the price of durability:

write()   ≈ microseconds        (just a memcpy into kernel RAM)
fsync()   ≈ 0.1–2 ms  on SSD    (100–1000× slower)
          ≈ 5–15 ms  on HDD

That 100–1000× gap is the fsync tax. Skipping fsync makes writes look dramatically faster — which is exactly why teams skip it, and exactly how they end up acking data they haven't actually saved.

Process Crash vs Power Loss

This is the crux the analytics team missed, so it's worth being precise. The two failure classes have opposite consequences for un-fsync'd data:

Process crash  → kill -9 / OOM / deploy / pod eviction
                 kernel survives → page cache (②) survives → un-fsync'd data ✅ SURVIVES

Power loss     → power event / --force stop / spot reclaim / host or kernel failure
                 RAM dies → page cache (②) is gone → un-fsync'd data ❌ LOST

The trap is subtle: a process crash — kill -9, an OOM, a deploy — leaves the page cache alive, so un-fsync'd data quietly survives it. Only a power-class event — a real power loss, a kernel panic, a forced host stop — erases that RAM. Durability has to hold against the second class, and the happy path never exercises it, which is why the loss tends to surface weeks later in someone else's logs.

The Durability Window

If you fsync on a timer instead of on every write, there is a slice of acknowledged data that isn't yet on disk — the durability window:

fsync policy	Window of exposure
appendfsync no	seconds (whenever the OS feels like flushing)
appendfsync everysec	≈ 1 s — Redis's default; the fsync timer runs independent of your write
appendfsync always	≈ 0 — one fsync per write, maximum safety, maximum tax

The subtle mistake here: "fsync every second" is not "my write is synced within a second of my write." The timer fires on its own schedule; a crash can land in the instant right before the next tick, taking a nearly-full second of acked writes with it. The window is a property of the timer, not of any individual request.

The Write-Ahead Log

The fix for all of this is the Write-Ahead Log (WAL): write the change to a durable, append-only log first — ahead of applying it to the real data structure — and only ack once that log entry is on disk. Log first, apply second.

SET balance=100
 ① append to WAL      (sequential, at the end of the file)
 ② fsync(wal_fd)      (one cheap flush → durable)
 ③ return 200 OK      (now it is safe to say "saved")
 ...later, in the background...
 ④ apply to the real data structure

Why a log instead of just updating the data structure in place?

• Append is cheap because it's sequential. The write always goes to the end of one file — no seeking. A B-tree update, by contrast, touches scattered pages all over the disk; fsyncing those is slow and a crash mid-update leaves a half-written structure = garbage.
• Recovery is a replay. On restart you read the log and re-apply entries in order. An incomplete tail entry (a crash mid-append) is detected by length/checksum and discarded — it was never acked, so dropping it is safe.
• Never write in the middle. The log is append-only on purpose: it preserves order (the history is the source of truth), keeps writes sequential, and keeps already-durable bytes immutable so only the final entry can ever be torn. You reclaim space with a checkpoint/truncation or a compaction that writes a fresh file and deletes the old one — never an in-place edit.

An analogy makes the ordering guarantee concrete:

Analogy:   a bank ledger — you never erase an old line; you append a correcting entry.
Technical: SET x=1 then SET x=2 both append. On power loss, replay in order → GET x = 2.
           Last write wins because log order is the source of truth.

This pattern is not a toy — it is everywhere:

System	Its WAL
PostgreSQL	the WAL (pg_wal)
SQLite	WAL journal mode
RocksDB / LevelDB	the write-ahead log in front of the memtable
Kafka	the entire log is the database
Redis	AOF (Append-Only File) — the AOF is a WAL; appendfsync always = window ≈ 0

One optimization worth naming: group commit. Doing one fsync per write caps throughput at N fsyncs/sec. Instead, batch every write that arrives in a 1–10 ms window, append them all, do one fsync for the batch, then ack all of them. Each client waits a hair longer; total throughput jumps, because the expensive syscall is amortized across the batch.

Indexes and Write Amplification

An append-only log is wonderful to write and terrible to read: finding a key means a full scan — read every record, O(n).

An index is a second, sorted data structure that answers "where is key X?", turning that O(n) scan into O(log n) or O(1). It's a derived copy kept in sync with the data — like the index at the back of a textbook. It is not the source of truth.

But indexes aren't free, and the cost has a name: write amplification — the ratio of physical writes to logical writes. Every index has to be updated on every write, so five indexes mean roughly five times the disk I/O per logical write.

Analogy:   the "Index Everything" reflex — index every column "just in case".
Technical: 5 indexes ≈ 5× write I/O. The signal you fell for it:
           your writes are slower than your reads.

An index is a trade — read speed bought with write cost and disk space — not a free win. That single reframing is what separates "add an index" as a reflex from "add an index" as a decision.

B-tree vs LSM: The Storage-Engine Fork

Once you accept that write amplification is real, the whole storage-engine design splits into two families based on how they lay writes down:

  B-tree — UPDATE IN PLACE              LSM — APPEND, MERGE LATER

  write ─┐                              write ─┐
         ▼                                     ▼
   seek to the right page              memtable  (RAM, sorted)
         ▼                                     │ fills up
   modify it where it lives                    ▼
         ▼                              flush ─► SSTable  (immutable file)
   maybe split / rebalance                     │  SSTable
                                               │  SSTable
  → random I/O, scattered writes               ▼
  → great reads (one lookup)          compaction merges them
                                       → sequential I/O, never in place
                                       → high write throughput

	B-tree (Postgres/MySQL)	LSM (Cassandra/RocksDB)
Write path	random, in-place	sequential append
Write throughput	moderate	high
Read path	one lookup down the tree	memtable + several SSTables
Read amplification	low (~1)	higher (N files)
Background work	page splits / rebalancing	compaction (CPU/I/O bursts)
Best when	balanced/read-heavy, rich queries + txns	write-heavy (logs, metrics, events)

An LSM tree is, in effect, "the WAL idea applied to the whole database." Its parts:

memtable   → in-RAM sorted table; every write lands here first (fast, sequential)
SSTable    → Sorted String Table: an immutable sorted file flushed from the memtable
compaction → background merge of SSTables; drops overwritten/deleted keys,
             writes new files and deletes the old ones — never in-place

The decision falls out of the workload. For a metrics system doing millions of writes/second where reads are mostly for recent data, you choose LSM: writes become cheap sequential appends, and recent reads hit the memtable and the newest SSTables so read amplification barely bites. A B-tree is ruled out at that write rate by random-I/O seeks and write amplification from page rewrites and splits. This is exactly why Cassandra, ScyllaDB, and InfluxDB are LSM-based.

The Trade-off Matrix

Widen the lens to include the append-only log as a first-class storage choice, and the full picture is three columns:

	B-tree (Postgres)	LSM (Cassandra)	Append-only log (Kafka)
Write throughput	moderate	high	highest
Point lookup	excellent	good	poor
Range/sorted scan	excellent	good	poor
Write amplification	page rewrites + splits	compaction (amortized)	~none
Read amplification	~1	N (worst on old data)	N/A
Operational cost	mature, predictable	compaction tuning	consumers track offsets
Reach for it when	balanced/read-heavy + txns	write-heavy, key/time-series	event streams, ordered replay

How to read it: don't pick the "best" engine — pick the column whose strength matches your bottleneck and whose weakness you can tolerate. Real systems mix engines: Postgres for accounts, Cassandra for the firehose, Kafka to move between them. "One database for everything" is the trap.

---

Durability Is Not Availability to Reads

The prototype is a durable key-value store: an append-only wal.log as the source of truth, an in-memory Map as a derived read cache rebuilt on startup. The write path is log-first, and the durability contract lives in one function — the ack happens only after the fsync returns:

function appendToWAL(logEntry) {
  const line = JSON.stringify(logEntry) + "\n";

  // Reaches only the OS page cache (layer ②) so far.
  fs.writeSync(fd, line);

  // Force page cache → NAND (④), and BLOCK until the disk confirms.
  fs.fsyncSync(fd);

  // We return only AFTER the fsync. The caller can now honestly say "OK".
}

Building the write path first exposed a gap that the phrase "the data survived" always hides. After appending two writes and restarting the server, GET x returned 404 — while wal.log still held both entries on disk.

Nothing was lost. The WAL faithfully kept every write across the restart. But the thing that answers a GET is the in-memory Map, and a fresh process starts with an empty one (in-memory keys at startup: 0). The history was recorded and never replayed. Durability is not availability to reads — they are two independent properties, and the WAL pattern has two halves to match: append on write (done) and replay on recovery (missing).

  AFTER A RESTART, BEFORE recovery runs:

    wal.log (disk)   │  x=1   y=hello        ← durable, intact, still on disk
    Map    (memory)  │  (empty)              ← GET x → 404
                     └────────────────────────────────────────────
                        the data IS saved, but nothing can read it

  recover()  replays the log back into memory:

    wal.log ──► parse each line in order ──► apply() ──► Map
      x=1                                                x=1
      y=hello                                            y=hello
                     └────────────────────────────────────────────
                        now GET x → 200 OK (value 1). gap closed.

The second half is recover(), called before the server accepts any request:

function recover() {
  const raw = fs.readFileSync(WAL_PATH, "utf8");
  const lines = raw.split("\n");        // clean file ends in "\n" → last element is ""
  let applied = 0, discardedTail = false;

  for (let i = 0; i < lines.length; i++) {
    const line = lines[i];
    if (line === "") continue;
    try {
      apply(JSON.parse(line));          // same apply() the live write path uses
      applied++;
    } catch (err) {
      // Only the LAST line may be broken — a torn tail append that was never acked.
      if (i === lines.length - 1) discardedTail = true;   // safe to drop
      else throw new Error(`WAL corruption at line ${i + 1}`); // real corruption → fail loud
    }
  }
  return { applied, discardedTail };
}

Two details carry the design. First, apply() is shared by both the live set/del path and replay, which guarantees a write and its recovery are interpreted identically. Second, the torn-tail rule is asymmetric on purpose: a parse error on the last non-empty line is a half-written append (never acked) and is discarded; a parse error anywhere else is real corruption and throws loudly.

With recovery in place, the same fresh process that returned 404 now serves GET x = 1 (HTTP 200) — recovered 2 WAL entries → 2 keys. And last-write-wins holds across a restart: PUT x=1 then PUT x=2 appends a new line rather than editing in place, so a restart replays recovered 3 WAL entries → 2 keys and GET x = 2. Replay order is write order.

---

The Fix Reveals the Lesson

Every failure in this phase traces to the same root — a layer of RAM being mistaken for durable storage — and each has a matching fix:

• Ack before durable → append to the WAL and fsync before returning 200 OK. The ack is only honest once the fsync confirms.
• Data on disk but reads 404 → replay the WAL into the read structure on startup. Durability and read-availability are separate; you must build both halves.
• Buffering in app memory → get bytes out of layer ① as early as possible; a per-request flush trades latency for the elimination of a silent-loss window (or use group commit to claw the latency back).

The shape is the same as Phases 1 and 2: the component that fails determines both the symptom and the fix. What's new is that the failure is invisible at both write time and read time — no error is ever returned. It surfaces only under a power-class failure that erases RAM, which the happy path never exercises — so the loss can reach production before anyone notices.

---

What This Phase Actually Teaches

1. A write() is not durable; only fsync is. Bytes pass through four layers and only NAND survives power loss. Until fsync returns, "saved" is a claim about RAM, not disk.

2. Process crash and power loss are different failure classes. Un-fsync'd data lives in the OS page cache — kernel memory — so it survives a process crash but dies on power loss. Conflating the two is how silent loss reaches production.

3. A WAL makes "saved" honest, cheaply. Append-first + fsync-before-ack buys durability with sequential I/O; recovery is an in-order replay; only the untorn, acked prefix is ever restored. It's the pattern under Postgres, Kafka, SQLite, and Redis AOF.

4. Durability is not availability to reads. The log surviving a crash and the reads working after a crash are independent guarantees. You have to build append-on-write and replay-on-recovery.

5. Every index is a trade. Read speed is bought with write amplification and space. "Index everything" shows up as writes slower than reads. B-tree vs LSM is the same trade at engine scale: pick the write/read amplification profile your workload can tolerate.

Decision heuristic:

Must never lose an acked write            → WAL: append + fsync BEFORE ack
Throughput-bound by fsyncs/sec            → group commit (batch, one fsync, ack all)
Can tolerate a ~1s loss window for speed  → fsync on a timer (everysec), eyes open
Balanced/read-heavy + rich queries + txns → B-tree (Postgres/MySQL)
Write-heavy, key or time-series, recent reads → LSM (Cassandra/RocksDB/Influx)
Ordered event stream, replayable          → append-only log (Kafka)
Reads slower than a full scan can afford   → add ONE index, and only the ones you query

---

Where I'm Still Fuzzy

• Where the durability window should actually sit. everysec vs always is a latency-vs-loss dial, but I don't have a principled way to choose the tolerance. Presumably it's a function of how much acked data the business can afford to lose per crash — but I haven't turned that into a number I'd defend.

• Torn-write detection beyond a parse error. Discarding a JSON line that won't parse catches an obviously truncated tail, but a torn write could in theory leave a line that does parse yet is semantically wrong. Real engines use checksums per record. I understand why, but I haven't built the checksum path to feel where the naive length/parse check breaks.

• When group commit's added latency is unacceptable. Batching amortizes fsync beautifully for throughput, but it deliberately makes each individual write wait. For a low-traffic, latency-sensitive write path the batch window might be pure overhead. I don't yet know where the crossover is.

---

Try It Yourself

The prototype is at github.com/aishwarya-chamanoor/system-design-agenticway in phase-03/.

git clone https://github.com/aishwarya-chamanoor/system-design-agenticway
cd system-design-agenticway/phase-03
node server.js

Experiments worth running:

1. PUT /kv/x a value, restart the server, then GET /kv/x — watch the startup log say recovered N WAL entries and the read return your value. That's replay-on-recovery working.
2. PUT the same key twice with different values, restart, GET — the second value wins, because the WAL appended a new line instead of editing in place. Observe last-write-wins falling out of log order.
3. Peek at wal.log between writes (it's newline-delimited JSON) and confirm it only ever grows — no line is ever rewritten. That append-only shape is the whole safety argument.

---

Next

Phase 4 asks what happens when reads outrun the durable store this phase built — and what it costs to keep a faster copy of the truth without letting it drift.

---

Part of a 12-phase series building distributed systems intuition from first principles. Each phase has a running prototype, failure scenarios, and a gate check before moving on.