CockroachDB hot range bottleneck: one leaseholder saturated while the cluster idles

One node runs hot while the rest of the cluster idles. CPU on that node is 80%+, but others hover around 20-30%. SQL latency is elevated, but only for specific tables. Transaction retry rates climb with writetooold as the dominant restart cause. No range is unavailable, no node has lost liveness, and disk I/O is within normal bounds. The cluster has aggregate capacity, but a single range is funneling all its traffic through one leaseholder.

This is a hot range bottleneck. The root cause is almost always a monotonic key pattern in your primary key or index that concentrates writes and reads onto a narrow slice of the keyspace. CockroachDB’s range-level architecture means all requests for a given range pass through its leaseholder. When one range receives disproportionate traffic, that leaseholder becomes a serial bottleneck for the entire workload, regardless of how much capacity the rest of the cluster has.

The asymmetry is the diagnostic fingerprint. If all nodes are equally loaded, the problem is elsewhere.

What this means

CockroachDB divides its keyspace into ranges of approximately 512 MiB each. Every range has multiple replicas (default 3) spread across nodes. One replica is the leaseholder, which serves all reads for that range and coordinates all writes through Raft consensus. This provides strong consistency, but means traffic for any single range is serialized through one node.

When a range receives disproportionate traffic from sequential key patterns, the leaseholder node saturates. The cluster cannot redistribute this load because traffic is pinned to one range and therefore one leaseholder. Adding nodes does not help. Scaling vertically on the saturated node helps only marginally. The fix requires changing the data access pattern so traffic spreads across multiple ranges.

Load-based splitting (kv.range_split.by_load.enabled, alias kv.range_split.by_load_enabled, on by default) can help by automatically splitting hot ranges when they exceed a QPS threshold (kv.range_split.load_qps_threshold, default 2500). But load-based splitting has structural limits: it cannot split a single-row hotspot, and it cannot keep up with moving hotspots like a write cursor that continuously advances through an index.

flowchart TD
    APP[Application traffic] -->|"Sequential keys concentrate on one range"| LH
    subgraph Node1 ["Node 1: Saturated"]
        LH[Hot range leaseholder]
        LH -->|"CPU 80%+, elevated latency"| SAT[Retry storms, P99 spikes]
    end
    subgraph Node2 ["Node 2: Idle"]
        R2[Cool range replicas]
        R2 -->|"CPU 20%"| IDLE2[Unused capacity]
    end
    subgraph Node3 ["Node 3: Idle"]
        R3[Cool range replicas]
        R3 -->|"CPU 20%"| IDLE3[Unused capacity]
    end
    LH -->|"Raft replication"| R2
    LH -->|"Raft replication"| R3

Common causes

Quick checks

Safe, read-only diagnostics. Run from any node with SQL or HTTP access.

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

# Check leaseholder distribution across nodes (lease metrics: leases, leases_transfers_success)
curl -s http://localhost:8080/_status/vars | grep leases

# Check transaction restart causes (look for writetooold)
curl -s http://localhost:8080/_status/vars | grep txn_restarts

-- Identify large or heavily replicated ranges (admin-only, expensive cluster-wide RPC)
-- Per-range QPS is not exposed in crdb_internal.ranges; the DB Console Hot Ranges page ranks ranges by QPS.
SELECT range_id, start_pretty, end_pretty, lease_holder, range_size
FROM crdb_internal.ranges
ORDER BY range_size DESC
LIMIT 20;

-- Check primary key definition of the affected table
SHOW CREATE TABLE <your_table>;

-- List SQL sequences that may be causing single-row bottlenecks
SHOW SEQUENCES;

The crdb_internal.ranges query is expensive. It performs a cluster-wide RPC fan-out and should be used for diagnosis only, not continuous monitoring. Do not build automated alerting on crdb_internal tables; they are unsupported and may change between versions.

How to diagnose it

1. Confirm the asymmetry. Compare per-node CPU utilization. One node above 2x the others, with similar range counts, strongly suggests a hot range. Check lease count per store to see if leaseholder distribution is skewed.

2. Identify the hot range. Use the DB Console Hot Ranges page to rank ranges by QPS; crdb_internal.ranges does not expose per-range QPS. Any range showing more than 10x the average QPS is hot. Note the start_pretty/end_pretty keys to identify which key range is affected.

3. Check the DB Console. The Hot Ranges page ranks ranges by QPS, CPU, reads, and writes. Compare the top range QPS against the load-based splitting threshold to confirm the range is a split candidate.

4. Examine the primary key. Run SHOW CREATE TABLE on the affected table. Look for monotonic patterns: timestamp-prefixed keys, externally ordered IDs, or any column whose values always increase. SERIAL defaults to unique_rowid() (time-ordered) unless sql.defaults.serial_normalization is changed.

5. Check the transaction restart breakdown. If writetooold is the dominant restart cause, it confirms write contention on hot keys. If readwithinuncertainty appears, that points to clock skew instead, which is a different problem. See CockroachDB clock offset high: catching clock drift before self-termination.

6. Determine if load-based splitting can help. If the hot range covers multiple rows and the hotspot is not a single row, load-based splitting may eventually split it. If the hotspot is a single row or a continuously advancing cursor, splitting will not help. The application or schema must change.

