CockroachDB
Design CockroachDB systems with ranges, Raft replication, key distribution, serializable retries, multi-region locality, and recovery.
What is CockroachDB?
CockroachDB is a distributed SQL database: applications use relational tables, indexes, constraints, and transactions while a cluster spreads the underlying data across multiple machines. Every node can receive SQL traffic, and the cluster presents one logical database even when a query touches data stored elsewhere.
It matters when one database must combine three requirements:
- keep SQL constraints and multi-row transactions;
- scale data and traffic beyond one machine;
- continue serving through a node or failure-domain outage.
The core invariant is stronger than "there are several copies." A write is committed only after the replicas responsible for every affected key agree through Raft. If the required quorum cannot form, CockroachDB stops that write instead of allowing independent histories to diverge.
CockroachDB does not remove distributed-systems trade-offs. Key design, transaction contention, replica placement, network latency, backups, and client-side retry behavior still determine whether a production system is correct and fast.
Follow one SQL request into the distributed keyspace
CockroachDB stores tables and indexes in one ordered key-value space. It divides that space into contiguous ranges. Each range is replicated, and one replica holds the lease used to coordinate reads and propose writes for that range.
From SQL to a replicated range
Any node can be the SQL gateway; the key determines which range and leaseholder own the work.
PostgreSQL wire protocol
Application
Sends a SQL statement or transaction through a connection pool. A load balancer can route the connection to any healthy CockroachDB node.
Plan
Gateway node
Parses and optimizes SQL, converts the plan into key-value operations, and routes each operation toward the range that owns its key.
Coordinate
Range leaseholder
Serves the range's consistent reads and proposes writes to that range's Raft group.
Commit
Replica quorum
Persists and agrees on the command. A majority acknowledgement makes the write durable for that range.
Four boundaries are easy to confuse:
- A node is one running CockroachDB process with local storage.
- A range is a contiguous ownership slice of the keyspace.
- A replica is one copy of a range on a node.
- A leaseholder is the replica currently coordinating requests for that range; it is not a permanent primary for the whole database.
Tables can span many ranges, and one transaction can span several tables and ranges. CockroachDB hides that distribution from SQL syntax, but network hops, contention, and quorum work still appear in latency and capacity.
Design keys for distribution, locality, and access paths
The primary key orders each table's primary index. Secondary indexes create additional ordered keyspaces. A key therefore controls more than uniqueness: it influences where new writes land, which rows remain adjacent, and how many ranges a query must visit.
Local order, hot tail
Sequential key
An increasing ID keeps nearby values together, but new inserts converge on the active end of the index. Splitting historical ranges does not divide that single incoming write frontier.
Spread point writes
Random UUID
Well-distributed UUID values send inserts across many key ranges. Point lookups remain direct, while scans by creation time need a separate ordered index.
Bounded write buckets
Hash-sharded index
A computed hash bucket spreads a sequential logical key across a fixed number of prefixes. It reduces the hot tail but makes ordered scans fan out across buckets.
Two common mistakes survive a quick architecture review:
- A random primary key does not distribute a secondary index whose leading column is an increasing timestamp.
- More nodes do not fix a hot range when the write pattern still targets one key interval.
Measure range-level request rate, CPU, leaseholder load, contention, and replica health. Cluster averages can look comfortable while one range limits the entire write path.
Lab 1: expose the hot range before adding nodes
Choose a key strategy, range count, node count, and peak write rate. The model shows whether load spreads across ownership ranges or remains concentrated on one write frontier.
Change at least two controls and compare:
- the average writes per node;
- the hottest range's request rate;
- the number of ranges doing active write work;
- the fan-out paid by an ordered scan.
Map the active write frontier
Loading key strategies, range limits, and workload controls.
Loading the range model...
This is a pressure model, not a CockroachDB benchmark. A real range's limit depends on row size, index maintenance, transaction shape, storage, replication latency, contention, and background work. Use production measurements to replace every illustrative capacity number.
Treat serializable retries as part of the application contract
CockroachDB supports distributed ACID transactions across arbitrary rows and tables. Its default SERIALIZABLE isolation level guarantees that committed transactions behave as if they ran in a valid serial order, even when their key-value operations span ranges.
Concurrent transactions can still conflict. CockroachDB retries some work internally, but it can return SQLSTATE 40001 when it cannot safely retry without the client. The application must rerun the entire transaction from the beginning.
1 Bound the invariant
Begin one transaction
Read and write only the rows needed for one business decision. Keep remote API calls and user interaction outside the database transaction.
2 Preserve correctness
Apply guarded mutations
Use constraints, conditional updates, and deterministic values. Lock rows deliberately when the chosen access pattern benefits from it.
3 Serializable order
Commit or receive 40001
Commit succeeds only when the transaction can join a valid serial history. A retry error means the client has not completed the business operation.
4 Whole transaction
Back off and replay
Discard attempt-local results, add bounded jitter, then rerun every statement against fresh state. Keep an idempotency key outside the retry loop.
Reduce retries before increasing retry limits:
- remove shared counters and other single-row write bottlenecks;
- keep transactions small in rows, bytes, statements, and wall-clock time;
- send related statements in one batch when practical;
- acquire locks in a stable order;
- keep side effects such as email or payment calls behind a durable outbox;
- alert on retry rate, contention time, and exhausted attempts.
Place data by its home, read path, and failure goal
A multi-region database separates two decisions:
- Table locality optimizes where a table or row is read and written.
- Survival goal defines the failure scope for which the database remains available.
One table home
REGIONAL BY TABLE
Keep a table's leaseholders near one region. Reads and writes are fast from that home and pay network distance from other regions. This is the default multi-region table locality.
A home per row
REGIONAL BY ROW
Place each row near the region that owns or most often updates it. Include the region in related access paths so transactions and foreign keys do not accidentally scatter.
Read locally everywhere
GLOBAL
Serve a read-mostly reference table with low read latency from every region. Writes coordinate more broadly, so frequently updated data is usually a poor fit.
The default zone survival goal tolerates a zone failure. A region survival goal keeps reads and writes available through loss of an entire region, but uses more replicas and adds at least an inter-region round trip to writes. A table locality does not silently upgrade the database's failure guarantee.
Lab 2: trade locality latency for region survivability
Choose the gateway region, workload, table locality, survival goal, and failure state. Watch the read path, write coordination path, transaction span, replica count, and stated availability guarantee change together.
Use the lab to answer two different questions:
- Is the data homed near the request that changes it?
- Does the database promise progress through the failure being tested?
Place data against latency and failure goals
Loading regional paths, locality policies, and survival goals.
Loading the regional model...
Latency values are illustrative network budgets. Validate a real design with measured round-trip times, a representative schema and transaction mix, regional failover exercises, and application behavior during retry storms.
Operate the database by ranges and invariants
Node health is necessary but not sufficient. Production reviews should connect user-facing objectives to the distributed boundary that can break them.
Observe the serving path
- Track SQL latency and errors by statement fingerprint and application name.
- Track transaction retries, contention duration, lock wait, and admission queueing.
- Inspect hot ranges, leaseholder imbalance, under-replicated ranges, and unavailable ranges.
- Watch storage capacity, compaction pressure, network saturation, clock health, and certificate expiry.
- Separate foreground demand from backup, restore, rebalancing, schema change, and changefeed work.
Recover more than a replica
- Test loss of one node, one zone, and one region against the configured survival goal.
- Keep backups independent from live replicas; replicated deletes and corrupt application writes can reach every copy.
- Restore into an isolated environment and verify row counts, constraints, checksums, and application reads.
- Define recovery time and recovery point objectives for accidental deletion and corruption, not only infrastructure failure.
- Rehearse client reconnect, transaction retry, and connection-pool behavior during lease movement and failover.
Change safely
- Apply schema changes with representative data volume and active traffic.
- Cap connection pools per workload so every application instance cannot overwhelm every SQL gateway.
- Add indexes from observed access paths and include their write amplification in capacity tests.
- Roll versions according to CockroachDB's supported upgrade path and keep a tested rollback boundary.
- Validate locality and survival settings after adding or removing regions.
Choose CockroachDB for the contract it actually provides
CockroachDB is a strong fit when the workload needs:
- relational constraints and transactions across distributed data;
- horizontal growth without application-managed shards;
- one SQL endpoint across nodes or regions;
- automatic replica placement and rebalancing;
- an explicit zone or region survival goal.
Evaluate another design when:
- one machine comfortably handles the workload and operational simplicity is the main objective;
- most writes update one unavoidable hot key;
- every transaction spans distant regions but the latency budget is local;
- the application cannot safely retry serializable transactions;
- the team cannot operate certificates, upgrades, backups, capacity, and failure testing for a distributed database.
Before production, write down the primary key strategy, hottest expected range, transaction retry boundary, table locality, survival goal, backup restore evidence, and client behavior during failover. Those decisions are the architecture; the node count is only one input.