CockroachDB Distributed SQL for Production Deployments
CockroachDB distributed SQL combines the familiarity of PostgreSQL-compatible syntax with the horizontal scalability of NoSQL databases. Therefore, teams can build globally distributed applications without sacrificing transactional consistency. As a result, it has become a leading choice for applications requiring strong consistency across multiple regions. Unlike a sharded PostgreSQL setup, the cluster handles data placement, rebalancing, and failover itself, so operators reason about regions and survivability rather than manual shard maps.
Architecture and Consensus Protocol
The database stores data in sorted key-value ranges that automatically split, merge, and rebalance across nodes. Moreover, each range maintains three or more replicas using the Raft consensus protocol to ensure durability and consistency. Consequently, the system survives individual node failures without any data loss or availability impact.
The query layer translates SQL into distributed key-value operations. Furthermore, the cost-based optimizer routes queries to the leaseholder replica closest to the data, minimizing cross-region latency. Each range defaults to roughly 512 MiB before it splits, and one replica per range holds the lease that serves reads and coordinates writes. Understanding the leaseholder concept is the single most useful mental model for performance tuning, because a write must reach a quorum of replicas before it commits. When those replicas sit in different regions, that quorum round-trip is the latency floor you cannot optimize away with indexes.
Multi-node cluster with Raft consensus across availability zones
CockroachDB Distributed SQL Multi-Region Configuration
Production deployments spanning multiple regions require careful data placement configuration. Specifically, table-level locality settings control where replicas are stored and which region serves reads. Additionally, you can pin specific rows to regions using regional by row tables for data residency compliance.
-- Configure multi-region database
ALTER DATABASE commerce SET PRIMARY REGION "us-east1";
ALTER DATABASE commerce ADD REGION "us-west2";
ALTER DATABASE commerce ADD REGION "eu-west1";
ALTER DATABASE commerce SET SECONDARY REGION "us-west2";
-- Global table: low-latency reads from any region
CREATE TABLE currencies (
code STRING PRIMARY KEY,
name STRING NOT NULL,
symbol STRING NOT NULL
) LOCALITY GLOBAL;
-- Regional by row: data pinned to user's region
CREATE TABLE user_profiles (
id UUID DEFAULT gen_random_uuid(),
region crdb_internal_region NOT NULL DEFAULT 'us-east1',
email STRING NOT NULL,
display_name STRING NOT NULL,
preferences JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT now(),
PRIMARY KEY (region, id)
) LOCALITY REGIONAL BY ROW;
-- Zone configuration for survivability
ALTER DATABASE commerce SURVIVE REGION FAILURE;
-- Create index with specific zone config
CREATE INDEX idx_profiles_email ON user_profiles (email)
STORING (display_name);
-- Query with region-aware filtering
SELECT id, email, display_name
FROM user_profiles
WHERE region = 'us-east1'
AND created_at > now() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;
The LOCALITY settings control data placement automatically. Therefore, reads from user_profiles in us-east1 never cross region boundaries for locally pinned data.
Choosing the Right Table Locality
The three localities are not interchangeable, and picking the wrong one is the most common multi-region mistake. REGIONAL BY ROW shines for user-scoped data where each row clearly belongs to one region, such as profiles or orders, because reads and writes stay local to the owning region. REGIONAL BY TABLE suits data shared within a region but rarely written, while GLOBAL is reserved for small, read-mostly reference data like currency codes or feature flags.
The trade-off behind GLOBAL tables is worth understanding. They deliver fast, consistent reads from every region by using non-blocking transactions and a small future-time read timestamp, but that same mechanism makes writes slower because the commit waits out a brief uncertainty window. Consequently, a GLOBAL table that receives frequent writes will disappoint, whereas the same table holding rarely-changing lookups performs beautifully. As a practical rule, reach for GLOBAL only when the read-to-write ratio is overwhelmingly lopsided, and default to REGIONAL BY ROW for anything user-facing.
Serializable Isolation and Performance
The database defaults to serializable isolation, the strongest consistency level in SQL. However, this guarantee comes with performance considerations that teams must understand. In contrast to read-committed isolation used by PostgreSQL defaults, serializable prevents all anomalies including phantom reads and write skew.
Transaction retries are normal under serializable isolation. As a result, applications must implement retry loops or use built-in retry mechanisms for contended workloads. When two transactions conflict, the database aborts one with a retryable error rather than serving an inconsistent result, so the client is expected to replay it. Recent versions also offer an opt-in READ COMMITTED isolation level for workloads that cannot tolerate retries, trading some anomaly protection for fewer aborts. The pattern below shows the kind of bounded retry loop teams typically wrap around contended transactions.
-- Application-side retry pattern (pseudocode in SQL form)
-- The driver catches SQLSTATE 40001 (serialization_failure) and replays.
BEGIN;
SAVEPOINT cockroach_restart;
UPDATE inventory
SET stock = stock - 1
WHERE sku = 'WIDGET-42' AND stock > 0;
-- If a conflicting txn touched the same row, the statement above
-- raises 40001; the client rolls back to the savepoint and retries.
RELEASE SAVEPOINT cockroach_restart;
COMMIT;For high-contention hotspots, the most effective fix is usually structural rather than a longer retry loop. Splitting a single counter into per-shard rows, or batching updates, reduces the conflicts that trigger retries in the first place.
Monitoring cluster performance metrics across regions
Production Survivability and Operations
Two survivability modes are available: zone failure survival and region failure survival. Moreover, region failure survival requires at least three regions and five replicas per range, ensuring the database remains available even when an entire cloud region goes offline.
For example, a three-region deployment with SURVIVE REGION FAILURE continues serving reads and writes when any one region becomes unavailable. Additionally, automated rebalancing restores the replication factor once the failed region recovers. The cost of this resilience is concrete: with replicas spread across three regions, write quorum requires acknowledgment from a second region, so cross-region write latency becomes the dominant factor in commit time. Teams therefore reserve SURVIVE REGION FAILURE for data that genuinely must outlive a regional outage, and keep latency-sensitive tables on SURVIVE ZONE FAILURE within a single region.
Multi-region deployment with automatic failover capabilities
When NOT to Choose This Database
Distributed SQL is powerful, but it is not the right default for every project, and an honest assessment prevents costly migrations later. A single-region application with modest traffic gains little from the distributed architecture while paying its overhead; a well-tuned PostgreSQL instance is simpler to operate and often faster for that profile. Likewise, write-heavy workloads with severe contention on a few hot rows fight the serializable model, and the resulting retries can erase the scalability benefit unless the schema is carefully sharded.
There are compatibility caveats too. Although the wire protocol is PostgreSQL-compatible, it is not a drop-in replacement: stored procedures, certain extensions, triggers, and some functions differ or are unsupported, so a lift-and-shift of a complex PostgreSQL schema rarely works untouched. Analytical, scan-heavy reporting also runs better on a columnar engine than on this row-oriented transactional store. In short, choose it when you genuinely need multi-region availability with strong consistency, and reach for simpler tools when you do not. For a side-by-side look at the alternatives, the comparison guides below are a useful next step.
Related Reading:
Further Resources:
In conclusion, CockroachDB distributed SQL delivers PostgreSQL compatibility with global scale and strong consistency guarantees. Therefore, choose it when your application demands multi-region availability with serializable transactions, and weigh the latency and compatibility trade-offs honestly before you commit.