Inside CockroachDB

SQL on any node

The application asks for the blue mug’s catalogue price:

SELECT price FROM products WHERE product_id = 'P7';

The node accepting the connection is the SQL gateway. It parses the statement, plans its execution and returns the result. SQL execution turns the lookup into key-value work. A key identifies an entry; its value holds the associated data. Suppose the connection enters node 4, while node 2 serves the range containing P7.

Node 4 owns the connection. Node 2 serves the range containing P7. They can be the same node, but nothing about accepting the SQL connection requires that. Larger queries can distribute SQL execution too, so a gateway is not necessarily where every join or aggregation runs. The SQL-layer documentation describes that planning boundary.

CockroachDB accepts the PostgreSQL client protocol, allowing familiar drivers and tools to connect. It is a different engine: supported SQL, types, isolation and errors need their own checks. Moving a SQL application to CockroachDB follows that application contract. A successful connection alone has tested very little of it.

Rows and indexes become keys

The primary index holds the table’s row data in primary-key order. Conceptually, P7’s key reads products / primary / P7 / family 0. The stored encoding uses table and index identifiers and encoded column values; the words here make the parts readable. The table prefix separates products from orders. The index prefix separates two orderings of the same table.

A column family groups columns of one row into a key-value pair. Our product table uses the default single family, so its name, price and stock travel together in the value. A row does not mean one independent key per column. Splitting columns into families can change what an update rewrites; it does not divide one stock value into several authorities. The column-family documentation explains the choices.

For Mina’s order history, suppose we add a secondary index on customer identifier, called orders_customer. It puts her entries together for this query:

SELECT order_id, status
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

Mina’s secondary-index entries identify orders 101 and 103. This index does not store their status, so the engine follows each primary key to the corresponding primary-index row to obtain it.

The secondary entries carry 101 and 103, rather than a machine address or a fixed slot in a file. Moving a range therefore need not rewrite every secondary entry that leads to it. The lookup uses the primary keys to find the row data wherever the ranges now live. This index lacks status; a covering index can hold returned columns too, as CockroachDB’s index documentation explains.

Another index is another set of stored keys. Creating it after rows exist must build entries for those rows while accommodating ongoing changes. “Online” does not mean free of processor, storage or network work. CockroachDB schema jobs under load develops the job and application-compatibility questions; here the extra representation explains why a backfill has work to do.

Ranges and routing

A range covers a continuous interval of the encoded keyspace, including its start and excluding its end. A key belongs to one range. A large primary index can occupy many ranges; a small range can cover data from more than one index. A range is therefore neither a table nor an index, and its replicas need not stay on the same machines.

The gateway’s KV routing uses a range descriptor: boundaries and replica locations. Cached descriptors spare repeated metadata lookups. A missing or outdated entry can require a lookup or refreshed routing after a split or move. It does not require asking every node whether it has P7. The distribution layer supplies this view of one ordered map over changing owners.

The same SQL result can cross several such intervals. Reading Mina’s customer-index span and then fetching 101 and 103 can involve different range owners. “One query”, “two returned rows” and “one range request” are different counts.

Replicas, Raft and the leaseholder

Each range has its own Raft group. Its voting replicas agree an ordered history of commands; the group’s elected leader coordinates replication. The leaseholder has authority to serve the range’s ordinary current reads and coordinate its writes. In v26.2, leader leases keep these roles on the same replica, except briefly during transfer. This is a role per range, so one node can lead some ranges and follow others.

A lease allows a current read to use local data without running a new Raft agreement round for every read. Its validity is maintained by the protocol; possessing a copy of P7 does not give another replica the same permission. The leader-leases paper explains why serving authority and leadership must stay aligned.

Now change P7’s catalogue price from 20.00 to 22.00. This is independent of the purchase: stock stays 0, order 103 stays pending, its agreed lines still total 29.00 and it has no payment. The range must first establish agreement on the command. Applying that command to a replica’s local price is a later step, so a durable shared decision can precede every copy displaying 22.00.

The range has three voters, so its majority is two. Once the established leader has the required agreement, it need not wait for the slowest copy to apply the command. A non-voting replica can follow the history without contributing a vote. Count voters in this range, not healthy nodes across the whole cluster. The replication-layer documentation describes both kinds of replica.

If this range loses two of its three voters, healthy nodes holding unrelated ranges cannot supply its missing votes. If it loses only its leader, the remaining majority can establish a suitable replacement, subject to detection, election, communication and storage. That is a possibility of progress, not a completion-time promise. This reasoning assumes fixed membership, surviving durable histories and storage that keeps acknowledged writes.

Replica placement and lease placement

Suppose these three voters occupy separate regions A, B and C, with the lease in A. A current read entering in B normally needs A; a new write in A needs at least one remote voter for a majority. Moving the lease to B changes the read path, while the copies remain in all three regions. Adding a non-voting copy in B would not replace either remote vote.

These are consequences of a stated placement, not latency measurements or a complete region-survival design. Table locality and survival policies place these owners; CockroachDB table locality and region survival develops and verifies those policies.

What each replica stores

On each node, a store contains replicas of many ranges. CockroachDB uses Pebble, an embedded storage engine, to keep their data on local storage. Pebble organises keys in a log-structured merge tree, or LSM. New writes enter an in-memory structure called a memtable; a local write-ahead log protects writes before they reach sorted files.

