Append throughput

We benchmarked reads in section 1. Now it is time to do the same thing for writes, with the same server scaffold and the same wrk setup, but hitting POST /users instead of GET /users/:id. Nothing in the framework changes. Only the code inside createUser changes between runs.

The twist is that in the last lesson we learned that the simple appendFileSync call we have been using since lesson two is not actually durable. It returns before the bytes are safe on disk. Now we are going to measure what happens when we add real durability, and what it costs.

The four write paths we are going to benchmark

Before any code, lay out the contenders. We will measure four different createUser implementations, chosen to isolate one variable at a time.

1. JSONL append, no fsync. What our linear scan and in-memory map stores already do. This is the speed ceiling without durability.
2. JSONL append, with fsync. Proper power-loss durability. One fsync per write. This is the cost of safety.
3. SQLite insert, one row per request. What our SQLite store already does. A real database doing a real transaction per request.
4. SQLite insert, batched in a transaction. A common production optimization. Many inserts, one fsync.

Implementations 1 and 3 we already have from section 1. We will add 2 and 4 in the next two steps.

Step 1: A wrk script for POST

wrk defaults to GET requests. To hit our POST /users endpoint, we need a Lua script that constructs a valid JSON body on every request. Put this at the project root next to random_ids.lua.

-- post_users.lua
math.randomseed(os.time())

local counter = 0

request = function()
  counter = counter + 1
  local body = string.format(
    '{"name":"User %d","email":"user%[email protected]"}',
    counter, counter
  )
  return wrk.format(
    "POST",
    "/users",
    { ["Content-Type"] = "application/json" },
    body
  )
end

Each call to request builds a unique payload. We use an incrementing counter rather than random ids because every POST should produce a fresh write, with no duplicates to worry about. wrk.format builds the request from method, path, headers, and body, and wrk sends it.

Run it the same way we ran the read benchmark.

wrk -t4 -c50 -d10s -s post_users.lua http://localhost:8081

Same flags as before. Four threads, fifty concurrent connections, ten seconds of load. That way the write numbers come out directly comparable to the read numbers from section 1.

Step 2: Add the fsync store

This is “implementation 2” from the list above. Create src/store-jsonl-fsync.ts.

Imports and a persistent file descriptor

// src/store-jsonl-fsync.ts
import { openSync, writeSync, fsyncSync } from "node:fs";
import { randomUUID } from "node:crypto";

const USERS_FILE = "users.jsonl";

export interface User {
  id: string;
  name: string;
  email: string;
  created_at: string;
}

// Open once, keep the file descriptor open for the lifetime of the process
const fd = openSync(USERS_FILE, "a");

Three helpers from node:fs. openSync opens a file and hands us back a file descriptor. writeSync writes bytes to that descriptor. fsyncSync forces the kernel to flush the write to the SSD.

The last line is the trick. We open the file once, at module load, and hold the descriptor for the lifetime of the process. Every previous version of createUser opened and closed the file on every request, which meant two extra syscalls (open and close) per write. That was invisible noise when the whole call took microseconds. When fsync makes each call take milliseconds, we want everything else to be as small as possible.

The write function

export function createUser(name: string, email: string): User {
  const user: User = {
    id: randomUUID(),
    name,
    email,
    created_at: new Date().toISOString(),
  };

  const line = JSON.stringify(user) + "\n";
  writeSync(fd, line);
  fsyncSync(fd);

  return user;
}

We build the record the same way we did in the previous stores, serialize it to JSON with a trailing newline, and write it with writeSync.

Then we call fsyncSync(fd). That is the durability barrier from the previous lesson. It blocks until the kernel has confirmed the bytes are on the SSD. Only then does createUser return.

That single fsync is why this store will be 50 to 100 times slower than the plain JSONL append. It is also why data written through it survives a power loss.

Step 3: Add the batched SQLite store

This is “implementation 4.” This is the most interesting store in the lesson, because the HTTP handler receives one write at a time, but we want to batch many writes into a single SQLite transaction. Create src/store-sqlite-batched.ts and build it piece by piece.

Imports, database setup, prepared statement

// src/store-sqlite-batched.ts
import { DatabaseSync } from "node:sqlite";
import { randomUUID } from "node:crypto";

const db = new DatabaseSync("users.db");
db.exec("PRAGMA journal_mode = WAL");
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id         TEXT PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT NOT NULL,
    created_at TEXT NOT NULL
  )
