7. A write traced

Six chapters of machinery, assembled. One statement, followed all the way down and back:

INSERT INTO users (id, email, region) VALUES (4718, 'alice@example.com', 'eu-west');

Three nodes in one region, replication factor 3, the row landing in range 137 whose replicas are on n2, n5 and n8 with the lease on n8. You are connected to n3.

   THE WHOLE JOURNEY

   client ──► n3 GATEWAY ──► n8 LEASEHOLDER ──┬─► n2 replica
                                              ├─► n5 replica
                                              └─► n8 local
                              ▲                    (Raft, fsync)
                              └── majority acks ───┘

① The connection and the statement

Your driver speaks the PostgreSQL wire protocol to port 26257. n3 — the gateway — parses the statement into an AST, resolves users against the schema it caches from the system ranges, and type-checks the values.

If your driver used a prepared statement, the plan may be reused; if not, planning happens now. For a single-row INSERT planning is trivial, and the whole interesting part is what comes next.

   n3   parse → resolve schema → typecheck → plan
        ~50–200 µs, entirely local, no network

② Beginning the transaction

You did not write BEGIN, so this is an implicit transaction: one statement, its own transaction, committed automatically. The gateway creates transaction state anyway, because everything below is the same either way.

The transaction is assigned an HLC timestamp from n3’s clock — call it ts14 — and an uncertainty interval of ts14 + max_offset.

   txn d81f…   timestamp ts14   status (not yet written)
               gateway n3 is the COORDINATOR

Nothing has been sent anywhere yet.

③ Encoding the row into keys

The SQL layer turns the row into the key-value writes of chapter 3:

   /Table/53/1/4718/0                      → ("alice@…","eu-west")
   /Table/53/2/"alice@example.com"/4718/0  → ∅        (email index)

Two keys, because the table has one secondary index. Note they are in different parts of the key space, so they may live in different ranges — this single-row INSERT may already be a multi-range transaction. Here, assume the index entry lands in range 44 on n1.

④ Routing: which node holds these keys?

The gateway consults its range cache, and on a miss reads the meta ranges (chapter 2):

   /Table/53/1/4718/0   → r137  replicas n2 n5 n8  lease n8
   /Table/53/2/"alice…" → r44   replicas n1 n4 n7  lease n1

The component doing this is called DistSender. It groups the writes by range, sends each batch to the right leaseholder, and handles the retries when a cached address turns out to be stale.

   n3 ──batch A──► n8   (row)
   n3 ──batch B──► n1   (index entry)
        both in flight at once

⑤ At the leaseholder: sequencing and evaluation

n8 checks it still holds a valid lease for r137, then puts the request through the latch manager — short-lived, in-memory latches over the affected keys that serialise concurrent requests to the same key for the duration of evaluation only. They are not transaction locks; they are closer to a mutex.

It then checks the lock table and any existing MVCC versions for conflicts:

   • is there an INTENT at this key from another txn?
       → conflict resolution of chapter 5
   • is there a committed version ABOVE ts14?
       → WriteTooOld: push this txn's timestamp forward
   • is there a version inside the uncertainty window?
       → ReadWithinUncertaintyIntervalError

Assume none of these fire. n8 evaluates the request: it computes the resulting key-value changes — a write intent at /Table/53/1/4718/0 @ ts14 — without applying them yet. What Raft replicates is the result, not the command, which is what keeps every replica byte-identical.

⑥ Raft: the part that costs milliseconds

n8 proposes the evaluated write to r137’s Raft group.

   n8  append to Raft log ─► fsync ─┐
   n2  append to Raft log ─► fsync ─┼─► ack to n8
   n5  append to Raft log ─► fsync ─┘

   COMMITTED the moment a MAJORITY (2 of 3) has fsynced.
   n8 counts itself, so it needs ONE of n2, n5 — whichever
   answers first. The slow one never delays you.

This is where the time goes, and it is two costs, not one:

   network round trip    0.2–1 ms  same region
                         30–80 ms  across a continent
   fsync on each replica 0.1–1 ms  local NVMe
                         1–10 ms+  network-attached disk

Meanwhile the same thing is happening on n1 for the index entry, in parallel. The transaction waits for both.

⑦ Applying

Once committed, each replica applies the entry to its local Pebble store: into the memtable, into Pebble’s own WAL, later flushed to an SST. Chapter 2’s note matters here — committed and applied are different moments, and a subsequent read at the leaseholder waits for the apply.

   Raft log entry ──► Pebble memtable + WAL ──► SST later

⑧ Committing: the parallel commits trick

The transaction record is written on the range of the first key written, listing the in-flight writes — and, thanks to parallel commits (chapter 5), it goes through consensus alongside the final intents rather than after them.

   WITHOUT parallel commits       WITH

   intents  ──consensus──►        intents ─┐
   record   ──consensus──►        record  ─┴─ consensus
   ═════════════════════          ══════════════════════
   2 × (network + fsync)          1 × (network + fsync)

When every listed write has succeeded, the transaction is committed — implicitly, immediately, with no further round trip. n3 sends INSERT 1 to your client.

   client ◄── "INSERT 0 1"      total ≈ 1–3 ms, one region

