How it works
Clients connect through a PostgreSQL-compatible wire protocol to a SQL gateway on a CockroachDB node. The gateway parses and plans queries, then coordinates execution across the nodes holding the data. PostgreSQL protocol compatibility does not mean every PostgreSQL feature or extension is supported.
Tables and indexes are encoded into an ordered key-value space, which is divided into contiguous ranges. CockroachDB splits and rebalances ranges as data and load change. Secondary indexes occupy their own key spans, so one SQL write can touch several ranges.
Each range has replicas that use Raft to agree on updates. A range leaseholder normally serves current reads and coordinates writes; the Raft leader handles replication, and these roles are usually colocated. A write requires agreement from a majority of that range's voting replicas, not a majority of the entire cluster.
The storage engine, Pebble, uses an LSM-tree structure, and MVCC retains timestamped versions of values. Hybrid logical clocks help assign and compare timestamps across nodes. Current reads normally go through leaseholders, while eligible historical reads can use nearby replicas at sufficiently old timestamps.
Distributed transactions coordinate writes across ranges using provisional writes called intents and transaction metadata. CockroachDB uses a distributed commit protocol, including parallel commits for eligible transactions, to establish atomic outcomes. Under serializable isolation, conflicts can require restarting a transaction; some retries are automatic, but applications must handle those returned to the client.
What it gives you in a design
- Distributed ACID transactions
- Transactions can update multiple rows, tables, and ranges atomically. This supports balance updates, reservation state, and uniqueness constraints without putting all related data on one database node.
- SQL and relational constraints
- Joins, secondary indexes, foreign keys, and unique constraints support relational application models. Query shape and index design still determine how much cross-node work a request creates.
- Automatic range distribution
- The database handles range splitting, replica placement, and rebalancing rather than requiring an application-maintained shard map. It does not eliminate the need to design keys that avoid concentrated writes.
- Multi-region data placement
- Table localities and survival goals let you align data placement with regional access and failure requirements. Keeping an owning region's work local can reduce latency, while surviving regional loss generally requires more cross-region coordination.
- Historical and follower reads
- Timestamped reads can serve a consistent historical snapshot, and eligible follower reads can avoid a distant leaseholder. These are useful for stale-tolerant reads, not decisions that require the latest inventory or balance.
- Changefeeds
- Changefeeds can publish database changes to downstream systems such as event pipelines and search indexes. Consumers must tolerate duplicates and respect the feed's documented ordering scope rather than assume global event order.
Scaling, replication and failure
Capacity grows by adding nodes and allowing ranges and replicas to rebalance. Primary keys, secondary indexes, and access patterns determine whether traffic spreads effectively. Sequential keys can concentrate inserts at one end of an index; hash-sharded indexes can distribute writes but add read trade-offs.
Replication protects each range independently. With three voting replicas, a range needs two to make progress and can tolerate one voting replica failure. Node or region survival depends on actual replica placement, configured survival goals, and retaining a quorum for every range the operation needs.
During a network partition, a side without a range's voting quorum cannot commit writes to that range. Default serializable transactions favor correctness over accepting conflicting writes on disconnected sides. Some historical reads may remain possible, but that does not make current transactional operations available.
Cross-region transactions and quorum replication add network round trips. Regional locality helps when requests mostly access data owned by one region, but transactions spanning regions and global constraints can still be expensive. Extra nodes do not remove contention on a single inventory row, account, or counter.
Limits and defaults worth knowing
Check the current documentation before relying on an exact value; many of these are configurable.
| Default transaction isolation | SERIALIZABLE is the default. Applications must handle retryable transaction failures, commonly reported with SQLSTATE 40001. |
|---|---|
| Default replication factor | Ordinary ranges commonly default to three replicas. Replication and voting configuration are configurable, and multi-region policies can change the layout. |
| Raft quorum requirement | A range requires a majority of its voting replicas for progress on writes: two of three or three of five. Non-voting replicas do not count toward this quorum. |
| Clock offset | The documented default maximum clock offset is 500 milliseconds and is configurable. Nodes need synchronized clocks; exceeding clock-offset safety bounds can cause a node to shut down. |
| MVCC garbage collection | The default gc.ttlseconds is 90,000 seconds, or 25 hours, and is configurable. Historical reads depend on retained versions; protected timestamps can preserve required history beyond the ordinary retention window. |
Use it when
- The design needs relational constraints and transactions while the dataset or workload must grow beyond one primary database server.
- Reservations, account state, or allocation decisions require atomic updates across multiple records.
- Regional failure tolerance is an explicit requirement, and the latency budget can accommodate the required replica placement.
- Most requests have a clear regional owner, making data locality practical.
- The application can implement bounded transaction retries and keep non-database side effects outside retryable transaction bodies.
Avoid it when
- A single-region PostgreSQL deployment already meets capacity and availability requirements with less operational complexity.
- The workload is primarily large analytical scans or high-volume telemetry ingestion better served by columnar or specialized storage.
- The design requires fresh, strongly consistent writes in every disconnected region during a partition.
- One heavily contended record dominates the workload, or every global write must have local-only latency.
What interviewers ask about it
Follow-ups you can expect once CockroachDB is on your whiteboard, and the core of a strong answer.
- How does CockroachDB prevent two requests from booking the same seat?
- Use a transaction with a conditional state change or an appropriate uniqueness constraint, and check whether the operation succeeded. Concurrent attempts may block, fail the condition, or require transaction retries; adding nodes does not remove contention on that seat.
- What happens when a region goes down?
- Availability depends on whether the configured replica placement preserves voting quorums and whether gateways can reach the surviving replicas. Explain the survival goal and failure-domain layout rather than claiming that any multi-region cluster automatically survives regional loss.
- Why can writes be slow even with a nearby SQL gateway?
- The gateway may need to contact remote leaseholders, and those ranges may need cross-region Raft acknowledgments. A transaction touching several regions also adds coordination, so a local connection does not imply a local transaction.
- How should application code handle transaction retries?
- Retry the whole transaction on retryable failures using bounded backoff, and recalculate decisions from newly read state. Do not charge a payment provider or send an email inside that retry loop; record an outbox entry atomically and perform the external action with idempotency.
- Can follower reads safely serve inventory checks?
- They can serve stale-tolerant browsing, such as an approximate availability display. The final reservation must validate current state in a transaction, because a consistent historical snapshot can still show inventory that has since been sold.
- Does automatic sharding eliminate hot spots?
- No: range splitting and rebalancing distribute separable work, but cannot make concurrent updates to one logical key independent. Spread independent writes through key and index design, and use admission control or a different data model for scarce, highly contended resources.
Common mistakes
- Treating PostgreSQL wire compatibility as complete PostgreSQL feature and extension compatibility.
- Saying that adding nodes makes a single hot row or a cross-region transaction faster.
- Assuming serializable transactions eliminate application retries, request idempotency, or uncertain external payment outcomes.
- Claiming region-level availability without specifying voting replica placement, survival goals, and the latency cost.
Alternatives
- PostgreSQL
- Choose it when one primary can handle the workload and simpler operations, extension support, or existing PostgreSQL tooling matter more than distributed writes.
- Google Cloud Spanner
- Consider it when a managed distributed relational database on Google Cloud fits the deployment, operational, and consistency requirements.
- YugabyteDB
- Evaluate it when distributed SQL is required and its PostgreSQL compatibility, architecture, or deployment model better matches the application; verify the specific features you depend on.
- DynamoDB
- Choose it for managed key-based access with explicit partition-key design when relational joins and general-purpose SQL transactions are not central requirements.
Where it comes up
Interview questions where CockroachDB is a natural fit, and why.
- Design a ticket booking system like TicketmasterConditional seat allocation and transactional reservation state expose both serializable correctness and hot-row contention.
- Design a payment systemAtomic ledger updates and idempotency records fit distributed SQL, while external charges still require careful workflow handling.
- Design a hotel booking systemAtomic multi-night inventory updates illustrate cross-row transactions, conflicts, and retry-safe reservation logic.
- Design a wire transfer systemBalanced ledger entries can share a database transaction, but bank interactions remain outside that atomic boundary.
- Design Netflix's concurrent screen limitAccount-wide slot allocation highlights the trade-off between consistent admission decisions and availability during regional partitions.
Questions people ask
What is CockroachDB used for?
CockroachDB is used for transactional applications that need SQL, horizontal distribution, and resilience to infrastructure failures. In an interview, examples include reservation records, account state, and multi-region application metadata. It is not usually the default choice for media objects or large analytical scans.
Is CockroachDB compatible with PostgreSQL?
CockroachDB supports the PostgreSQL wire protocol and many PostgreSQL SQL features, so many drivers and tools can connect. It is not PostgreSQL internally and does not support every feature, extension, or behavior. Check compatibility and transaction retry handling before assuming an application can migrate unchanged.
Is CockroachDB strongly consistent?
CockroachDB provides strongly consistent transactional operations, with serializable isolation as the default. It also supports explicitly historical reads, which can be stale by design. During a partition, losing the necessary voting quorum prevents writes rather than allowing conflicting histories.