`);

export interface User {
  id: string;
  name: string;
  email: string;
  created_at: string;
}

const insert = db.prepare("INSERT INTO users (id, name, email, created_at) VALUES (?, ?, ?, ?)");

Exactly the same setup as our regular SQLite store from section 1. Open the database, turn on WAL mode, create the table if it does not exist, prepare the insert statement.

A helper that inserts a batch in one transaction

function insertMany(rows: User[]) {
  db.exec("BEGIN");
  try {
    for (const r of rows) insert.run(r.id, r.name, r.email, r.created_at);
    db.exec("COMMIT");
  } catch (err) {
    db.exec("ROLLBACK");
    throw err;
  }
}

This is the batching mechanism. db.exec("BEGIN") opens a transaction. From here until COMMIT, SQLite is collecting changes in its WAL but has not flushed them yet. We run every insert. Then db.exec("COMMIT") writes the whole batch atomically, with a single fsync at the end.

If any insert throws, we ROLLBACK to discard the partial work and re-throw the error.

node:sqlite does not ship a db.transaction(fn) helper the way some other SQLite clients do, so we wrap BEGIN and COMMIT manually. The effect is the same.

The in-memory buffer and the two flush triggers

let buffer: User[] = [];
const FLUSH_SIZE = 100;
const FLUSH_INTERVAL_MS = 50;

function flush() {
  if (buffer.length === 0) return;
  const toFlush = buffer;
  buffer = [];
  insertMany(toFlush);
}

buffer holds users that have been “created” from the client’s perspective but are not yet in SQLite. FLUSH_SIZE is how many pending users trigger an immediate flush. FLUSH_INTERVAL_MS is how long we are willing to let an entry sit in the buffer before we force a flush.

flush is the thing that actually calls insertMany. Notice the subtle move: we snapshot buffer into toFlush and replace buffer with an empty array before inserting. That way, if new requests arrive during the insert, they land in the fresh empty buffer and do not get committed twice or dropped.

The time-based trigger

// Periodic flush. srvx's serve() keeps the event loop alive, so we do not
// need to do anything special to prevent this interval from blocking exit.
setInterval(flush, FLUSH_INTERVAL_MS);

setInterval arms a timer that calls flush every 50ms. This matters for low traffic. Without the timer, a user created at 3am on a quiet night would sit in the buffer unwritten until the next POST happened. The timer gives every write a bounded “time to disk” of at most 50ms.

We do not need to do anything special to keep this timer from blocking process exit. serve (from srvx) is already holding the event loop open for as long as the server is running. The timer just rides along on the same event loop and will stop when the process stops.

Graceful shutdown

// Best-effort flush on graceful shutdown. Anything still in the buffer when
// the process is force-killed is lost. That is the tradeoff this approach makes.
process.on("SIGTERM", flush);
process.on("SIGINT", flush);

When the process receives SIGINT (Ctrl+C) or SIGTERM (a polite shutdown signal from a process manager or Docker), we try to flush before exiting. If the process is killed with SIGKILL or crashes unexpectedly, any buffered writes are lost. That is the price of batching.

createUser itself

export function createUser(name: string, email: string): User {
  const user: User = {
    id: randomUUID(),
    name,
    email,
    created_at: new Date().toISOString(),
  };

  buffer.push(user);
  if (buffer.length >= FLUSH_SIZE) flush();

  return user;
}

The whole point of the file lives in these last lines. Build the user. Push onto the buffer. If the buffer is at capacity, flush immediately. Return the user.

Notice what we return. We return the user record as if it had been saved. It has not actually been committed to SQLite yet. The client gets a 201 Created response with a valid id, but the row is sitting in memory waiting for the next flush.

What do you think happens if the client calls createUser, the server returns 201, and then the process crashes before the flush? The user sees success. The record is gone. The client thinks the user was created. We lied.

This is the “fast but lying” version. It is a real pattern used in real systems, with very honest tradeoffs. You should understand what you are trading away before you reach for it.

Step 4: Wire the approach switch

We already set up src/server.ts in the benchmarking lesson to pick a store file based on the APPROACH env var. That logic handles the new stores too, as long as we name the files to match: src/store-jsonl-fsync.ts and src/store-sqlite-batched.ts. No further code changes.

Step 5: Run each implementation

Run all four with wrk and record the numbers. The sleep 1 after each server start gives Node a moment to bind the port before wrk connects.

# Implementation 1: JSONL no fsync
APPROACH=map node src/server.ts &
sleep 1
wrk -t4 -c50 -d10s -s post_users.lua http://localhost:8081
kill %1

# Implementation 2: JSONL with fsync
APPROACH=jsonl-fsync node src/server.ts &
sleep 1
wrk -t4 -c50 -d10s -s post_users.lua http://localhost:8081
kill %1

# Implementation 3: SQLite per-row
APPROACH=sqlite node src/server.ts &
sleep 1
wrk -t4 -c50 -d10s -s post_users.lua http://localhost:8081
kill %1

# Implementation 4: SQLite batched
APPROACH=sqlite-batched node src/server.ts &
sleep 1
wrk -t4 -c50 -d10s -s post_users.lua http://localhost:8081
kill %1

You should see the shape of differences we describe below. Exact numbers vary with your SSD, but the order-of-magnitude gaps are universal.

What to expect

The read benchmarks in section 1 were run end-to-end on our machine. The write numbers below are estimates; treat the ratios between rows as the real lesson. The absolute numbers will depend heavily on your SSD’s fsync latency.

A few things worth absorbing from this table.

fsync is the dominant cost. JSONL without fsync pushes tens of thousands of req/s. Turn fsync on and it collapses by roughly 50 to 100 times. That collapse is almost entirely the latency of your SSD responding to fsync. Your hardware is the ceiling. No amount of clever software can push above it if you are fsyncing on every write.

SQLite beats per-write fsync on flat files. Even with full durability, SQLite in WAL mode is several times faster than the naive fsync-on-every-write JSONL approach. The reason is that SQLite does its own per-write batching internally. WAL writes are amortized across multiple in-flight inserts, and SQLite fsyncs the WAL less often than once per insert. You get durability without paying the fsync-per-write cost.

Batched transactions get you back to the no-fsync ceiling. Wrap a hundred inserts in a transaction and you pay one fsync for the whole batch. Per-row throughput rises by almost exactly the batch size. This is the biggest single optimization available for any bulk-write workload, and it is what every database tutorial eventually mentions.

JSONL without fsync is fast and dishonest. It looks great on the throughput chart. It does not survive power loss. Most production systems could not ship with it. But it is the baseline that shows you the difference a fsync makes.

What product reality looks like

Most products are write-light. A typical CRUD application has a 10:1 or 100:1 read-to-write ratio. Even at a hundred thousand daily active users at peak load, writes might be a few hundred per second. Not tens of thousands.

For those products, even fsync-on-every-write JSONL is fast enough. A thousand or two writes per second is roughly 80 to 200 million writes per day. If your app is doing a hundred million writes per day, you are already large.

The places where write throughput actually becomes a bottleneck are specific:

• Analytics ingestion. Every page view, every click, every event is a write.
• IoT and sensor data. Thousands of devices each posting readings every second.
• Logging and observability. Application logs, traces, metrics at scale.
• Append-only event stores. Every state change in the system is a new event.

For these use cases, batching is unavoidable. You will not get away with one fsync per event; your SSD’s fsync ceiling is lower than your event rate. This is precisely why purpose-built databases exist for these cases. ClickHouse for analytics, InfluxDB for time series, Kafka for event streams. They are all built around the principle of batching writes and amortizing fsync costs.

For the vast majority of CRUD applications, SQLite with default settings (one row per request, WAL mode) handles anything you will ever actually throw at it. Do not optimize prematurely.

What we have not benchmarked

Mixed read-write workloads. Real applications do both at once, and the interactions between concurrent readers and concurrent writers matter a lot. SQLite in WAL mode is genuinely impressive here: readers are not blocked by writers, and vice versa. We will come back to this in the concurrent writers lesson.

We also benchmarked single-process throughput. In production you often run multiple processes behind a load balancer. The JSONL approaches get harder there (multiple processes appending to the same file introduces lock contention). SQLite handles it gracefully (one writer, many readers, all coordinated through the file itself). That is also coming in the concurrent writers lesson.

In the next lesson we go back to the binary search approach and look at what writes do to a sorted index. The short answer is that they break it. The long answer is most of what makes a database engine work.