⑨ Afterwards, asynchronously

Your client is gone, and work remains:

   • intents are RESOLVED into ordinary values
     (or by the next reader who trips over one)
   • the txn record is eventually cleaned up
   • Pebble flushes and compacts
   • the closed timestamp advances past ts14, making
     the write visible to follower reads      → ch 8
   • changefeeds emit the row                 → ch 12

None of it is on your latency path, and none of it affects correctness if it is delayed.

The same write, when something is in the way

The trace above was the happy path. Three detours are common enough to recognise in a trace.

Another transaction holds an intent on the key. The write queues in that range’s contention queue behind the earlier transaction, and the trace shows a gap with conflicting intent or waiting for … to finish in it. If the blocking transaction is long, this is your latency — and no amount of hardware fixes it.

   n8   evaluate ──► INTENT from txn B at ts13
                     │
                     ├─ push txn B: "commit above me
                     │  or abort" — decided by priority
                     └─ wait, then proceed

A committed version exists above our timestamp. WriteTooOld: the transaction is bumped forward in time, its earlier reads are refreshed (chapter 5), and if the refresh succeeds nothing is visible to you but a slightly later commit timestamp. If it fails, you get 40001.

The gateway’s range cache was stale. DistSender sent the batch to a node that no longer holds the lease, got NotLeaseHolderError, refreshed from the meta ranges and resent. One extra round trip, invisible except in a trace — and utterly routine after a rebalance.

The same write across three regions

Change nothing but the topology, and the arithmetic changes completely:

   replicas: us-east, eu-west, ap-southeast
   client and gateway in eu-west
   leaseholder in us-east

   eu gateway ──75 ms──► us-east leaseholder
                          │
                          ├─ Raft to eu-west   75 ms ✓ majority
                          └─ Raft to ap-se    230 ms  (ignored)
                          │
   eu gateway ◄──75 ms────┘
   ═══════════════════════════════════════════════════
   ≈ 225 ms for ONE INSERT

Two of those three legs are avoidable, and chapter 11 is about avoiding them: move the lease into eu-west (saves the first and last hops), and home the row’s replicas in Europe (makes the quorum leg 10 ms instead of 75 ms). The same statement then costs about 12 ms rather than 225 ms, with no code change at all.

Caution

This is why “we will add regions later and see how it goes” ends badly. The latency is not a tuning problem you discover in load testing — it is arithmetic you can do in advance, and it changes which schema you should have written.

Where the time actually went

   ONE REGION, LOCAL NVMe                 ≈ 1–3 ms
   ├─ parse, plan, encode         ~0.2 ms
   ├─ gateway → leaseholder hop   ~0.3 ms
   ├─ Raft: network + 2 fsyncs    ~1–2 ms   ◄── dominant
   └─ response                    ~0.2 ms

   THREE REGIONS, quorum spanning two      ≈ 40–90 ms
   └─ Raft round trip to the NEARER remote region
      dominates everything else combined

That second box is the single most important number in a multi-region deployment, and chapter 11 exists to make it smaller by moving the quorum, not by tuning anything.

Seeing it yourself

SET tracing = on;
INSERT INTO users (id, email, region) VALUES (99, 'x@y.z', 'eu-west');
SET tracing = off;
SELECT * FROM crdb_internal.session_trace;

Every step above appears in that trace by name: dist sender send, /cockroach.roachpb.Internal/Batch, evaluating Put, proposing command, applied. Better still for a real query:

EXPLAIN ANALYZE (DIST, VERBOSE)
  INSERT INTO users (id, email, region) VALUES (100, 'a@b.c', 'eu-west');

Tip

When a write is slower than you expect, the trace tells you which of three things it is: latching and lock waits (contention — chapter 5), the Raft round trip (distance or disk — chapters 4 and 11), or the apply (an LSM that is behind on compaction — chapter 13). Guessing between those three wastes far more time than tracing does.

What to take from this chapter

• The gateway parses, plans and encodes the row into keys — a row key and one key per secondary index, which may live in different ranges.
• DistSender groups writes by range and sends them to each leaseholder in parallel, refreshing stale routing on the fly.
• At the leaseholder: latches serialise concurrent access briefly, the lock table and MVCC versions are checked for conflicts, and the request is evaluated into the resulting writes — Raft replicates results, not commands.
• The write commits when a majority has fsynced it. The second-fastest replica sets your latency; the slowest never does.
• Parallel commits collapse commit into a single consensus round.
• Afterwards, asynchronously: intent resolution, record cleanup, Pebble flush and compaction, closed-timestamp advance, changefeed emission. None of it is on your latency path.
• In one region a simple write is 1–3 ms, dominated by network plus fsync; across regions it is 40–90 ms, dominated entirely by the round trip to the nearer remote replica.
• SET tracing and EXPLAIN ANALYZE (DIST, VERBOSE) show every step above by name, and separate contention from distance from storage.

Next: reads — why they can be a single local lookup, and how follower reads and AS OF SYSTEM TIME avoid crossing a region at all.