CockroachDB: Distributed SQL, Multi-Region & Retries

CockroachDB

CockroachDB is a distributed SQL database: it looks like PostgreSQL to applications — the same wire protocol, much of the same SQL — but stores data across many nodes, replicates it with the Raft consensus protocol, and keeps running through node, zone, and even region failures. It adds nodes to scale horizontally, rebalances data automatically, and provides serializable ACID transactions across the whole cluster.

That combination solves real problems — global applications, strict uptime requirements, outgrowing a single PostgreSQL primary — and brings real costs: higher per-query latency than a single-node database, transaction retries under contention, and data modeling that has to think about distribution. It shares design ideas with Google Cloud Spanner without requiring special clock hardware.

TL;DR

Speaks the PostgreSQL wire protocol; most PostgreSQL drivers and ORMs work, but not every PostgreSQL feature or extension.
Data is split into ranges, each replicated (three replicas by default) with Raft; a leaseholder serves reads and coordinates writes.
Transactions are serializable by default; clients must retry on serialization errors (SQLSTATE 40001).
Avoid sequential primary keys that create hotspots; use UUIDs or hash-sharded indexes.
Multi-region features place rows near users (REGIONAL BY ROW, GLOBAL tables) and set survival goals.
Licensing changed in 2024: one enterprise license, free for smaller companies; managed CockroachDB Cloud is available.

Quick Example

A three-region database where each user's row lives near that user and the cluster survives the loss of a whole region:

Application code connects with an ordinary PostgreSQL driver, but wraps transactions in a retry loop:

Core Concepts

Ranges, Raft, and Leaseholders

CockroachDB stores all data in one sorted key-value space, split into ranges of up to 512 MiB by default. Each range is replicated to (by default) three nodes, which agree on writes with Raft, so a range stays available as long as a majority of its replicas is up. One replica holds the lease and serves consistent reads and coordinates writes. Ranges split, merge, and move automatically as data and load change.

Transactions and Isolation

Transactions are serializable by default — the strongest standard isolation level — using multi-version concurrency control with hybrid logical clocks. When concurrent transactions conflict, one may be aborted with a retryable error, which the client must retry. Read Committed isolation is also available for workloads ported from PostgreSQL that expect it, trading some anomaly protection for fewer retries. See Database Transactions.

Primary Keys and Hotspots

Because data is sorted by primary key, sequential keys — auto-incrementing integers, timestamps — send every insert to the same range and the same leaseholder, creating a hotspot. Use gen_random_uuid(), or keep a sequential column but index it with a hash-sharded index (USING HASH) to spread writes.

Multi-Region Abstractions

Follower Reads and Changefeeds

Queries using AS OF SYSTEM TIME follower_read_timestamp() can be served by the nearest replica with slightly stale data — ideal for dashboards and reports. Changefeeds stream row changes to Kafka, cloud storage, or webhooks for change data capture.

PostgreSQL Compatibility

Most applications can connect with standard PostgreSQL drivers and ORMs, but plan for differences:

Not every feature or extension: PostGIS-style spatial types have native support, but many PostgreSQL extensions are unavailable.
Serial columns behave differently by default (unique, not strictly sequential) to avoid hotspots.
Retries must be handled in the application or with driver helpers.
Schema changes run online as background jobs, which changes how migrations behave and how long they take.
Latency: each write requires consensus among replicas, so single-row writes are slower than on a single PostgreSQL node, especially across regions.

When It Fits

Best Practices

Design Primary Keys for Distribution

Use UUIDs or composite keys led by a well-distributed column, and hash-sharded indexes for sequential access patterns.

Make Every Transaction Retryable

Keep transactions short, avoid side effects inside them (send emails after commit), and use a retry loop or a driver's transaction helper.

Measure Latency per Region

Place leaseholders near the users who write the data, use GLOBAL tables for read-mostly reference data, and use follower reads where staleness is acceptable.

Watch Contention

Use the built-in contention and statement insights to find hot rows and conflicting transactions, and redesign counters and queues that funnel writes into one row.

Common Mistakes

Auto-Increment Keys

Ignoring Retry Errors

Treating It Exactly Like PostgreSQL

FAQ

Is CockroachDB compatible with PostgreSQL?

It uses the PostgreSQL wire protocol and supports much of PostgreSQL's SQL, so most drivers and ORMs work. Some features, functions, and extensions are missing or behave differently, so applications should be tested rather than assumed compatible.

Why do I get transaction retry errors?

Because CockroachDB runs transactions at serializable isolation and aborts one of two conflicting transactions to preserve correctness. Clients are expected to retry; reducing contention on hot rows reduces how often it happens.

How does CockroachDB survive failures?

Every range is replicated with Raft across nodes, zones, or regions. As long as a majority of a range's replicas is reachable, it keeps serving reads and writes, and lost replicas are re-created automatically elsewhere.

Is CockroachDB free?

Since late 2024, self-hosted CockroachDB uses a single enterprise license that is free for businesses below a revenue threshold, with paid licenses above it. The managed CockroachDB Cloud has its own pricing tiers.

How is CockroachDB different from Spanner?

Both are distributed SQL databases with serializable transactions. Spanner relies on Google's TrueTime clock infrastructure and runs only on Google Cloud; CockroachDB runs anywhere, uses hybrid logical clocks, and speaks the PostgreSQL protocol.