How CockroachDB actually works in production: a mental model for operators

CockroachDB is often introduced as distributed PostgreSQL. In production, that description is a liability. The first time a node self-terminates at 3 a.m. because its clock drifted past 500 ms, or when a single hot range bottlenecks a thirty-node cluster while the others idle, you realize the abstraction leaks. CockroachDB is a stack of specialized subsystems: SQL execution over a transactional KV engine, replicated via Raft, stored in an LSM tree, coordinated by hybrid logical clocks, and throttled by admission control. You cannot debug it as a monolithic database.

A GC pause in the SQL gateway can prevent Raft heartbeat processing. That triggers lease redistribution, which generates snapshot traffic. Snapshots saturate disk I/O, compaction falls behind, and the LSM tree stalls writes. Without the model, you restart nodes. With the model, you spot the memory pressure before the liveness flapping begins.

What it is and why it matters

CockroachDB is a distributed, strongly-consistent SQL database built on a replicated transactional key-value store. The SQL layer is the top of a stack that includes distributed query execution, serializable snapshot isolation via MVCC, multi-raft consensus, and a log-structured merge tree storage engine. Every write flows through this entire stack, and every layer competes for CPU, memory, disk I/O, and network.

The symptoms you see at the SQL layer are often generated three layers down. A spike in P99 latency might originate from LSM compaction debt in Pebble, not from a missing index. A node flapping between live and dead might be a Go GC pause exceeding the liveness heartbeat interval, not a network partition. Diagnose only at the SQL layer and you will misidentify the root cause.

How it works

SQL gateway and DistSQL. Client connections arrive via pgwire at a gateway node. The gateway parses, plans, and either executes locally or distributes the query via the DistSQL engine. DistSQL creates flows: pipelines of processors connected by streams that shuffle data between nodes. A single analytical query can saturate inter-node bandwidth and consume memory on multiple nodes via result buffering. Each client connection also consumes a goroutine and session state memory. Gateway nodes are stateful; a restart drops in-flight DistSQL flows and forces client reconnects.

Transactional KV. Below SQL, the transactional KV layer handles MVCC timestamps, write intents, and conflict resolution. Each transaction acquires a timestamp from the node’s Hybrid Logical Clock (HLC). Contention for the same keys produces transaction pushes and retries; high push counts indicate serializable conflicts, not just slow queries. Uncommitted writes leave intents that the transaction coordinator or encountering transactions must resolve. Abandoned intents create asynchronous cleanup work that adds latency to unrelated reads and writes.

Ranges, leaseholders, and Raft leaders. The keyspace is divided into ranges of approximately 512 MiB by default. Every range is a unit of replication, load balancing, and consensus. A range has multiple replicas (default 3). The leaseholder serves all reads and coordinates writes. The Raft leader drives consensus by replicating log entries to followers. Usually co-located, the roles can diverge during rebalancing or failure recovery. A node with 10,000 ranges runs 10,000 Raft state machines concurrently, consuming CPU for heartbeats, log application, and ticking.

Pebble LSM tree. Each node stores data in Pebble, an LSM tree engine. Writes go to a memtable, then flush to SSTables in Level 0. Background compaction merges SSTables from L0 down through Level 6, reducing read amplification at the cost of write amplification. When compaction falls behind, L0 sublevel count grows. You can observe this via L0 sublevel metrics. Past 10 to 20 sublevels, read latency spikes nonlinearly. Past 20, Pebble may stall writes entirely to prevent unrecoverable tree imbalance.

Hybrid logical clocks. HLC combines physical wall-clock time with a logical counter. CockroachDB enforces a maximum clock offset between nodes (default 500 ms). If a node detects clock skew beyond a safety threshold relative to peers, it self-terminates. Smaller offsets within the allowed window cause read uncertainty restarts, silently inflating tail latency.

Admission control. CockroachDB queues work into admission control classes including kv, sql-kv-response, sql-sql-response, elastic-cpu, and store-write. The store-write queue is tied directly to LSM L0 health; it begins shaping regular traffic at 5 L0 sublevels and elastic traffic at 1 sublevel. Queuing is protective, but sustained queuing means the system is at capacity and has no burst headroom.

Node liveness and background processes. Each node maintains a liveness record via periodic heartbeats. If renewal fails, the cluster marks the node dead after a timeout and redistributes its leases and replicas. A node that is overloaded but still heartbeating can appear live while serving severely degraded performance. Background processes compete for the same resources as foreground traffic: range splits and merges, rebalancing, lease transfers, Raft snapshots, schema change backfills, MVCC GC, intent resolution, time-series pruning, and statistics collection.

