Skip to content
Toolforge

16. Cheat sheet

Everything worth having to hand, in one place.

The six facts

Section titled “The six facts”
1. The cluster is ONE SORTED KEY SPACE cut into RANGES
   (~512 MiB). Each range is its own RAFT GROUP. That
   is how writes scale where etcd cannot.


2. One replica per range holds the LEASE: it serves
   reads locally and coordinates writes. Spreading
   leases is what spreads load.


3. A write commits when a MAJORITY has fsynced it.
   The SECOND-FASTEST replica sets your latency; the
   slowest never does.


4. Isolation is SERIALIZABLE by default. Your app MUST
   retry SQLSTATE 40001. READ COMMITTED is opt-in.


5. Time is a HYBRID LOGICAL CLOCK plus an UNCERTAINTY
   INTERVAL (--max-offset). Bad NTP kills nodes.


6. A MONOTONIC leading key = ONE hot range = ONE node.
   UUID, unique_rowid(), or hash-shard it.

Starting and connecting

Section titled “Starting and connecting”
Terminal window
cockroach start --certs-dir=certs --advertise-addr=n3:26257 \
  --join=n1,n2,n3 --locality=region=eu-west,zone=eu-west-1c \
  --store=/mnt/data --cache=.25 --max-sql-memory=.25


cockroach init --certs-dir=certs --host=n1      # once, ever
cockroach demo --nodes=3                        # throwaway cluster
cockroach sql --certs-dir=certs --host=n1
postgresql://app@n1,n2,n3:26257/store?sslmode=verify-full
                                 ▲ port 26257, not 5432

Cluster health

Section titled “Cluster health”
Terminal window
cockroach node status --certs-dir=certs            # THE command
cockroach node status --ranges --certs-dir=certs
cockroach node status --decommission --certs-dir=certs
SELECT node_id, address, locality, is_live FROM crdb_internal.gossip_nodes;
SELECT node_id, ranges, leases FROM crdb_internal.kv_node_status;
SELECT * FROM crdb_internal.replication_constraint_stats
WHERE violating_ranges > 0;
   ALERT ON
     unavailable ranges        must ALWAYS be 0  ◄ page someone
     under-replicated ranges   0 at rest
     liveness heartbeat latency
     SQL service latency p99
     LSM read amplification    > ~10 = compaction losing
     txn restarts / contention events
     disk capacity             < 60% is comfortable

Ranges and hotspots

Section titled “Ranges and hotspots”
SHOW RANGES FROM TABLE users WITH DETAILS;
SHOW RANGE FROM TABLE users FOR ROW (4718);
SELECT * FROM crdb_internal.cluster_replicas LIMIT 20;


ALTER TABLE users SPLIT AT VALUES (1000000);
ALTER TABLE users UNSPLIT ALL;

Schema patterns

Section titled “Schema patterns”
-- spread writes
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
id INT  PRIMARY KEY DEFAULT unique_rowid()


-- ordered key that must not be hot
CREATE INDEX ON events (ts) USING HASH WITH (bucket_count = 8);


-- colocate by access dimension
PRIMARY KEY (tenant_id, id)


-- avoid the index join
CREATE INDEX ON orders (customer_id) STORING (status, total);


-- index only what you query
CREATE INDEX ON orders (created_at) WHERE status = 'pending';


-- let the database do the deleting
ALTER TABLE events SET (ttl_expiration_expression = "ts + INTERVAL '90 days'");

Transactions

Section titled “Transactions”
BEGIN; … COMMIT;
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- opt-in
SELECT … FROM accounts WHERE id = 42 FOR UPDATE;   -- wait, don't retry
BEGIN AS OF SYSTEM TIME '-30s';                    -- read-only snapshot
   ERROR … (SQLSTATE 40001)   → retry the whole txn,
                                exponential backoff + jitter
   single-batch txns          → retried transparently
   interactive txns           → YOUR responsibility

Reads

Section titled “Reads”
SELECT … ;                                          -- leaseholder, current
SELECT … AS OF SYSTEM TIME follower_read_timestamp();  -- local, ~3 s stale
SELECT … AS OF SYSTEM TIME with_max_staleness('10s');  -- 1 stmt, 1 range
SELECT … AS OF SYSTEM TIME '-1m';                      -- reports, no contention
SELECT … AS OF SYSTEM TIME '2026-09-21 09:15:00';      -- time travel
   NEVER use stale reads for: read-modify-write,
   uniqueness checks, or read-your-own-writes.

Plans

Section titled “Plans”
EXPLAIN SELECT …;
EXPLAIN ANALYZE (DIST, VERBOSE) SELECT …;
SET disallow_full_table_scans = on;      -- app session default
ANALYZE users;                           -- after ANY bulk load
SHOW STATISTICS FOR TABLE users;
   READ A PLAN IN THIS ORDER
     1. FULL SCAN on a big table      → missing index
     2. estimated vs actual rows      → stale statistics
     3. join algorithm                → hash/merge/lookup
     4. distribution + network time   → geography
     5. contention time               → chapter 5, not 9

Multi-region

Section titled “Multi-region”
ALTER DATABASE store SET PRIMARY REGION "eu-west";
ALTER DATABASE store ADD REGION "us-east";
ALTER DATABASE store SURVIVE REGION FAILURE;    -- or ZONE FAILURE


