cockroachdb-sql-patterns

CockroachDB SQL Best Practices & Anti-Patterns

24 production-verified best practices for CockroachDB. Each section marks anti-patterns with ❌ NEVER and correct patterns with ✅ ALWAYS. Docs: https://www.cockroachlabs.com/docs/stable/

---

1. Primary Keys — Avoid Sequential Hotspots

CockroachDB distributes data across ranges sorted by primary key. Sequential keys concentrate ALL inserts into a single range on a single node, creating a write hotspot.

❌ NEVER: Use SERIAL, BIGSERIAL, or auto-increment as a single-column primary key

-- BAD: All inserts land on ONE node. CPU skew: one node at 80-90%, others at 10-20%.
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,  -- HOTSPOT! unique_rowid() is monotonically increasing
    customer_id UUID,
    amount DECIMAL
);

✅ ALWAYS: Use UUID with gen_random_uuid() for most tables

-- GOOD: Writes distribute evenly across all nodes
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

✅ Use hash-sharded indexes when time ordering is required

-- GOOD: Distributes sequential timestamps across 8 buckets
CREATE TABLE audit_events (
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    event_id UUID NOT NULL DEFAULT gen_random_uuid(),
    event_type STRING NOT NULL,
    payload JSONB,
    PRIMARY KEY (created_at, event_id) USING HASH WITH (bucket_count = 8)
);

✅ Use composite primary keys for multi-tenant or regional data

-- GOOD: Data co-located by tenant for fast reads, distributed across tenants
CREATE TABLE tenant_data (
    tenant_id UUID NOT NULL,
    record_id UUID NOT NULL DEFAULT gen_random_uuid(),
    data JSONB,
    PRIMARY KEY (tenant_id, record_id)
);

Why one DB node has elevated CPU: SERIAL PKs route ALL inserts to the last range, which lives on one leaseholder node. That node shows 80-90% CPU while others idle at 10-20%. You're paying for 5 nodes but getting 1 node's throughput. Diagnosis: check the Hot Ranges page and Hardware dashboard in DB Console.