CockroachDB sequential primary key hotspot: SERIAL, timestamps, and write skew

One node in your CockroachDB cluster runs hot while the others sit near idle. Write latency for specific tables climbs into hundreds of milliseconds. Transaction restarts tagged writetooold increase steadily alongside the insert rate. The cluster has plenty of aggregate CPU, memory, and disk headroom, but the workload cannot use it.

CockroachDB stores data in an ordered keyspace divided into ranges of approximately 512 MiB. Each range has a single leaseholder that serves all reads and coordinates all writes for keys in that range. When primary keys are sequential (SERIAL, auto-incrementing integers, timestamps), every new insert lands near the end of the keyspace, concentrated in the same range. That range’s leaseholder becomes a single-node bottleneck.

CockroachDB’s load-based splitter tries to break up hot ranges automatically, but sequential keys offer no balanced split point. The range stays intact and grows hotter. The fix requires changing key distribution, not tuning cluster settings.

Why sequential keys concentrate writes

CockroachDB’s SERIAL type maps to INT8 with a default of unique_rowid(). That function produces a 64-bit integer combining a timestamp component with a node identifier. The values are time-sortable: each new insert targets a key after all previous ones. All writes concentrate on the last range of the table.

Because the leaseholder for that single range serves every insert, that node saturates while the rest of the cluster idles. Concurrent transactions writing to overlapping keys conflict under CockroachDB’s serializable isolation, producing writetooold transaction restarts. A writetooold restart means the transaction tried to write a key that another transaction already wrote at a higher timestamp. Under a hotspot, many transactions target the same narrow keyspace region simultaneously.

Load-based splitting evaluates hot ranges for split opportunities. For uniformly distributed keys (UUID v4), the splitter finds a midpoint that evenly divides requests and creates two ranges. For sequential keys, no balanced split point exists. Every candidate split leaves nearly all traffic on one side. The splitter logs that it cannot find a balanced division and leaves the range alone. Repeated occurrences of this log message confirm a sequential hotspot.

The same problem affects timestamp-prefixed secondary indexes. Even when the primary key uses UUID v4, a secondary index whose leading column is a timestamp (INDEX (created_at), INDEX (last_seen_at)) creates a moving hotspot. All new inserts land at the end of the index, concentrating writes on one range.

flowchart LR
    A["App INSERT workload"] --> B{"Primary key pattern?"}
    B -->|SERIAL / timestamp| C["All writes to last range"]
    B -->|UUID v4 / hash| D["Writes spread across ranges"]
    C --> E["Leaseholder saturates"]
    E --> F["writetooold restarts climb"]
    E --> G["One node CPU hot"]
    G --> H["Splitter cannot balance range"]
    D --> I["Cluster scales horizontally"]

Common causes

Quick checks

These commands are read-only and safe to run during production traffic. The crdb_internal.ranges query is admin-only and performs an expensive cluster-wide RPC, so use it for diagnosis, not continuous monitoring.

-- Identify the hottest ranges by queries per second
SELECT range_id, start_pretty, table_name, lease_holder, queries_per_second
FROM crdb_internal.ranges
ORDER BY queries_per_second DESC
LIMIT 20;

# Check writetooold restart rate
curl -s http://localhost:8080/_status/vars | grep txn_restarts

# Check per-node CPU for asymmetry
curl -s http://localhost:8080/_status/vars | grep -E 'sys_cpu_(user|sys)_ns'

# Check leaseholder distribution across nodes
curl -s http://localhost:8080/_status/vars | grep leases_count

-- Examine the primary key and index definitions
SHOW CREATE TABLE <database>.<table>;

# Check SQL latency for the affected tables
curl -s http://localhost:8080/_status/vars | grep sql_service_latency

How to diagnose it

1. Confirm the asymmetry. Compare per-node CPU utilization. A hot range shows one node with CPU significantly elevated while peers with similar range counts sit at moderate levels. Compare leases_count per node to rule out general lease imbalance.

2. Identify the hot range. Run the crdb_internal.ranges query above. Look for a range with queries_per_second more than 10x the table or cluster average. The start_pretty and table_name columns identify the table and key region.

3. Examine the key pattern. Run SHOW CREATE TABLE on the table from step 2. Look for SERIAL, unique_rowid(), auto-increment behavior, or timestamp columns in the primary key. Check secondary indexes for timestamp-prefixed columns.

4. Confirm writetooold correlation. Check whether txn_restarts with writetooold cause tracks the insert volume for the affected table. If restarts rise and fall with insert rate, the hotspot is the source.

5. Check for splitting failures. Search CockroachDB logs for messages about load-based splitting being unable to find a balanced split. Repeated messages on the same range confirm that automatic mitigation is not working.

Metrics and signals to monitor

Fixes

Short-term: manual range splits

ALTER TABLE ... SPLIT AT forces a range split at a specific key value. This distributes the hot range across two leaseholders temporarily.

