UUIDs in Microservices & CockroachDB: Between Scalability and Performance

In the microservices world, UUIDs are often chosen as primary keys almost without a second thought. The reasons sound reasonable — UUIDs are globally unique, can be generated without central coordination, and are safe for distributed systems. But when the system starts scaling and data grows, many teams realize one bitter truth: join queries that used to feel fast can suddenly be hundreds of times slower than systems using integer IDs. The problem isn’t the UUID itself, but the UUID variant chosen and how it interacts with the database index structure — especially in distributed SQL like CockroachDB.

What Is a UUID?

A UUID (Universally Unique Identifier) is a 128-bit (16-byte) identifier designed to be unique without needing central coordination. No single server needs to be contacted to ensure IDs don’t collide — every service can generate its own UUID.

UUID example: 550e8400-e29b-41d4-a716-446655440000

In databases, a UUID is usually stored as 16 binary bytes even though it’s often seen as a string. There are several UUID variants, and the choice of variant is what most determines your system’s performance fate.

Relevant UUID Variants

UUID v4 is the most commonly used by default — completely random, with no time ordering at all. Easy to use and doesn’t leak temporal information, but its random nature is precisely what becomes the root of performance problems.

UUID v1 is based on a timestamp and node identifier, making it time-ordered. Better than v4 for database performance, but potentially leaks time and node identity information. Rarely recommended in modern systems anymore.

UUID v7 is the newest standard — combining a timestamp at the start with random bits at the back. Inserts are almost sequential, indexes are far more stable, and it stays globally unique. This is what should be used as a primary key in modern databases.

Why Are UUIDs Popular as Primary Keys?

UUIDs answer several real problems in distributed architectures that are hard to solve with auto-increment integers.

No central coordination needed. With BIGSERIAL or auto-increment, every insert must contact one point to get the next ID. In microservices, this creates a bottleneck and coupling. UUIDs can be generated on the application side, even before touching the database.

Safe for public APIs. Sequential integer IDs are easy to guess — an attacker can enumerate resources just by incrementing numbers. UUIDs have no predictable pattern, so they’re safe to expose externally.

No collisions when merging data. During migration, import, or data synchronization between clusters, UUIDs are almost impossible to collide accidentally. This is very valuable in multi-region systems.

All these reasons are valid. UUIDs aren’t the wrong choice — they’re just often used without understanding their trade-offs.

What Actually Happens When Performance Drops?

This scenario often occurs in teams that just scaled:

-- A query that looks normal
SELECT o.*, p.*
FROM orders o
JOIN payments p ON o.id = p.order_id;

-- With integer IDs: finishes in milliseconds
-- With UUID v4: can take seconds, even tens of seconds on large data

There are three mechanisms working together making UUID v4 so expensive for joins.

1. Larger Comparison Size

BIGINT is only 8 bytes. A UUID is 16 bytes — twice as much. In every join, the database must compare a key from one table with a key in another table. In large joins with millions of rows, the 8-byte difference per comparison accumulates into a significant difference in CPU and cache.

2. UUID v4 Destroys Index Locality

This is the most fundamental problem. Modern databases use B-trees for indexes. B-trees work optimally when inserts arrive ordered — new nodes are always added at the end, not in the middle.

UUID v4 is completely random. Every new insert lands in a random position in the B-tree, forcing the database to constantly do page splits and index structure reorganizations.

flowchart TD
    subgraph Integer["Insert with Integer ID (Sequential)"]
        A1[1] --> A2[2] --> A3[3] --> A4[4] --> A5[5]
        style Integer fill:#d4edda
    end

    subgraph UUID4["Insert with UUID v4 (Random)"]
        B1[550e...] 
        B2[a3f1...]
        B3[12bc...]
        B4[ff09...]
        B1 -.->|"forced split"| B3
        B3 -.->|"forced split"| B2
        B2 -.->|"forced split"| B4
        style UUID4 fill:#f8d7da
    end

The result is a fragmented index — when the database needs to scan the index for a join, it must jump to pages scattered across the entire disk, not read linearly.

3. Multiplied Impact in CockroachDB

CockroachDB is a distributed SQL database — data is divided across many nodes in the form of ranges (collections of nearby keys). A join in CockroachDB isn’t just an operation on one machine, but can involve RPCs between nodes.

Random UUID v4 causes range churn — data spreads evenly across all nodes without pattern, so every join can require coordination to many nodes at once.

sequenceDiagram
    participant Client
    participant Node1
    participant Node2
    participant Node3

    Client->>Node1: JOIN orders + payments
    Note over Node1: UUID v4: data scattered randomly
    Node1->>Node2: Fetch key a3f1... (on another node)
    Node1->>Node3: Fetch key ff09... (on another node)
    Node2-->>Node1: Result
    Node3-->>Node1: Result
    Node1-->>Client: Response (slow because of many RPCs)

On top of the join cost, there’s also higher replication cost because CockroachDB uses Raft — larger keys mean larger Raft logs, and slower recovery.

Identifier Comparison for Primary Keys

Not all UUIDs are created equal. Here’s a complete comparison of the relevant options.

UUID v7 and ULID are the ideal middle ground for microservices: still globally unique without coordination, but time-ordered so inserts are sequential and indexes stay stable.

flowchart TD
    A{Need global uniqueness<br/>without coordination?} -- Yes --> B{Need to be exposed<br/>to a public API?}
    A -- No --> C[BIGINT / BIGSERIAL]
    B -- Yes --> D{Modern database<br/>with UUID v7 support?}
    B -- No --> E[UUID v7 as PK<br/>BIGINT as internal ID]
    D -- Yes --> F[UUID v7]
    D -- No --> G[ULID]