Raft’s log orders replicated commands for a range. Pebble’s WAL supports crash recovery for one store. Raft state itself is data that the local engine must store. Keeping a replicated decision and maintaining the local files that hold its data are related responsibilities at different boundaries.

A memtable flush produces a sorted string table, or SST, file. These files are immutable. Compaction reads sorted contents and writes replacement files, retiring old files when safe. The application’s one update can therefore cause later storage work too. Files and levels belong to the store’s LSM; a range boundary is not a promise of one file per range. See the storage layer and Pebble’s value-separation explanation for how files and value storage interact.

Versions of the same key

CockroachDB keeps timestamped versions of values using multi-version concurrency control, or MVCC. After the separate price change commits, retained history can include both 20.00 and 22.00. Those are two versions of P7 on a replica, not two replicas that independently chose a price.

A historical read needs both the required retained version and evidence that its target replica can safely serve that history. A current read needs the appropriate current authority. Neither follows just from finding 20.00 or 22.00 on disk. CockroachDB isolation and follower reads develops those choices and their retention limits.

Replication protects surviving copies, but also preserves an unwanted committed change. Setting the price to 2.20 by mistake is still a change the replicas must copy. A backup preserves recoverable earlier history under a separate contract. CockroachDB backup and recovery follows the restore and reconciliation work.

One transaction across ranges

Return to before purchase 103, separately from the price experiment. P7 costs 20.00 and has stock 1; P3 has stock 12. The purchase must create a pending order and two lines, and guard both stock decrements. Its stored work includes more than the visible rows.

Agreement inside a range makes its commands durable and ordered. It does not tell another range whether the whole purchase committed. CockroachDB’s transaction layer supplies the shared outcome and handles provisional writes across ranges. The first range cannot simply call its own stock work committed because its voters agreed it.

A committed purchase leaves stock 0 and 10, the pending order and two lines totalling 29.00. An aborted attempt keeps none of that attempt’s changes. CockroachDB transactions and retries follows native intents, commit, retries and uncertain replies; the transaction-layer documentation specifies the engine’s machinery.

Splits, movement and hotspots

A split divides an ownership interval into two. Moving a replica changes where a copy lives. Transferring a lease changes who serves the range. They can work together to distribute demand, but each changes something different.

Suppose one range contains P3, P7 and P9, with its lease on node 2. Splitting immediately before P7 creates one range for P3 and another for P7 and P9. That boundary alone moves no work to another machine. Moving a replica of the second range to node 4, then transferring its lease there, lets the two ranges have different serving owners.

Independent P3 requests can now use node 2 while P7 and P9 use node 4. If every request instead targets P7, the P3 range is quiet and all active work still reaches one key on node 4. Repeating the split cannot put halves of that single stock decision under independent ownership.

CockroachDB splits growing ranges and can also consider load when choosing a useful boundary. A load-based split needs a key distribution it can separate; a high request count alone does not guarantee one. The load-based splitting documentation describes that distinction.

An advancing index edge creates a related problem. If new keys arrive in increasing order, subsequent writes can keep following the newest range after each split. Old ranges spread while new demand remains concentrated. This differs from repeatedly updating P7, but both can leave added machines with little useful work. CockroachDB query work and hot ranges uses native evidence to distinguish these patterns and weigh alternative keys and indexes.

Reading the cluster’s range evidence

A native range listing connects the model to an actual deployment:

SELECT start_key, end_key, range_id, lease_holder,
       replicas, voting_replicas, non_voting_replicas
FROM [SHOW RANGES FROM TABLE products WITH DETAILS]
ORDER BY start_key;

The key bounds say which interval is covered. The range identifier names that ownership unit; replicas lists its node locations. The voting and non-voting lists distinguish participation in agreement, and lease_holder identifies serving authority at the observation. The SHOW RANGES reference defines the fields and required access.

In the disposable v26.2.8 single-node run, the products listing first showed one covering range, 82. A deliberate manual split at P7 produced ranges 82 and 83. Both still had node 1 as their only replica, voter and leaseholder; neither had a non-voting replica.

The split created a boundary without creating another machine or another copy. This fixture used single-node mode with replication disabled, so it cannot establish the three-voter model’s survival behaviour or regional timing.

The same run returned price 20.00 for the initial lookup and orders 101 and 103 for Mina. After the separate price update, it returned 22.00 for P7. Stock was still 10/0/4, order 103 was pending with a line value of 29.00, and there were no payments. Its orders schema had only the primary index: the added customer-index example above is illustrative, not a measured access path from this run.

A placement snapshot changes as the cluster works. It does not measure demand, prove that a range can currently make progress or validate a maintenance plan. The per-range majority matters even if most of the cluster is healthy. Operating and securing a CockroachDB cluster develops identity, access, health and node-removal procedures from this evidence.

Changing the cluster version

A rolling binary change temporarily removes one node’s resources. The affected ranges still need enough usable replicas to make progress. Counting live machines without checking those ranges can hide a loss of redundancy.

For a major-version upgrade, replacing binaries and finalising the cluster version are separate transitions. Finalisation enables changes that can close the route back to the previous major version; self-hosted deployments enable automatic finalisation by default. The versioned upgrade documentation defines the release rules. Upgrading a CockroachDB cluster turns them into a rehearsed rollout and return procedure.

The range remains the useful unit to follow: which keys it covers, which copies survive and which replica can serve it. Those answers explain where a request goes, what agreement it needs and whether adding or changing machines can help its actual work.