CockroachDB Distributed SQL Database Cheat Sheet

Updated 2026-05-15

Next Topic: Database Caching Strategies and Patterns Cheat Sheet

🧠Study flashcards on this topic93 cards · spaced repetition→

CockroachDB is a cloud-native distributed SQL database built for horizontal scalability, strong consistency, and automatic fault tolerance. It combines PostgreSQL wire protocol compatibility with a distributed architecture based on Raft consensus and range-based sharding, enabling applications to survive availability zone and region failures while maintaining ACID transactions at global scale. One key insight: CockroachDB separates leaseholder (handles reads/writes) from Raft leader (drives consensus) roles per range, optimizing both read performance and write durability, though in practice they're co-located via the leader leases mechanism.

What This Cheat Sheet Covers

This topic spans 19 focused tables and 92 indexed concepts, 93 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Distributed Architecture Core ConceptsTable 2: Multi-Region Table Locality PatternsTable 3: PostgreSQL Compatibility and DialectTable 4: Schema Changes and Online DDLTable 5: Transaction Isolation and Retry HandlingTable 6: Backup and Restore OperationsTable 7: Change Data Capture (Changefeeds)Table 8: Query Optimization and ExecutionTable 9: Data Types and Column FeaturesTable 10: Zone Configuration and Replication ControlTable 11: Cluster Management and MonitoringTable 12: CockroachDB Cloud Deployment OptionsTable 13: SQL Functions and OperatorsTable 14: Data Import and ExportTable 15: Common Table Expressions and Advanced QueriesTable 16: Sequences and Auto-incrementing IDsTable 17: Constraints and Data IntegrityTable 18: Connection ManagementTable 19: Troubleshooting and Performance

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Distributed Architecture Core Concepts

Everything else in CockroachDB rests on the handful of primitives here. Data is carved into ranges, each range is kept consistent by its own Raft group, and a leaseholder fronts reads and writes — once those three ideas click, splitting, rebalancing, and distributed query execution all follow naturally.

Concept	Example	Description
Range	512 MiB default max size	• Contiguous keyspace chunk replicated across nodes • CockroachDB's fundamental unit of data distribution and rebalancing
Raft consensus	3-replica default with quorum writes	• Protocol ensuring strong consistency by requiring majority approval for writes • each range maintains its own Raft group
Leaseholder	Handles all reads/writes for a range	• Replica serving traffic for a specific range • always co-located with Raft leader via leader leases for write consistency
Range splitting	Automatic at 512 MiB or high load	• Divides ranges when size or QPS thresholds exceeded • enables horizontal scalability and hotspot mitigation
Rebalancing	Automatic based on replica count	• Moves replicas across nodes to balance load and satisfy zone constraints • happens continuously in background
DistSQL	Parallel query execution	Distributed SQL execution engine that pushes computation to data nodes for parallel processing across ranges.

Table 2: Multi-Region Table Locality Patterns

When you go multi-region, locality settings decide where each table's data physically lives — and that choice trades read latency against write latency. Pin a table to one region, scatter rows by region for compliance, or replicate globally for fast reads everywhere; the survival goals then control whether the cluster shrugs off a zone outage or a whole region disappearing.

Locality	Example	Description
REGIONAL BY TABLE	`ALTER TABLE users SET LOCALITY REGIONAL BY TABLE IN "us-east"`	• Table pinned to one region • low-latency reads/writes from that region, higher latency elsewhere

The rest of this section is part of the full cheat sheet

Table 3: PostgreSQL Compatibility and Dialect

CockroachDB speaks the Postgres wire protocol, so existing drivers and tools mostly just connect — but the dialect adds its own extensions and a few sharper edges. These are the syntax features you'll actually notice as different, from the blind-write UPSERT and time-travel AS OF SYSTEM TIME to the RETURNING NOTHING performance shortcut.

Feature	Example	Description
pgwire protocol	Version 3.0 compatible	Wire-level PostgreSQL protocol support enables most Postgres drivers/tools to connect without modification.

The rest of this section is part of the full cheat sheet

Table 4: Schema Changes and Online DDL

Schema changes run online here — adding columns, building indexes, and altering types all happen in the background while reads and writes keep flowing, thanks to a multi-version schema engine. The index variants matter most: hash-sharded indexes spread sequential keys to kill hotspots, inverted indexes unlock JSONB and array queries, and partial indexes shrink the index to just the rows you care about.

Operation	Example	Description
ADD COLUMN	`ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'`	Online non-blocking operation; multi-version schema engine allows reads/writes during change.

The rest of this section is part of the full cheat sheet

Table 1: Distributed Architecture Core Concepts

Concept	Example	Description
Range	512 MiB default max size	• Contiguous keyspace chunk replicated across nodes • CockroachDB's fundamental unit of data distribution and rebalancing
Raft consensus	3-replica default with quorum writes	• Protocol ensuring strong consistency by requiring majority approval for writes • each range maintains its own Raft group
Leaseholder	Handles all reads/writes for a range	• Replica serving traffic for a specific range • always co-located with Raft leader via leader leases for write consistency
Range splitting	Automatic at 512 MiB or high load	• Divides ranges when size or QPS thresholds exceeded • enables horizontal scalability and hotspot mitigation
Rebalancing	Automatic based on replica count	• Moves replicas across nodes to balance load and satisfy zone constraints • happens continuously in background
DistSQL	Parallel query execution	Distributed SQL execution engine that pushes computation to data nodes for parallel processing across ranges.

Table 2: Multi-Region Table Locality Patterns

Locality	Example	Description
REGIONAL BY TABLE	`ALTER TABLE users SET LOCALITY REGIONAL BY TABLE IN "us-east"`	• Table pinned to one region • low-latency reads/writes from that region, higher latency elsewhere

Table 3: PostgreSQL Compatibility and Dialect

Feature	Example	Description
pgwire protocol	Version 3.0 compatible	Wire-level PostgreSQL protocol support enables most Postgres drivers/tools to connect without modification.

Table 4: Schema Changes and Online DDL

Operation	Example	Description
ADD COLUMN	`ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'`	Online non-blocking operation; multi-version schema engine allows reads/writes during change.