Anti-Patterns to Avoid

-- ✗ Anti-pattern 1: UUID v4 as PK in frequently joined tables
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- v4, fully random
  user_id UUID,
  ...
);
-- ✓ Solution: use UUID v7 or ULID so inserts are sequential
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuid_v7(),
  user_id UUID,
  ...
);

-- ✗ Anti-pattern 2: one UUID for all purposes at once
-- (internal PK, join key, and public API identifier all become one)
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()
  -- this id is used for joins, for URLs, for everything
);
-- ✓ Solution: separate the internal ID and public ID
CREATE TABLE users (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  -- for internal joins
  public_id UUID UNIQUE DEFAULT uuid_v7()              -- for external APIs
);

-- ✗ Anti-pattern 3: plain BIGSERIAL in CockroachDB
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY  -- sequential insert → hotspot on one node
);
-- ✓ Solution: use UUID v7 or a hash-sharded index
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT uuid_v7()  -- more even distribution
);

-- ✗ Anti-pattern 4: cross-service joins at runtime
SELECT o.*, u.name
FROM orders_service.orders o
JOIN users_service.users u ON o.user_id = u.id;  -- cross-domain database join
-- ✓ Solution: use a denormalized read model or event-driven projection
-- Store the needed data in your own domain, don't join into another domain

Best Practices in Production Systems

Use UUID v7 or ULID as the Primary Key

Both are time-ordered so inserts are almost sequential — indexes don’t fragment, and joins are far more efficient than with UUID v4.

-- CockroachDB with UUID v7
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuid_v7(),
  created_at TIMESTAMPTZ DEFAULT now(),
  customer_id UUID NOT NULL,
  total_amount DECIMAL(12,2)
);

-- Or with ULID in the application (generate app-side, store as a string)
CREATE TABLE orders (
  id TEXT PRIMARY KEY,  -- ULID from the application
  created_at TIMESTAMPTZ DEFAULT now()
);

Separate Internal and Public IDs

This pattern is used in large-scale systems to get the best of both worlds — join performance from integers, and UUID security for public APIs.

CREATE TABLE products (
  id       BIGINT  PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  -- ^ used for all internal joins, very efficient

  public_id UUID   UNIQUE NOT NULL DEFAULT uuid_v7(),
  -- ^ used in URLs, API responses, and inter-service communication

  name     TEXT    NOT NULL,
  price    DECIMAL(10,2)
);

With this pattern, internal join queries use id (BIGINT), while external APIs and inter-service communication use public_id (UUID). Join performance isn’t disturbed, security stays maintained.

Avoid Cross-Domain Joins at Runtime

This is a microservices principle often ignored for short-term convenience. Cross-service-domain database joins are a time bomb — they scale badly, have high coupling, and are very hard to fix later.

flowchart LR
    subgraph Bad["❌ Runtime Cross-Domain Join"]
        A[Order Service] -->|JOIN at query time| B[User Service DB]
        A -->|JOIN at query time| C[Product Service DB]
    end

    subgraph Good["✓ Read Model / Projection"]
        D[Order Service] --> E[Order Read Model]
        F[User Service] -->|event: user.updated| E
        G[Product Service] -->|event: product.updated| E
        H[Query] --> E
    end

Use CQRS, denormalized read models, or event-driven projections for data needing cross-domain combination. Heavy analytics should be moved to a data warehouse, not runtime queries against operational databases.

Primary Key Checklist in Distributed Systems

IDENTIFIER SELECTION:
  □ UUID v7 or ULID chosen if global uniqueness without coordination is needed
  □ UUID v4 NOT used as PK in frequently joined tables
  □ BIGINT considered for internal tables not needing external exposure
  □ BIGSERIAL NOT used in CockroachDB (write hotspot risk)

TABLE DESIGN:
  □ Internal ID (BIGINT) and public ID (UUID) separated if both are needed
  □ Foreign key indexes use the same column as the referenced PK
  □ No cross-domain joins in runtime queries

SPECIFICALLY FOR COCKROACHDB:
  □ PK chosen to distribute writes evenly across nodes
  □ UUID v7 or hash-sharded indexes used for large tables
  □ Range splits monitored to detect hotspots

MONITORING:
  □ Query plans checked to ensure indexes are used efficiently
  □ Slow query log enabled and monitored
  □ Index fragmentation metrics monitored periodically

Summary

• UUIDs aren’t the problem, UUID v4 as a join-heavy PK is — its random nature destroys index locality and causes significant B-tree fragmentation at scale.
• UUID v7 and ULID are the best current choices for primary keys in modern databases — both are time-ordered so inserts are sequential, indexes stay stable, and joins are efficient.
• In CockroachDB, the problem multiplies — UUID v4 causes range churn and many inter-node RPCs, because join data scatters across the entire cluster without pattern.
• Separate internal and public IDs if you need both: BIGINT for fast internal joins, a UUID as public_id for APIs and inter-service communication.
• Plain BIGSERIAL is dangerous in CockroachDB — sequential inserts make all writes pile up on one node (hotspot), defeating the cluster distribution purpose.
• Avoid cross-domain joins at runtime — use read models, CQRS, or event-driven projections. Cross-service joins are both a microservices anti-pattern and a performance time bomb.
• UUIDs remain relevant and valid — the key is choosing the right variant and using it for the right purpose, not avoiding them entirely.