Prevent Range Hotspots in CockroachDB Before They Cause Latency (2026)

Range hotspots are a common cause of performance degradation in CockroachDB, manifesting as uneven load distribution across your data. This leads to certain ranges (the underlying storage units for data) becoming overloaded, while others remain idle.

Let’s see this in action. Imagine a simple users table where user_id is the primary key and is a sequential integer.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255)
);

-- Inserting data sequentially
INSERT INTO users (user_id, username, email)
VALUES (1, 'alice', 'alice@example.com'),
       (2, 'bob', 'bob@example.com'),
       (3, 'charlie', 'charlie@example.com');
-- ... and so on for millions of users

When you insert data with sequentially increasing user_ids, all new writes will go to the last range in the users table. This range will quickly become a hotspot. Reads hitting this range will also be slow.

The Core Problem: Uneven Load

CockroachDB distributes data into contiguous key-value pairs called ranges. Each range is managed by a Raft group, ensuring replication and fault tolerance. When operations (reads or writes) are concentrated on a few ranges, those ranges become performance bottlenecks. This is a "hotspot."

The primary driver of hotspots is often the choice of primary key or the pattern of data access. Sequential primary keys, like auto-incrementing integers, are the classic culprit. All new writes are directed to the range containing the highest keys, overloading it.

Preventing Hotspots: Strategies and Tactics

The key is to ensure that writes and reads are distributed as evenly as possible across your data.

1. Use Non-Sequential Primary Keys:

   • Diagnosis: Examine your CREATE TABLE statements. If your primary keys are sequential integers (e.g., SERIAL, BIGSERIAL, AUTO_INCREMENT), this is a prime suspect.
   • Fix: Switch to a universally unique identifier (UUID) as your primary key. CockroachDB has a gen_random_uuid() function.

     CREATE TABLE users (
         user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
         username VARCHAR(255),
         email VARCHAR(255)
     );
     

   • Why it works: UUIDs are randomly distributed, so new inserts will be spread across many different ranges, preventing any single range from accumulating all the writes.

2. Employ Hashed Indexes for Hot Data:

   • Diagnosis: If you have specific tables that must have sequential keys (e.g., for ordering reasons) or if you’re dealing with high-volume inserts that still cause issues, consider a hashed index.
   • Fix: Create a HASH index on the column that is causing the hotspot. This distributes the data based on a hash of the column’s value.

     CREATE TABLE events (
         event_id BIGINT PRIMARY KEY, -- Sequential, potential hotspot
         event_time TIMESTAMPTZ,
         payload JSONB
     );
     
     -- Add a hashed index on the primary key
     CREATE INVERTED INDEX events_pk_hash_idx ON events (event_id);
     

     Note: In CockroachDB v23.1+, CREATE INVERTED INDEX is the way to achieve this. For older versions, it was CREATE HASH INDEX.
   • Why it works: The INVERTED index effectively creates multiple backing ranges for the data, distributing writes and reads across these new ranges based on the hash of the indexed column.

3. Distribute Writes with a "Shard" Column:

   • Diagnosis: For very large tables where even UUIDs might not be enough or for specific use cases where you need more control, you can manually shard.
   • Fix: Add a "shard" column to your table. This column should have a limited, well-distributed set of values (e.g., integers from 0 to 15). You then need to ensure your application logic distributes writes across these shard values.

     CREATE TABLE messages (
         message_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
         shard INT NOT NULL, -- Manual sharding column
         created_at TIMESTAMPTZ DEFAULT now(),
         content TEXT
     );
     
     -- Application logic must ensure inserts go to different shards:
     -- INSERT INTO messages (shard, content) VALUES (0, 'Hello');
     -- INSERT INTO messages (shard, content) VALUES (1, 'World');
     -- ... etc.
     

   • Why it works: By distributing writes across different shard values, you force the data into different underlying ranges, spreading the load.

4. Monitor Range Splits and Merges:

   • Diagnosis: Use SHOW RANGES and SHOW RANGES FROM TABLE <table_name> to observe range sizes. If ranges are consistently growing very large or if you see many ranges being created or merged rapidly, it indicates load imbalance.
   • Fix: This is more of a diagnostic tool than a direct fix. Understanding range splits helps you identify where hotspots are forming, guiding your primary key or indexing strategy. CockroachDB automatically splits ranges when they reach a certain size (e.g., 512MB by default) to distribute data. If splits are happening too frequently on a single range, it’s a hotspot.
   • Why it works: Monitoring range behavior helps confirm if your strategies are working or if further intervention is needed.

5. Consider Table Partitioning (for specific workloads):

   • Diagnosis: For tables with a natural, time-based or categorical distribution (e.g., logs, historical data), partitioning can help.
   • Fix: Partition your table based on a column like created_at or a region code.

     CREATE TABLE logs (
         log_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
         created_at TIMESTAMPTZ,
         message TEXT
     ) PARTITION BY RANGE (created_at);
     
     CREATE TABLE logs_y2023m01 PARTITION OF logs
         FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
     CREATE TABLE logs_y2023m02 PARTITION OF logs
         FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
     -- ... and so on
     

   • Why it works: Partitioning can help isolate workloads. For instance, if most writes are to the latest partition, it still might create a hotspot within that partition, but it can simplify management and query performance for historical data. It’s not a direct hotspot prevention for writes, but a way to manage data distribution and access patterns.

6. Use crdb_internal.ranges for granular inspection:

   • Diagnosis: This is an advanced tool to see the exact load per range.
   • Fix: Query crdb_internal.ranges and look at lease_liveness_count, write_bytes, read_bytes, and key_count for individual ranges. If a few ranges have disproportionately high lease_liveness_count (meaning they are actively serving requests) or write_bytes, you’ve found your hotspot.

     SELECT
         range_id,
         start_key,
         end_key,
         lease_liveness_count,
         write_bytes,
         read_bytes,
         key_count
     FROM crdb_internal.ranges
     WHERE table_id = (SELECT id FROM pg_catalog.pg_class WHERE relname = 'users');
     

   • Why it works: This provides concrete evidence and the precise key ranges experiencing the load, allowing you to pinpoint the problematic data and apply the appropriate fix (like changing the primary key or adding a hash index).

The next problem you’ll likely encounter is understanding how CockroachDB handles transactions and distributed locks when these hotspots start to resolve and load rebalances.