How CockroachDB Stores Data: KV Engine Explained with Real Internals
Foundation: Everything Is KV
How CockroachDB Stores Data: KV Engine Explained with Real Internals
Foundation: Everything Is KV
CRDB stores ALL data — tables, indexes, metadata — as key-value pairs in RocksDB (now Pebble, a Go port of RocksDB). There are no “rows” at the storage layer. A SQL row is encoded into one or more KV pairs.
SQL Layer: Tables, Rows, Indexes, Columns
│
▼ encode/decode
KV Layer: key → value
│
▼ store
Pebble: Sorted SST files on disk (LSM tree)Key Encoding Format
Every key in CRDB follows this structure: /Table/<table_id>/<index_id>/<index_values…>/<column_family_id>
Each part is encoded using a type-aware binary encoding called “key encoding” that preserves sort order
Sort order is critical — because ranges are split on key boundaries, and range scans must work correctly, the key encoding must sort the same way the data sorts.
Concrete Example: The Holdings Table
CREATE TABLE holdings (
user_id INT,
stock_symbol VARCHAR(10),
qty INT,
avg_price DECIMAL,
last_updated TIMESTAMP,
PRIMARY KEY (user_id, stock_symbol)
);
CRDB assigns internal IDs:
Table ID: 54
Primary Index ID: 1 (always 1 for primary key)How Primary Key Rows Are Stored
For each row, CRDB creates one KV entry per column family (by default all non-PK columns go into column family 0):
-- Insert this row:
INSERT INTO holdings VALUES (1001, 'INFY', 50, 1423.50, '2026-04-23 10:00:00');
Key:
/Table/54/1/1001/"INFY"/0
│ │ │ │ │ │
│ │ │ │ │ └── column family ID (0 = default)
│ │ │ │ └────── stock_symbol value ("INFY")
│ │ │ └──────────── user_id value (1001)
│ │ └─────────────── index ID (1 = primary index)
│ └───────────────── table ID (54)
└──────────────────────── system prefix
Value:
{qty: 50, avg_price: 1423.50, last_updated: '2026-04-23 10:00:00'}
Encoded as: protobuf/columnar binary
Only non-PK, non-family-key columns stored here
PK columns (user_id, stock_symbol) are already in the KEY
so they are NOT repeated in the value
Full KV pair:
KEY: /Table/54/1/1001/"INFY"/0
VALUE: {qty:50, avg_price:1423.50, last_updated:'2026-04-23T10:00:00'} Multiple Rows — How They Look in Sorted Order
INSERT INTO holdings VALUES
(1001, 'INFY', 50, 1423.50, ...),
(1001, 'RELI', 100, 2891.00, ...),
(1001, 'TCS', 25, 3456.00, ...),
(2005, 'INFY', 200, 1410.00, ...),
(2005, 'WIPRO', 75, 412.00, ...);
KV store (sorted by key — this is how Pebble stores them):
KEY VALUE
─────────────────────────────────────────────────────────────────
/Table/54/1/1001/"INFY"/0 → {qty:50, avg_price:1423.50, ...}
/Table/54/1/1001/"RELI"/0 → {qty:100, avg_price:2891.00, ...}
/Table/54/1/1001/"TCS"/0 → {qty:25, avg_price:3456.00, ...}
/Table/54/1/2005/"INFY"/0 → {qty:200, avg_price:1410.00, ...}
/Table/54/1/2005/"WIPRO"/0 → {qty:75, avg_price:412.00, ...}
Notice:
- All of user 1001's rows are contiguous (sorted together)
- Within user 1001, stocks are sorted alphabetically
- user 2005's rows come after ALL of user 1001's rows
- This is why range scans by user_id are fast — sequential reads
This sort order is the entire reason CRDB uses key encoding that preserves
order. A range scan WHERE user_id = 1001 just needs to read a contiguous block
of KV pairs.How a Primary Key Lookup Works
SELECT qty FROM holdings WHERE user_id = 1001 AND stock_symbol = 'INFY';
Step 1: Encode the lookup key
/Table/54/1/1001/"INFY"/0
Step 2: Point lookup in Pebble (like a dictionary lookup)
Pebble uses bloom filters → O(1) check if key exists
Then binary search in SST files
Step 3: Found the KV pair
KEY: /Table/54/1/1001/"INFY"/0
VALUE: {qty:50, avg_price:1423.50, ...}
Step 4: Decode value → return qty=50
Total: single KV read — very fastColumn Families — Splitting One Row Into Multiple KV Pairs
By default all columns go in one KV pair (family 0). But you can split them:
CREATE TABLE holdings (
user_id INT,
stock_symbol VARCHAR(10),
qty INT,
avg_price DECIMAL,
last_updated TIMESTAMP,
PRIMARY KEY (user_id, stock_symbol),
FAMILY core (qty, avg_price), -- family 0
FAMILY metadata (last_updated) -- family 1
);
Now each row becomes TWO KV pairs:
/Table/54/1/1001/"INFY"/0 → {qty:50, avg_price:1423.50}
/Table/54/1/1001/"INFY"/1 → {last_updated:'2026-04-23T10:00:00'}
Why useful?
If you frequently query qty and avg_price but rarely last_updated,
family 1 is never read → less I/O. Pebble can skip it entirely. Secondary Indexes
CREATE INDEX idx_symbol ON holdings (stock_symbol);
CRDB assigns this index ID 2.
For each row, CRDB creates an additional KV pair for the secondary index:
Row: (user_id=1001, stock_symbol='INFY', qty=50, ...)
Primary KV (index 1):
KEY: /Table/54/1/1001/"INFY"/0
VALUE: {qty:50, avg_price:1423.50, last_updated:...}
Secondary index KV (index 2):
KEY: /Table/54/2/"INFY"/1001/0
│ │ │
│ │ └── primary key appended (to make key unique)
│ └───────── index column value (stock_symbol)
└────────────── index ID (2)
VALUE: {} (empty — all needed info is in the key)
All 5 rows' secondary index entries, sorted:
KEY VALUE
─────────────────────────────────────────────────────────────────
/Table/54/2/"INFY"/1001/0 → {} ← user 1001 holds INFY
/Table/54/2/"INFY"/2005/0 → {} ← user 2005 holds INFY
/Table/54/2/"RELI"/1001/0 → {}
/Table/54/2/"TCS"/1001/0 → {}
/Table/54/2/"WIPRO"/2005/0 → {}
Sorted by stock_symbol first — so a query like:
SELECT * FROM holdings WHERE stock_symbol = 'INFY'
reads a contiguous block: /INFY/1001 and /INFY/2005How the lookup works:
SELECT qty FROM holdings WHERE stock_symbol = 'INFY';
Step 1: Scan secondary index for "INFY"
Read: /Table/54/2/"INFY"/1001/0 → {} → extract primary key: (1001, "INFY")
Read: /Table/54/2/"INFY"/2005/0 → {} → extract primary key: (2005, "INFY")
Step 2: For each primary key found, go back to primary index
Read: /Table/54/1/1001/"INFY"/0 → {qty:50, ...} ← INDEX BACKFILL (join)
Read: /Table/54/1/2005/"INFY"/0 → {qty:200, ...} ← INDEX BACKFILL (join)
Step 3: Return qty values
This is called an "index join" — two KV lookups per row
Secondary index → get PK → primary index → get columnsUnique Index
CREATE UNIQUE INDEX idx_unique_symbol_user ON holdings (stock_symbol, user_id);
For a unique index, CRDB does NOT append the primary key to make the key unique
(because the index itself guarantees uniqueness):
Non-unique index key: /Table/54/2/"INFY"/1001/0 ← PK appended
Unique index key: /Table/54/4/"INFY"/1001/0 ← same here because
(symbol, user_id) IS unique
but:
Unique index key (single column): /Table/54/5/"INFY"/0
← no PK appended
← value contains PK
VALUE: {user_id: 1001} ← primary key stored in value
Lookup: find row where stock_symbol = 'INFY'
1. Read unique index → get PK (1001, "INFY")
2. Read primary index with PK → get full row MVCC — Multiple Versions of the Same Key
CRDB is a multi-version store. Every KV write creates a new version with a timestamp. Old versions are kept until garbage collection.
User updates qty from 50 to 75:
Before update:
KEY: TIMESTAMP VALUE
/Table/54/1/1001/"INFY"/0 @12:00:00.100 {qty:50,avg_price:1423.50}
After update:
/Table/54/1/1001/"INFY"/0 @12:00:05.200 {qty:75,avg_price:1423.50} ← newest
/Table/54/1/1001/"INFY"/0 @12:00:00.100 {qty:50,avg_price:1423.50} ← old version
A read at T=12:00:06 → sees qty=75 (latest version)
A read at T=12:00:03 → sees qty=50 (version before update)
(this is how AS OF SYSTEM TIME queries work) GC runs periodically (default 25h TTL) → deletes old versions
This is how CRDB provides snapshot isolation and time-travel queries without locking.
How Ranges Map to KV Keys
Ranges are just contiguous spans of the KV keyspace:
All possible keys sorted:
/Table/54/1/1/... ← start of holdings table
/Table/54/1/1001/"INFY"/0
/Table/54/1/1001/"RELI"/0
/Table/54/1/1001/"TCS"/0
/Table/54/1/2005/"INFY"/0
/Table/54/1/2005/"WIPRO"/0
...
/Table/54/1/500000/... ← end of holdings table
/Table/54/2/"INFY"/... ← secondary index starts here
...
Range split (at ~512MB):
Range 3a: /Table/54/1/1/... → /Table/54/1/250000/...
Range 3b: /Table/54/1/250001/... → /Table/54/1/500000/...
user_id 1001 → Range 3a (leaseholder: Node1)
user_id 260000 → Range 3b (leaseholder: Node2)
Writes to user 1001 → Node1
Writes to user 260000 → Node2
Parallel, no contentionFull Picture
SQL row: (user_id=1001, stock_symbol='INFY', qty=50, avg_price=1423.50)
Stored as KV pairs:
Primary Index:
/Table/54/1/1001/"INFY"/0 @T1 → {qty:50, avg_price:1423.50}
Secondary Index (stock_symbol):
/Table/54/2/"INFY"/1001/0 @T1 → {}
Covering Index (stock_symbol STORING qty):
/Table/54/3/"INFY"/1001/0 @T1 → {qty:50} - All sorted in Pebble LSM tree on disk.
- Multiple MVCC versions per key for snapshot isolation.
- Split into ranges (~512MB) → each range is a Raft group.
- Leaseholder of each range guarantees reads are current
The encoding guarantees that: — Primary key lookups = single KV point read — Range scans = sequential KV reads (fast, cache-friendly) — Secondary index lookups = index scan + primary index join (unless covering) — MVCC = multiple timestamped versions, GC’d after TTL
메타데이터
- post_id
- dee0f2d088b5
- slug
- crdb-key-value-storage-deep-dive-dee0f2d088b5
- url
- https://medium.com/@meenakshi_kumari/crdb-key-value-storage-deep-dive-dee0f2d088b5
- canonical_url
- https://medium.com/@meenakshi_kumari/crdb-key-value-storage-deep-dive-dee0f2d088b5
- author_url
- https://medium.com/@meenakshi_kumari
- status
- ok
- fetched_at
- 2026-07-13 06:23:13
