UUID vs Serial Primary Keys in CockroachDB: Avoid Hotspots (2026)

CockroachDB’s distributed nature means that even seemingly small choices, like your primary key type, can have massive performance implications.

Let’s see how that plays out. Imagine we have a users table.

CREATE TABLE users (
    id UUID PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

And we populate it with some data.

INSERT INTO users (id, username, email) VALUES
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'alice', 'alice@example.com'),
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a2', 'bob', 'bob@example.com'),
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a3', 'charlie', 'charlie@example.com');

Now, let’s consider a common alternative: a serial primary key.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

If we insert data into products, CockroachDB will assign sequential IDs.

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Keyboard', 75.00),
('Mouse', 25.00);

The problem with SERIAL (or BIGSERIAL) primary keys in CockroachDB is that they are sequential. In a distributed system, data is sharded across different nodes. When you insert data with sequential primary keys, all new data gets written to the same range of data on disk, which usually resides on a single node or a small group of nodes. This creates a "hotspot" – one node becomes overwhelmed with write traffic, while others sit idle. This can lead to high latency, transaction errors, and a generally poor user experience.

UUIDs, on the other hand, are designed to be unique across space and time. When generated client-side (or using CockroachDB’s gen_random_uuid() function), they are effectively random. This randomness distributes writes across different ranges and therefore different nodes in the cluster. Instead of all writes hitting node 1, they might hit nodes 2, 5, and 8, balancing the load.

Here’s how you’d typically generate a UUID primary key in CockroachDB:

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_name VARCHAR(100),
    order_date TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

INSERT INTO orders (customer_name) VALUES ('Alice');

The DEFAULT gen_random_uuid() ensures that a new, unique UUID is generated for each new row, and because these UUIDs are random, the inserts will be distributed across your cluster.

The core of the problem with sequential keys is that CockroachDB, like many distributed databases, uses a range-based partitioning strategy. By default, new data with sequential keys will all fall into the same initial range. As this range grows, it might eventually split, but the initial insertion point remains a bottleneck. When writes are concentrated on a single range (and thus a single node), that node’s resources (CPU, network, disk I/O) become saturated. This leads to increased transaction latency as requests queue up. You might see errors like max retries exceeded or transaction deadline exceeded.

If you must use sequential IDs for some reason, CockroachDB offers a solution: experimental_auto_random_sortable_uuid(). This function generates UUIDs that are mostly random but have a sortable component, allowing for better distribution while still maintaining some degree of sortability.

CREATE TABLE events (
    event_id experimental_auto_random_sortable_uuid() PRIMARY KEY,
    event_type VARCHAR(50),
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

INSERT INTO events (event_type) VALUES ('login');

This function is designed to produce UUIDs that distribute writes more evenly than simple sequential integers. The "sortable" aspect means that even with random generation, records inserted close in time will likely have IDs that are also close in their sortable component, which can be beneficial for range scans.

The common mistake is to assume that a primary key’s job is solely about uniqueness and indexing, and that its generation strategy doesn’t impact distributed performance. In a distributed system, how data is initially placed and how subsequent writes are distributed are critical performance factors. Sequential IDs create a predictable, centralized insertion point, which is anathema to a distributed architecture.

When you have a hotspot, you’ll observe one or more nodes in your cluster consistently showing significantly higher CPU utilization than others. You can check this using SHOW CLUSTER METRICS and looking at sql.exec_ பாதிக்கிறது.max or sql.distsql.query_total.max per node, or by monitoring your cluster via the Admin UI. The key indicator is an imbalance of load.

The fix is to migrate to a universally unique and random identifier for your primary keys. For existing tables with sequential primary keys that are already experiencing hotspots, this is a more involved process. You would typically:

1. Add a new column of type UUID with a default gen_random_uuid().
2. Backfill this new column for all existing rows.
3. Create new indexes or constraints on this new UUID column.
4. Gradually migrate application reads and writes to use the new UUID column.
5. Once traffic is fully migrated, drop the old sequential primary key column.

This migration needs careful planning to avoid downtime or performance degradation during the transition.

The next error you’ll encounter after fixing primary key hotspots is likely related to secondary index hotspots, which follow a similar root cause but manifest differently.