flowchart LR
    Client[Client pgwire] -->|submit| Gateway[SQL Gateway]
    Gateway -->|plan| DistSQL[DistSQL Flows]
    DistSQL -->|batch| KV[Transactional KV]
    KV -->|resolve| Leaseholder[Leaseholder Replica]
    Leaseholder -->|propose| Raft[Raft Leader]
    Leaseholder -->|read/write| Pebble[Pebble LSM]
    Raft -->|append| Pebble
    Pebble -->|fsync| Disk[(Disk WAL SSTables)]

Where it shows up in production

The boundaries between these subsystems are where incidents form. These archetypes repeat across production deployments.

LSM compaction death spiral. Write rate exceeds compaction throughput. L0 sublevels climb past 10, then 20. Read amplification increases, slowing reads and compaction. Eventually Pebble stalls writes to protect the tree. During stalls, the node cannot append to the Raft log and loses leadership and leases. If multiple nodes hit this after bulk ingest or backup, cluster-wide unavailability follows. Recovery requires throttling writes or adding temporary disk IOPS, not restarting nodes.

Raft liveness failure. A node becomes slow due to a disk stall, CPU saturation, or a Go GC pause. It misses Raft heartbeats. The cluster revokes its leadership and leases. Redistribution creates snapshot traffic and lease transfers that further load surviving nodes, sometimes triggering cascades.

Clock skew crisis. NTP misconfiguration or VM live migration causes clock drift. The first symptom is often readwithinuncertainty restarts, which operators misattribute to contention. If drift exceeds the configured max-offset, nodes self-terminate. In VM environments where nodes share an NTP source, multiple nodes can drift together, causing quorum loss across system ranges. Monitor clock offset between nodes, not just local NTP offset.

Hot range bottleneck. A single range receives disproportionate traffic, often from sequential primary keys, timestamp-prefixed indexes, or single-row counters. Every request funnels through one leaseholder. That node saturates while the rest of the cluster idles, a pattern aggregate CPU metrics hide. Look for per-range QPS imbalance in the DB Console or range reports.

Intent accumulation cascade. Long-running or abandoned transactions leave write intents across the keyspace. Other transactions must resolve them before proceeding. If intent resolution cannot keep up with the creation rate, the system spends more time cleaning up than processing new requests, and throughput collapses.

Memory pressure to GC thrashing to liveness loss. Large SQL queries, goroutine leaks, or misconfigured memory budgets grow the Go heap. GC frequency and pause duration increase. If a pause exceeds the liveness heartbeat interval, the node loses its lease. It recovers, reacquires leases, then repeats, creating oscillating availability that looks like a network problem but is memory pressure.

Common operational blind spots

Experienced operators still miss these interactions because they treat CockroachDB as a black-box SQL store.

Assuming leaseholder and Raft leader are identical. They usually are, but during rebalancing, drains, or slowdowns they separate. Reads go to the leaseholder; writes are proposed through the Raft leader. Reasoning about traffic as if both roles always live on the same node causes misreads during topology changes. Check range status when latency spikes during rolling restarts.

Ignoring L0 sublevel count until write stalls occur. Teams monitor disk utilization and IOPS but not LSM health. L0 gives 10 to 30 minutes of warning before stalls, yet most teams never instrument it. Instrument L0 sublevel count in your monitoring; it is cheaper to fix at 10 sublevels than after a write stall.

Not distinguishing transaction restart causes. A retry rate spike is not a single signal. writetooold means contention and likely a schema problem. readwithinuncertainty means clock skew and an infrastructure problem. Lumping them together sends you to the wrong subsystem. Break down transaction statistics by retry reason.

Monitoring only aggregate SQL latency. A slow query among thousands of fast ones barely moves cluster P99, but it kills a specific endpoint. Without per-statement-fingerprint latency, you miss plan regressions and hot paths. Track per-fingerprint latency via statement statistics or the DB Console Statements page.

Forgetting that background work is adversarial. Compaction, rebalancing, snapshots, schema backfills, and MVCC GC compete with foreground traffic for CPU, disk I/O, and network. A routine backup can push a store from healthy L0 into write stalls if disk headroom is thin. Schedule backups and schema changes outside peak hours, or throttle them via cluster settings.

Ignoring range count as a scaling dimension. Teams add data and watch CPU, memory, and disk. They miss that a node with 100,000 ranges behaves differently from one with 10,000 because of per-range Raft CPU overhead. Range count grows with

[OUTPUT TRUNCATED: Response exceeded output token limit.]