CockroachDB: distributed SQL database and architecture

History and context

Core architecture elements

Data model and transaction path

The heart of CockroachDB is the seam where a familiar SQL table turns into a distributed key-value store. The interactive section below walks that transition: ranges, replicas, write intents, isolation modes, and locality controls.

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  tenant_id UUID,
  status STRING,
  created_at TIMESTAMPTZ
);

CockroachDB architecture by layer

To see where latency comes from, it helps to split the database into layers: SQL gateway, transaction layer, range distribution, Raft replication, and storage with locality mechanics. Each layer adds its own step to the request path.

Read / Write Path through components

This is where the cost of each query becomes visible: the path from the SQL gateway through range routing, leaseholder handling, Raft consensus, and on to transaction commit. The more steps cross into another region, the costlier the operation.

When to choose CockroachDB

Practice: DDL and DML

From theory to code: practical CockroachDB SQL examples. DDL sets up the schema, indexes, and multi-region settings; DML shows transactions, UPSERT, and concurrent access to the same rows — exactly where retries show up.

CREATE TABLE accounts (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  balance DECIMAL(18,2) NOT NULL,
  status STRING NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_accounts_tenant_status
ON accounts (tenant_id, status)
STORING (balance, created_at);

ALTER DATABASE appdb PRIMARY REGION "us-east1";
ALTER DATABASE appdb ADD REGION "eu-west1";
ALTER DATABASE appdb ADD REGION "ap-southeast1";