-- Force a split at a specific value (adjust to your keyspace)
ALTER TABLE <database>.<table> SPLIT AT VALUES (<value>);

This is a stopgap. Sequential keys immediately create a new hotspot at the end of the newly split range. Use this only to buy time while preparing a schema change.

UUID v4 primary keys (recommended for new tables)

Cockroach Labs recommends UUID v4 for all primary keys. UUID v4 values are randomly distributed, so inserts spread evenly across the keyspace and across ranges.

-- New table with UUID v4 primary key
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data BYTES
);

For existing tables, migrating from SERIAL to UUID requires a data backfill. Plan this as a schema change with sufficient time and disk I/O headroom.

If you need integer keys but want better distribution, consider setting serial_normalization to unordered_rowid mode. This uses unordered_unique_rowid(), which scrambles the key ordering. It does not guarantee ordering and may not scatter as uniformly as UUID v4.

Hash-sharded indexes

When you need sequential ordering for range scans but want to distribute writes, hash-sharded indexes distribute sequential traffic across ranges by hashing the index key into buckets.

-- Create a hash-sharded index on a sequential column
CREATE INDEX idx_events_created ON events (created_at)
    USING HASH WITH BUCKET_COUNT = 8;

Tradeoffs:

• Write throughput improves because writes spread across bucket ranges instead of concentrating on one.
• Read performance degrades for scans that must check each bucket separately.
• Bucket count above node count yields diminishing returns. Start with a count close to your node count and increase as the cluster grows.
• Cannot be used with explicit PARTITION BY (including REGIONAL BY ROW partitioning on crdb_region).
• Since v22.1, the shard column is virtual (not stored), so new hash-sharded indexes do not require a backfill. Indexes created before v22.1 use a stored column and may backfill. Drop and recreate to avoid backfill overhead.

Timestamp-prefixed secondary indexes

Even with a UUID primary key, a secondary index like INDEX (created_at) creates the same hotspot. All new inserts land at the end of the index.

Options:

• Use a hash-sharded version of the index (USING HASH WITH BUCKET_COUNT = N).
• Prefix the index with a higher-cardinality column to distribute writes: INDEX (user_id, created_at) instead of INDEX (created_at).

Prevention

• Default to UUID v4 for all new tables. Use gen_random_uuid() as the primary key default.
• Audit secondary indexes. Any index whose leading column is a timestamp, serial, or auto-increment value creates a hotspot under write load.
• Monitor per-range QPS distribution. Prefer Prometheus metrics (_status/vars) for continuous monitoring rather than polling crdb_internal.ranges, which is an expensive cluster-wide RPC. Alert on any range exceeding 10x the average.
• Watch writetooold restart rates. A rising writetooold rate that tracks insert volume is the earliest quantitative signal of a sequential hotspot.
• Use hash-sharded indexes when sequential ordering is required. Accept the read scan penalty in exchange for write distribution.
• Review table designs during schema review. Catch sequential key patterns before they reach production.

How Netdata helps

• Per-second metric granularity catches CPU asymmetry between nodes before it becomes a user-visible latency problem. A hot range shows up as one node’s CPU diverging from its peers within seconds.
• Correlating txn_restarts (writetooold cause) with per-node CPU and sql_service_latency on a single timeline makes the sequential hotspot pattern immediately recognizable. The combination of one node hot, writetooold climbing, and INSERT latency rising is diagnostic.
• leases_count per store reveals leaseholder skew without requiring an expensive crdb_internal query. If one node holds disproportionate leases for a hot table, it confirms the bottleneck.
• ML-based anomaly detection flags the CPU asymmetry and restart rate deviation even when no static threshold has been set, which is useful for gradually emerging hotspots as write volume grows over weeks.
• The storage_l0_sublevels metric distinguishes a hot range problem from a compaction death spiral. If L0 is low but one node is CPU-saturated, the issue is key distribution, not storage health.

Netdata’s CockroachDB monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• CockroachDB clock_offset_meannanos high: catching clock drift before self-termination
• CockroachDB clock skew cascade: how shared NTP drift causes quorum loss
• CockroachDB clock synchronization error: this node is more than 500ms away from at least half of the known nodes
• CockroachDB compaction backlog growing: when Pebble can’t keep pace with writes
• CockroachDB context deadline exceeded: timeouts, slow ranges, and overload
• CockroachDB storage_l0_sublevels climbing: the earliest warning of write stalls
• CockroachDB LSM compaction death spiral: L0 sublevels, read amplification, and write stalls
• CockroachDB monitoring checklist: the signals every production cluster needs
• CockroachDB monitoring maturity model: from survival to expert
• CockroachDB node liveness failure: heartbeats, lease redistribution, and flapping
• CockroachDB Pebble write stalls: when the storage engine refuses writes
• CockroachDB Raft liveness failure cascade: slow node, lost leases, rolling unavailability