ALTER TABLE users    SET LOCALITY REGIONAL BY ROW;
ALTER TABLE orders   SET LOCALITY REGIONAL BY TABLE IN "eu-west";
ALTER TABLE products SET LOCALITY GLOBAL;


ALTER TABLE orders CONFIGURE ZONE USING
  lease_preferences = '[[+region=eu-west]]';
   SURVIVE ZONE FAILURE     fast local writes, region
                            loss = unavailable
   SURVIVE REGION FAILURE   survives a region, every
                            write pays a cross-region
                            round trip
   GLOBAL                   fast reads everywhere,
                            slower writes

Zones and replication

Section titled “Zones and replication”
ALTER TABLE users CONFIGURE ZONE USING
  num_replicas = 5,
  constraints  = '{"+region=eu-west": 2, "+region=us-east": 2}',
  gc.ttlseconds = 14400;


SHOW ZONE CONFIGURATION FROM TABLE users;
   3 replicas tolerate 1 loss · 5 tolerate 2
   4 buys NOTHING over 3
   replication factor is INDEPENDENT of cluster size

Backups and changefeeds

Section titled “Backups and changefeeds”
BACKUP INTO 's3://bucket/crdb?…' AS OF SYSTEM TIME '-10s';
BACKUP INTO LATEST IN 's3://bucket/crdb?…' AS OF SYSTEM TIME '-10s';
SHOW BACKUPS IN 's3://bucket/crdb?…';
RESTORE FROM LATEST IN 's3://bucket/crdb?…';
RESTORE TABLE store.orders FROM LATEST IN 's3://…' WITH into_db = 'recovery';


CREATE SCHEDULE store_backup FOR BACKUP INTO 's3://…'
  RECURRING '@hourly' FULL BACKUP '@daily';


CREATE CHANGEFEED FOR TABLE orders INTO 'kafka://broker:9092'
  WITH updated, resolved = '10s', diff;
SHOW CHANGEFEED JOBS;
   gc.ttlseconds MUST exceed your incremental backup
   interval AND your worst changefeed outage. Both
   read MVCC history. This is the #1 misconfiguration.

Jobs

Section titled “Jobs”
SHOW JOBS;
SHOW JOBS WHERE status = 'running';
PAUSE JOB 891…;  RESUME JOB 891…;  CANCEL JOB 891…;

Settings

Section titled “Settings”
SELECT * FROM crdb_internal.cluster_settings
WHERE value != default_value;            -- FIRST thing on a new cluster


SET CLUSTER SETTING server.time_until_store_dead = '5m';
SET CLUSTER SETTING sql.defaults.statement_timeout = '30s';
SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = true;

Node operations

Section titled “Node operations”
Terminal window
cockroach node decommission 8 --certs-dir=certs --wait=all   # ALWAYS this
cockroach node drain 4 --certs-dir=certs                     # before restart
# rolling upgrade: drain → restart → wait → next node
SET CLUSTER SETTING version = crdb_version();   -- finalise, when all done

Diagnosing, in order

Section titled “Diagnosing, in order”
-- 1. which statement?
SELECT key, count, service_lat_avg, rows_avg, retries
FROM crdb_internal.node_statement_statistics
ORDER BY service_lat_avg * count DESC LIMIT 20;


-- 2. contention?
SELECT * FROM crdb_internal.cluster_contention_events
ORDER BY num_contention_events DESC LIMIT 10;


-- 3. the plan
EXPLAIN ANALYZE (DIST, VERBOSE) SELECT …;


-- 4. the ranges
SHOW RANGES FROM TABLE … WITH DETAILS;
   rows read ≫ returned   → index          ch 9, 10
   contention time high   → hot row        ch 5
   network time high      → geography      ch 11
   KV time high           → storage/scan   ch 13
   one node hot           → hot range      ch 3

Error messages, decoded

Section titled “Error messages, decoded”
   SQLSTATE 40001 / TransactionRetryWithProtoRefresh
     → serialization conflict. retry the transaction.


   ReadWithinUncertaintyIntervalError
     → clock uncertainty (ch 6). normal in small
       numbers; check NTP if frequent.


   NotLeaseHolderError
     → stale range cache. routine. constant = leases
       thrashing.


   "clock synchronization error"
     → node shut itself down. FIX NTP.


   "range unavailable" / query hangs
     → lost a majority of a range's replicas. incident.


   "protected timestamp … expired" / "below GC threshold"
     → the GC window passed while a backup or changefeed
       was not running. new full backup / recreate feed.

The operator’s weekly ten minutes

Section titled “The operator’s weekly ten minutes”
   □ unavailable = 0, under-replicated = 0
   □ p99 latency unchanged week on week
   □ retries and contention not climbing
   □ read amplification stable, disks < 60%
   □ backups succeeding — and a restore tested this month
   □ changefeed lag well inside gc.ttlseconds
   □ non-default settings still the ones you intended

Further reading

Section titled “Further reading”
   Spanner paper (Google, 2012)      the design this
                                     descends from
   Raft paper (Ongaro & Ousterhout)  the consensus,
                                     also in the etcd guide
   Cockroach Labs docs               release notes matter
                                     more than blog posts

Read alongside: PostgreSQL for the single-node contrast, etcd for Raft in its simplest form, Cassandra for the opposite bet, and Kafka for where your changefeed lands.

Written by Sepuri Sai Krishna

© 2026CC BY 4.0Source