Metrics and signals to monitor

For continuous monitoring, use Prometheus metrics exposed at /_status/vars. Per-node CPU asymmetry and lease count distribution are safe for per-scrape collection. Per-range QPS requires crdb_internal.ranges, which is expensive and should be polled at low frequency (every 5-10 minutes at most).

Fixes

Monotonic primary keys: short-term split, long-term hash

Short-term: ALTER TABLE ... SPLIT AT forces a manual range split at a specified value, distributing the range’s traffic across multiple leaseholders. This provides immediate relief.

-- Manual split at a specific value (short-term mitigation only)
-- This changes range distribution; expect brief latency during rebalancing.
ALTER TABLE <table> SPLIT AT VALUES (<value>);

Automatic range merging may undo the split if the resulting ranges become small and low-traffic. If merging is a problem, consider raising range_min_bytes in the zone configuration for the affected table. Manual splits are a tactical fix, not a permanent solution.

Long-term: hash-sharded indexes (GA since v21.2; v22.1+ uses virtual shard columns that skip backfill) are the recommended structural fix for monotonic primary keys. They distribute writes across multiple buckets by hashing the key.

-- Hash-sharded primary key (available since v21.2)
CREATE TABLE events (
    id BIGINT SERIAL,
    data BYTES,
    PRIMARY KEY (id ASC) USING HASH WITH (bucket_count = 8)
);

Alternatively, UUID-based primary keys distribute writes across the keyspace without sequential concentration. Note that unique_rowid() (the default for SERIAL) is time-ordered, so it can still create a moving hotspot at high write rates.

Single-row hotspots: change the application pattern

If a single row is the bottleneck (for example, a global counter or status field updated by all transactions), the range is indivisible. Neither SPLIT AT nor hash-sharded indexes can help. The application pattern must change:

• Move counters to a side table with multiple rows (sharded counter pattern) and aggregate on read.
• Use application-level caching for frequently updated values.
• Batch updates to reduce per-transaction contention on the hot row.

Sequence-based hotspots: cache or replace

SQL sequences create a single-row bottleneck because nextval() increments one row through its leaseholder. Even with a well-distributed primary key, the sequence itself is a hotspot.

-- Increase sequence caching to reduce leaseholder round-trips
ALTER SEQUENCE <seq_name> CACHE 100000;

For high-throughput insert paths, consider replacing sequences with UUID-based keys that distribute across the keyspace without a centralized counter.

Lease placement tuning for multi-region

For multi-region deployments, zone configurations can influence where leaseholders land:

-- Hint where leaseholders should live (not a hard constraint)
ALTER TABLE <table> CONFIGURE ZONE USING
    lease_preferences = '[[+region=us-east-1]]';

lease_preferences are hints, not hard constraints. The allocator may not satisfy them if the preferred region lacks sufficient replicas or is behind quorum. Do not rely on lease preferences as the sole fix for hot ranges; they address placement, not traffic concentration.

Prevention

• Audit primary key design during schema review. Any monotonically increasing key pattern is a future hot range. This includes timestamp-prefixed keys, externally ordered IDs, and sequence-backed columns; SERIAL defaults to unique_rowid(), which is itself time-ordered. Prefer hash-distributed or UUID-based keys for high-write tables.

• Watch for moving hotspots. Queue-like workloads that write to the tail of an index create a continuously advancing hotspot. Load-based splitting cannot keep up with this pattern. Hash-sharded indexes are the only effective structural fix.

• Monitor per-node CPU asymmetry continuously. The earliest warning of a hot range is one node running significantly hotter than others. Alert on sustained asymmetry (above 2x for more than 10 minutes) even if absolute CPU is not critical.

• Review hot ranges periodically. A weekly look at the DB Console Hot Ranges page, plus a crdb_internal.ranges check ordered by range_size, can reveal emerging hotspots before they cause user-visible degradation.

• Use the Key Visualizer in DB Console. It shows write distribution across the keyspace over time. Bright bands at sequential key boundaries indicate hotspot formation. Regular review catches patterns that point-in-time metrics miss.

How Netdata helps

• Per-node CPU asymmetry detection. Netdata collects sys_cpu_user_ns and sys_cpu_sys_ns per node with per-second granularity. A hot range shows up immediately as one node diverging from the others.

• Lease count distribution. By tracking lease count per store, Netdata makes leaseholder imbalance visible at a glance. A sudden shift in lease distribution often accompanies hot range formation or lease rebalancing churn.

• Transaction restart correlation. Netdata surfaces txn_restarts broken down by cause. Correlating a writetooold spike with per-node CPU asymmetry confirms a hot range diagnosis without querying crdb_internal tables during an incident.

• SQL latency segmentation. Per-node sql_service_latency histograms let you see which nodes are experiencing elevated P99. When one node’s P99 diverges from the cluster, the leaseholder on that node is likely serving a hot range.

• Historical baselining. Netdata retains high-resolution historical data, making it possible to identify when asymmetry began and correlate it with deployment events, schema changes, or traffic pattern shifts.

Netdata’s database monitoring brings these signals together with per-second metrics.

Related guides

• CockroachDB clock offset 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