Articles

Design CockroachDB Indexes to Avoid Hotspots and Full Scans (2026)

April 16, 2026 6 min read

CockroachDB indexes are not like traditional relational database indexes; they are actually full table copies, and the default primary key design is a time-series one that guarantees hotspots.

Let’s see how this plays out. Imagine a simple users table:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username STRING UNIQUE,
    email STRING UNIQUE,
    created_at TIMESTAMP DEFAULT now(),
    last_login TIMESTAMP
);

When you insert a new user, gen_random_uuid() generates a UUID. In CockroachDB, UUIDs are lexicographically sortable. This means new UUIDs are always generated with a higher value than existing ones. When you insert rows, CockroachDB tries to keep them sorted by their primary key. So, all new rows are written to the end of the users table’s primary index.

This is a problem because CockroachDB, like many distributed databases, shards its data based on ranges of primary keys. If all your writes are going to the single, ever-growing, highest-key range, that range (and the nodes hosting it) will become a hotspot. All your inserts, updates, and deletes targeting new records will hit the same set of servers.

The Hotspot Problem in Action

Here’s what a hotspot looks like in practice. You’d typically see this in your CockroachDB Admin UI’s "Overview" page, under "Hotspots." You’ll see one or more ranges with significantly higher CPU, QPS, or latency metrics than others. If you drill down into a hot range, you might see it contains a disproportionately large number of your table’s rows.

The command to identify hot ranges is SHOW RANGES FROM TABLE <table_name>;. Look for ranges with a high lease_count or replicas that are consistently busy.

Avoiding Hotspots: The range_split_at Strategy

The fundamental solution is to ensure that primary key values are distributed more evenly across the available key space. CockroachDB offers a mechanism for this: range_split_at.

Instead of relying on gen_random_uuid() (which is sequential), you can use a hashing function or a strategy that distributes values. A common and effective approach for time-series data or anything with sequential inserts is to use range_split_at during table creation.

Let’s redefine our users table, but this time, we’ll use a strategy to distribute id values. We’ll aim to split the UUID space into smaller, more manageable ranges from the start.

-- Example with range_split_at for UUIDs
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_v4(), -- uuid_v4 is generally better distributed than gen_random_uuid
    username STRING UNIQUE,
    email STRING UNIQUE,
    created_at TIMESTAMP DEFAULT now(),
    last_login TIMESTAMP
);

-- After table creation, manually split the primary key range.
-- This command splits the key space into 16 ranges.
ALTER TABLE users SPLIT AT VALUES
    (uuid_from_bytes('00000000000000000000000000000000')),
    (uuid_from_bytes('80000000000000000000000000000000'));

-- If you need more splits, you can add more UUIDs.
-- For example, to split into 256 ranges:
-- ALTER TABLE users SPLIT AT VALUES
--     (uuid_from_bytes('00000000000000000000000000000000')),
--     (uuid_from_bytes('01000000000000000000000000000000')),
--     ... up to 255 splits ...
--     (uuid_from_bytes('ff000000000000000000000000000000'));

Why this works: The uuid_v4() function generates UUIDs that are generally more randomly distributed than gen_random_uuid(). By manually splitting the primary key range using SPLIT AT VALUES, you’re telling CockroachDB to create initial ranges that cover different parts of the UUID space. For example, splitting at 0000... and 8000... creates ranges for 0000... to 7fff... and 8000... to ffff.... When new UUIDs are generated, they are more likely to fall into different ranges, distributing the write load.

The exact UUIDs used for splitting are important. They should be strategically chosen to divide the entire UUID space. For a UUID v4, the first 128 bits are random. The uuid_from_bytes function allows you to specify a 16-byte value to split at. Using 0000... and 8000... effectively splits the space in half. For more granular control, you’d pick UUIDs that divide the space into your desired number of initial ranges.

Secondary Indexes and Full Scans

Now, let’s talk about secondary indexes. CockroachDB implements secondary indexes as inverted indexes. This means the secondary index stores the indexed column values and the primary key of the rows that contain those values.

Consider an index on username:

CREATE INDEX username_idx ON users (username);

When you query SELECT * FROM users WHERE username = 'alice';, CockroachDB first looks up 'alice' in username_idx. It finds the primary key (e.g., a UUID) associated with 'alice'. Then, it uses that primary key to fetch the full row from the primary index.

The problem arises when you perform a query that cannot use an index effectively, leading to a full table scan. This happens when:

  1. No relevant index exists: You query on a column that isn’t indexed.
  2. The index is not selective enough: The query condition matches a very large percentage of rows (e.g., WHERE created_at >= '2023-01-01').
  3. The query requires columns not in the index: If you select columns that are not part of a covering index, CockroachDB has to go back to the primary index to fetch them.

A full table scan means CockroachDB has to read every single row in the table’s primary index to find the ones that match your query. This is incredibly inefficient, especially for large tables.

Avoiding Full Scans: Indexing Strategies

  • Index the right columns: If you frequently filter or sort by username or email, ensure those columns have indexes.

  • Use covering indexes: If you often query for specific columns alongside an indexed column, include those columns in the index.

    -- Index for finding users by username and returning their email and last_login
CREATE INDEX username_email_login_idx ON users (username) STORING (email, last_login);

    Why this works: When you query SELECT email, last_login FROM users WHERE username = 'alice';, CockroachDB can satisfy the entire query from username_email_login_idx alone. It doesn’t need to perform a secondary lookup on the primary index, saving significant I/O.

  • Composite indexes: For queries involving multiple conditions, create composite indexes. The order of columns matters.

    -- Index for finding users created after a certain time
CREATE INDEX created_at_idx ON users (created_at);

    If you query SELECT * FROM users WHERE created_at > '2023-01-01';, this index is useful. However, if you query SELECT * FROM users WHERE username = 'alice' AND created_at > '2023-01-01';, the username_idx would be used first, and then a filter would be applied on the results. A composite index like CREATE INDEX username_created_at_idx ON users (username, created_at); would be more efficient for that specific query pattern.

The Counterintuitive Indexing Detail

Many people think of secondary indexes as just lookup tables. In CockroachDB, a secondary index is also a full copy of the data it indexes, plus references to the primary key. This means that if you create many secondary indexes, especially on large tables, you significantly increase storage overhead and write amplification. Every write to the base table must be applied to all its secondary indexes. The SHOW SYNTAX command for an index will reveal its underlying structure, which is essentially another table.

The next logical step after optimizing indexes is understanding how CockroachDB handles transactions and what happens when they fail due to contention.

Want structured learning?

Take the full Cockroachdb course →