A Senior+ Guide to Building Globally Consistent, Horizontally Scalable SQL Infrastructure
1. Introduction: CockroachDB at Scale
CockroachDB is a distributed SQL database designed from the ground up to survive anything, scale infinitely, and remain consistent across every node in a cluster. Built as a NewSQL system, it combines the familiar SQL interface and ACID transaction guarantees of traditional relational databases with the horizontal scalability and fault tolerance of distributed systems like Google Spanner, from which it draws significant architectural inspiration. CockroachDB is open source, written in Go, and architected to run across multiple data centers, cloud regions, or even continents while maintaining serializable isolation for every transaction.
The fundamental promise of CockroachDB is threefold: serializable isolation, geo-partitioning with data locality, and five-nines (99.999%) uptime. Serializable isolation is the strongest level of transactional consistency, guaranteeing that the result of any concurrent execution is equivalent to some sequential ordering of those transactions. This eliminates an entire class of anomalies such as write skew, read anomalies, and phantom reads that weaker isolation levels permit. Most traditional distributed databases force developers to choose between consistency and availability, but CockroachDB leverages its Raft-based consensus protocol and hybrid logical clocks to provide serializable consistency without sacrificing availability during single-node or even entire-region failures.
Geo-partitioning in CockroachDB allows you to pin rows of data to specific geographic regions at the row level, not merely at the table level. This means a multi-national SaaS application can store European user data exclusively in EU-based nodes, American user data in US regions, and Asian data in APAC nodes — all within the same logical table and the same SQL connection. The result is dramatically reduced read latency, compliance with data residency regulations like GDPR and LGPD, and consistent global queries that span regions when needed. CockroachDB achieves this through a combination of zone configurations, partitioning, and locality-optimized indexes that route reads and writes to the optimal replica locations.
The 99.999% uptime target — amounting to roughly 26 seconds of downtime per year — is achieved through automatic range-level replication, leader rebalancing, online schema changes, non-blocking reads during rebalancing, and zero-downtime node decommissioning. CockroachDB uses Raft consensus groups for each range, and these ranges are automatically replicated across nodes. When a node fails, its ranges elect new leaders on surviving replicas within seconds. When nodes are added or removed, ranges are automatically rebalanced. All of this happens transparently to the application.
Under the hood, CockroachDB separates SQL processing from storage in a clean layered architecture. The SQL layer parses, plans, and optimizes queries. The distribution layer handles range-based data partitioning, routing, and metadata management. The storage layer, built on Pebble (CockroachDB's custom LSM-based storage engine), handles the actual persistent storage of key-value pairs. This separation allows each layer to scale independently and enables features like distributed query execution where a single SQL query can be decomposed into ranges and executed in parallel across dozens of nodes.
CockroachDB has been adopted by companies across financial services, gaming, e-commerce, and SaaS industries. Notable adopters include Netflix, Baidu, and numerous fintech companies that require both SQL compatibility and global distribution. The database supports PostgreSQL wire protocol compatibility, meaning that most existing PostgreSQL ORMs, drivers, and tools work with CockroachDB out of the box, dramatically reducing migration friction.
Why CockroachDB Over Alternatives
When evaluating distributed databases, engineers face a spectrum of trade-offs. Traditional PostgreSQL or MySQL with manual sharding (using Vitess or application-level routing) provides SQL compatibility but requires significant engineering effort for data distribution, failover handling, and cross-shard transactions. NoSQL databases like Cassandra or DynamoDB provide automatic distribution but sacrifice SQL semantics, ACID transactions, and relational data modeling. NewSQL databases like CockroachDB bridge this gap by providing the full SQL experience with automatic distribution. Compared to Google Spanner, CockroachDB offers the same architectural philosophy but with cloud-agnostic deployment, open-source availability, and a PostgreSQL-compatible interface. Compared to TiDB, CockroachDB provides stronger default isolation (serializable vs. snapshot) and finer-grained geo-partitioning. Compared to YugabyteDB, CockroachDB has a more focused SQL-only approach with a custom storage engine optimized for its workload patterns.
The decision to use CockroachDB typically crystallizes around three requirements: the need for horizontal SQL scaling without manual sharding, the need for multi-region data placement with strong consistency, and the need for high availability that survives region-level failures. If your application requires any two of these three properties, CockroachDB is likely the optimal choice. The database excels in scenarios where data must be globally distributed but queries must remain strongly consistent — financial transactions across countries, user data that must comply with regional regulations, and real-time systems that cannot tolerate the eventual consistency offered by many distributed alternatives.
| Feature | CockroachDB | Traditional RDBMS | NoSQL (DynamoDB) |
|---|---|---|---|
| SQL Support | Full ANSI SQL + PostgreSQL | Full ANSI SQL | NoSQL / PartiQL |
| ACID Transactions | Serializable (strongest) | Serializable | Eventually consistent |
| Horizontal Scaling | Automatic, transparent | Manual sharding | Built-in |
| Geo-Distribution | Row-level geo-partitioning | Manual replication | Global tables (limited) |
| High Availability | 99.999% (automatic failover) | Manual HA setup | 99.99% |
| Online DDL | Yes (non-blocking) | Often locks tables | Schema-less |
2. Architecture Overview
CockroachDB's architecture is organized into three primary layers, each with distinct responsibilities and the ability to scale independently. The SQL Layer accepts PostgreSQL wire protocol connections, parses SQL into ASTs, performs semantic analysis, feeds queries into the cost-based optimizer (CBO), and executes the resulting distributed plan. The Distribution Layer maintains the mapping from SQL tables to key ranges, tracks range locations across the cluster, routes read and write requests to appropriate replicas, and manages range metadata including splits and merges. The Storage Layer uses Pebble, an LSM-tree based key-value engine, for persistent storage with native MVCC support, bloom filters, and block caching. This three-layer architecture is a deliberate design decision that mirrors how Google Spanner and F1 separate concerns, but CockroachDB implements all layers in open-source Go rather than relying on proprietary infrastructure.
Beyond the three primary layers, CockroachDB includes several critical supporting subsystems. The gossip protocol enables automatic node discovery and cluster metadata dissemination without requiring external coordination services like ZooKeeper or etcd. Each node periodically gossips information about its store capacity, ranges, and latency to other nodes, building a cluster-wide view of the topology. The allocator subsystem uses this gossip information to make automatic data placement decisions, ensuring ranges are distributed evenly across nodes and failure domains while respecting zone constraints. The jobs system tracks long-running operations such as backups, schema changes, and data imports, providing progress tracking and failure recovery. These subsystems collectively make CockroachDB a self-managing database that requires minimal operational intervention once deployed.
SQL Layer Deep Dive
The SQL layer is where CockroachDB's PostgreSQL compatibility truly shines. When a client establishes a connection, it goes through the pgwire protocol handler which supports both simple and extended query protocols. The parser converts SQL text into an AST using a grammar largely compatible with PostgreSQL, with some CockroachDB-specific extensions. The cost-based optimizer is the intellectual core of the SQL layer — it uses histogram-based statistics to estimate execution strategy costs and selects the optimal plan. Join ordering, index selection, statistics-based cardinality estimation, and join algorithm selection (hash join, merge join, lookup join) are all handled by the optimizer. The optimizer also performs predicate pushdown, projection pruning, and filter pushdown to minimize data movement.
Distribution Layer Mechanics
The distribution layer implements CockroachDB's distributed data model. Every table is divided into ranges covering contiguous portions of the key space. Each range is identified by a unique range ID and replicated across multiple nodes using Raft. The DistSender component forwards requests to the correct node when a gateway receives a request for a range it does not own. The range descriptor cache reduces metadata lookup latency by caching recently accessed range locations. The lease manager assigns range leases to specific nodes, enabling single-node reads without going through Raft consensus for read operations.
Storage Layer: Pebble
Pebble is CockroachDB's custom LSM-tree storage engine written in Go, designed as a replacement for RocksDB. It provides a key-value interface where all data is stored as sorted byte slices. The key encoding scheme maps SQL tables and indexes to key-value pairs using a structured encoding that preserves lexicographic ordering, ensuring that range scans translate efficiently into sequential I/O. Pebble supports MVCC natively, storing multiple versions tagged with timestamps, enabling CockroachDB's multi-version concurrency control without a separate MVCC layer. The compaction process in Pebble is optimized for CockroachDB's workload patterns, with dynamic level targeting and subcompaction for parallel compaction of large SSTables.
Gateway Nodes and Load Balancing
Every node in CockroachDB can serve as a gateway node, accepting SQL queries, planning them, and executing them regardless of where the data resides. This eliminates the need for a dedicated SQL proxy, although load balancers are commonly used for connection management. The gateway node becomes the coordination point for a query, assembling results from remote ranges and returning them to the client. Adding more nodes increases both storage capacity and SQL processing capacity simultaneously. The gossip protocol enables automatic node discovery and dissemination of cluster metadata without requiring a centralized coordination service like ZooKeeper.
| Layer | Primary Responsibility | Key Components | Scalability |
|---|---|---|---|
| SQL Layer | Query parsing, optimization, execution | Parser, CBO, DistSQL, Session Manager | Horizontal (stateless) |
| Distribution Layer | Data partitioning, routing, metadata | DistSender, RangeCache, LeaseManager | Automatic splits/merges |
| Storage Layer | Persistent key-value storage | Pebble, Raft, MVCC, Compaction | Per-node, auto-rebalanced |
3. Consensus Protocol (Raft)
CockroachDB uses the Raft consensus protocol to ensure every write is durably replicated across a majority of replicas before being acknowledged. Each range has its own Raft group operating independently, meaning a range of 3 replicas requires 2 of 3 to acknowledge a write before it is considered committed — ensuring durability against any single replica failure. Raft was chosen over Paxos for its understandability and equivalent safety guarantees. The protocol operates through a leader-based replication model where one replica is elected leader, responsible for receiving writes, sequencing them through a monotonically increasing Raft log index, and replicating entries to followers.
Leader Election and Pre-Vote
When a Raft group is created or the leader becomes unreachable, remaining replicas initiate leader election. Each candidate increments its term number and requests votes, with each replica granting its vote to the first candidate heard in a given term. CockroachDB optimizes this through the pre-vote extension, which prevents disruptive elections by checking whether a candidate could win before incrementing the term. This prevents partitioned nodes from causing unnecessary term increments upon rejoining.
In CockroachDB, the range lease is layered on top of Raft leadership. While Raft leadership handles log replication, the range lease provides a single point for serving reads. The leaseholder is guaranteed to have applied all committed Raft entries and can serve consistent reads without going through Raft, significantly reducing read latency. The leaseholder and Raft leader are typically the same node but can differ for leader-follower workloads.
Range Replication Strategy
Each range is by default replicated three times across different nodes, with the allocator ensuring replicas span failure domains — different availability zones, regions, or racks. Zone configurations govern replica placement with constraints like +region=us-east1. When a node fails, the allocator automatically moves replicas to healthy nodes. The Raft log itself requires careful management — entries grow as writes are proposed and shrink as followers acknowledge. Log compaction uses snapshotting to truncate old entries, preventing unbounded growth and reducing catch-up time for new replicas.
C#
using Npgsql;
public class RaftHealthMonitor
{
private readonly string _connectionString;
public RaftHealthMonitor(string connectionString) { _connectionString = connectionString; }
public async Task<List<RaftRangeStatus>> GetRangeHealthAsync()
{
var results = new List<RaftRangeStatus>();
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
var query = @"SELECT range_id, start_pretty, end_pretty,
array_length(replicas,1) as rep_count, lease_holder,
CASE WHEN lease_holder = ANY(replicas) THEN 'healthy' ELSE 'degraded' END
FROM crdb_internal.ranges_no_leases ORDER BY range_id LIMIT 100;";
await using var cmd = new NpgsqlCommand(query, conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
results.Add(new RaftRangeStatus {
RangeId = reader.GetInt64(0),
StartKey = reader.GetString(1),
EndKey = reader.GetString(2),
ReplicaCount = reader.GetInt32(3),
Status = reader.GetString(5)
});
}
return results;
}
public async Task<int> GetUnderReplicatedRangesAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(
"SELECT COUNT(*) FROM crdb_internal.ranges_no_leases WHERE array_length(replicas,1) < 3;", conn);
return Convert.ToInt32(await cmd.ExecuteScalarAsync());
}
}
public class RaftRangeStatus
{
public long RangeId { get; set; }
public string StartKey { get; set; } = "";
public string EndKey { get; set; } = "";
public int ReplicaCount { get; set; }
public string Status { get; set; } = "";
}
Handling Network Partitions
When network partitions occur, Raft ensures safety through majority quorum. If a partition isolates the leader from the majority, it can no longer commit entries and steps down after election timeout. The majority partition elects a new leader and continues serving. When the partition heals, the old leader discovers the new leader and replicates committed entries to converge. This guarantees committed writes are never lost. In CockroachDB, the range lease adds another dimension to partition handling: if a partition isolates the leaseholder from its range's replicas, the lease expires after the configured duration, and a new leaseholder is elected on the majority side. This ensures that the majority partition can continue serving both reads and writes, while the minority partition returns lease expiration errors to clients rather than serving stale data.
Raft Performance Optimizations
CockroachDB implements several performance optimizations on top of standard Raft. Pipelining allows the leader to propose new log entries before previous entries are fully committed, increasing write throughput when network latency is high. Parallel append processing allows the leader to send append requests to multiple followers simultaneously rather than sequentially. The leader batching optimization groups multiple pending proposals into a single append request, reducing the per-entry overhead of Raft messaging. These optimizations collectively enable CockroachDB to achieve high write throughput even across geographically distributed clusters where inter-node latency is significant. The trade-off is slightly increased complexity in the implementation, but the performance benefits are substantial for real-world workloads.
| Raft Component | Purpose | CockroachDB Optimization |
|---|---|---|
| Leader Election | Single leader per term | Pre-vote prevents disruptive elections |
| Log Replication | Durably replicate writes | Pipeline replication with batching |
| Commit Protocol | Majority quorum | Leaseholder reads skip Raft |
| Log Compaction | Prevent unbounded growth | Snapshot-based with parallel streaming |
| Membership Changes | Dynamic replica add/remove | Constraint-aware automatic placement |
4. Distributed SQL Engine
CockroachDB's distributed SQL engine transforms it from a distributed key-value store into a true distributed relational database. The engine decomposes SQL queries into distributed execution flows that parallelize across multiple nodes, assemble at a gateway node, and return results transparently. This is achieved through the cost-based optimizer (CBO), the DistSQL execution framework, and the two-phase commit (2PC) protocol for distributed transactions.
Two-Phase Commit (2PC)
When a transaction touches data across multiple ranges, CockroachDB uses 2PC for atomicity. In Phase 1 (Prepare), the coordinator gathers write intents from all participating ranges and transitions them to a staged state. Each range writes an intent and records participation in the transaction record, which is stored with the first key written. In Phase 2 (Commit), if all ranges staged successfully, the coordinator marks the transaction record as committed, triggering intent resolution. If Phase 1 fails at any range, the coordinator marks the transaction aborted and cleans up all intents. The transaction record acts as the single source of truth — even if the coordinator crashes, other nodes can check it to resolve or clean up intents.
Distributed Query Execution
The DistSQL framework implements a push-based data flow model. A physical plan is decomposed into flows running on different nodes, each processing data and pushing results to the next pipeline stage. For example, a distributed sort might scan data on three nodes, push sorted results to a merge node on the gateway, which assembles the final result. The framework supports table scans, index lookups, joins, aggregations, sorts, limits, and window functions — all distributable when beneficial. The optimizer uses table statistics including histograms for cardinality estimation, enabling informed decisions about join ordering, index selection, and algorithm choice.
C#
using Npgsql;
using System.Data;
public class CockroachDbDistributedQueryService
{
private readonly string _connectionString;
public CockroachDbDistributedQueryService(string connectionString) { _connectionString = connectionString; }
public async Task ExecuteDistributedJoinAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
var query = @"SELECT c.customer_id, c.name, c.region,
COUNT(o.order_id) as order_count, SUM(o.total_amount) as total_spent
FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.created_at > NOW() - INTERVAL '90 days'
GROUP BY c.customer_id, c.name, c.region
HAVING COUNT(o.order_id) > 5
ORDER BY total_spent DESC LIMIT 100;";
await using var cmd = new NpgsqlCommand(query, conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
Console.WriteLine($"Customer {reader.GetString(1)} ({reader.GetString(2)}): " +
$"{reader.GetInt32(3)} orders, ${reader.GetDecimal(4):N2} total");
}
}
public async Task AnalyzeQueryPlanAsync(string sqlQuery)
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand($"EXPLAIN (DISTSQL) {sqlQuery}", conn);
await using var reader = await cmd.ExecuteReaderAsync();
Console.WriteLine("=== Distributed Execution Plan ===");
while (await reader.ReadAsync()) Console.WriteLine(reader.GetString(0));
}
public async Task ExecuteMultiRangeTransactionAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var tran = await conn.BeginTransactionAsync(IsolationLevel.Serializable);
var insertOrder = new NpgsqlCommand(
"INSERT INTO orders (customer_id, total_amount, region) VALUES (@cid, @amt, @rgn) RETURNING order_id;", conn, tran);
insertOrder.Parameters.AddWithValue("@cid", 1001L);
insertOrder.Parameters.AddWithValue("@amt", 250.00m);
insertOrder.Parameters.AddWithValue("@rgn", "us-east1");
var orderId = await insertOrder.ExecuteScalarAsync();
var insertPayment = new NpgsqlCommand(
"INSERT INTO payments (order_id, amount, method) VALUES (@oid, @amt, 'credit_card');", conn, tran);
insertPayment.Parameters.AddWithValue("@oid", orderId);
insertPayment.Parameters.AddWithValue("@amt", 250.00m);
await insertPayment.ExecuteNonQueryAsync();
await tran.CommitAsync();
Console.WriteLine($"Transaction committed atomically. Order ID: {orderId}");
}
}
| Component | Role | Key Detail |
|---|---|---|
| Cost-Based Optimizer | Select optimal plan | Histograms, distinct counts, null fractions |
| DistSQL Executor | Distributed flows | Push-based parallel execution |
| Two-Phase Commit | Atomic cross-range TXN | Transaction record as source of truth |
| Intent Resolver | Promote provisional values | Asynchronous after commit |
| Leaseholder Reads | Consistent reads without Raft | Applied all committed entries |
5. Data Model: Ranges, Tablets, and Meta Ranges
CockroachDB's data model is built on ranges — contiguous key-space segments forming the fundamental unit of distribution and replication. Each range typically covers up to 512 MB of data, automatically splitting when exceeding this threshold and merging when adjacent ranges are small enough. All SQL data is encoded as key-value pairs in Pebble using a structured scheme that maps tables, indexes, and columns to byte sequences preserving ordering. The general form is /table/index/column1/column2/.../value, where table ID 53 with primary index 1 encodes as /53/1/{pk_value}. This encoding means range scans translate directly into efficient sequential scans on the LSM storage engine.
Meta Ranges: The Directory
Meta ranges serve as the directory for all other ranges, storing range descriptors with key boundaries, replica locations, and leaseholder info. There are three levels: Meta1 stores Meta2 boundaries, Meta2 stores data range boundaries, and data ranges store SQL data. A node locating a key starts at the known Meta1 location, which points to the appropriate Meta2, which points to the data range. This hierarchical structure mirrors a B-tree and scales to millions of ranges. Meta ranges themselves are split when large and cached at the distribution layer to reduce lookup latency.
Range Splitting and Merging
Splitting triggers automatically when ranges exceed 512 MB or when the split controller detects hot ranges. The split is itself a Raft-replicated operation ensuring consistent boundary knowledge across replicas. A new range descriptor is created for the right half, and meta ranges are updated atomically. Merging occurs when adjacent ranges are small enough, reducing management overhead. Manual splitting via ALTER TABLE SPLIT AT is available for pre-splitting hot keys to avoid initial hotspots.
C#
using Npgsql;
public class RangeManagementService
{
private readonly string _connectionString;
public RangeManagementService(string connectionString) { _connectionString = connectionString; }
public async Task<List<RangeInfo>> GetRangeInformationAsync()
{
var ranges = new List<RangeInfo>();
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(@"SELECT range_id, start_pretty, end_pretty,
database_name, table_name, lease_holder, array_length(replicas,1),
range_size, live_bytes FROM crdb_internal.ranges
WHERE database_name NOT IN ('system','crdb_internal')
ORDER BY range_size DESC LIMIT 50;", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
ranges.Add(new RangeInfo {
RangeId = reader.GetInt64(0), StartKey = reader.GetString(1),
EndKey = reader.GetString(2), Database = reader.GetString(3),
Table = reader.GetString(4), LeaseHolder = reader.GetInt64(5),
ReplicaCount = reader.GetInt32(6), RangeSizeBytes = reader.GetInt64(7)
});
}
return ranges;
}
public async Task ConfigureZoneConstraintsAsync(string tableName, string constraints)
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand($@"ALTER TABLE {tableName} CONFIGURE ZONE USING
num_replicas=3, constraints='{constraints}', gc.ttlseconds=86400,
lease_preferences='[[+region=us-east1]]';", conn);
await cmd.ExecuteNonQueryAsync();
}
}
public class RangeInfo
{
public long RangeId { get; set; }
public string StartKey { get; set; } = "";
public string EndKey { get; set; } = "";
public string Database { get; set; } = "";
public string Table { get; set; } = "";
public long LeaseHolder { get; set; }
public int ReplicaCount { get; set; }
public long RangeSizeBytes { get; set; }
}
| Component | Description | Default |
|---|---|---|
| Range | Contiguous key-space segment | Max 512 MB, 3 replicas |
| Meta1 Range | Directory of Meta2 ranges | /Meta1 prefix |
| Meta2 Range | Directory of data ranges | /Meta2 prefix |
| Write Intent | Uncommitted MVCC value | Resolved on commit/abort |
| Transaction Record | Single truth for TXN status | Colocated with first key |
6. Transaction Model
CockroachDB enforces serializable isolation for every transaction — the strongest SQL isolation level. This prevents dirty reads, non-repeatable reads, phantom reads, and write skew. Under the hood, it uses multi-version concurrency control (MVCC) with hybrid logical clocks (HLC). Every value is stored with a timestamp; readers see a consistent snapshot, while writers create new versions rather than overwriting. The HLC combines physical wall clock with a logical counter, providing approximately synchronized timestamps across nodes while maintaining causal ordering.
Write Intents and Conflict Resolution
When a transaction writes, it creates a write intent — a provisional value visible only to that transaction. Other transactions attempting to read the same key either block or read the previous committed version. Intents store the transaction ID and write timestamp. If a write conflict is detected — another transaction committed a value between the read and proposed commit timestamps — CockroachDB may push the timestamp forward or restart the transaction. Application code must implement retry logic for serialization failures (PostgreSQL error code 40001), catching the error, rolling back, and restarting. The retry budget prevents infinite loops, and typically the vast majority of transactions complete on the first attempt. In production workloads, the retry rate for well-designed schemas is typically below 1%, though highly contended workloads with hot keys may see higher rates.
The write intent resolution process is an important aspect of CockroachDB's transaction performance. After a transaction commits, its intents must be resolved — promoted from provisional to committed values — before subsequent readers can see the committed data without blocking. CockroachDB resolves intents eagerly in the background, but during high contention, unresolved intents can cause read latency spikes as readers must wait for intent resolution or read older MVCC versions. The transaction liveness mechanism uses heartbeat transactions to detect abandoned intents from crashed coordinators. If a transaction coordinator crashes, its intents are eventually cleaned up by other nodes that observe the abandoned transaction through the transaction record, ensuring that locks do not persist indefinitely.
Read-Only Transactions and Follower Reads
CockroachDB optimizes read-only transactions through several mechanisms. Standard reads are served by the leaseholder, which has applied all committed Raft entries and can return consistent results without Raft overhead. For analytics workloads that tolerate stale data, follower reads (using AS OF SYSTEM TIME with a past timestamp) serve reads from any replica, reducing load on the leaseholder and providing region-local latency. Historical reads (using AS OF SYSTEM TIME with a timestamp in the past, not just a few seconds) can read data as it existed at any point within the GC TTL window, enabling time-travel queries and consistent backups without blocking. These read optimization paths allow CockroachDB to handle mixed OLTP/OLAP workloads effectively within the same cluster.
Hybrid Logical Clocks
The HLC is central to serializable isolation across distributed nodes. Physical clocks are never perfectly synchronized — NTP drift and network latency introduce uncertainties. The HLC combines wall clock with a logical counter for monotonically increasing, approximately synchronized timestamps. When a node sends a message, it includes its HLC timestamp; the receiver advances its HLC to be at least as large, ensuring causal ordering. A transaction's read timestamp is determined at the first read, pushed to the current HLC time for consistent snapshot reads. This mechanism ensures reads and writes are consistent with serializable ordering.
C#
using Npgsql;
using System.Diagnostics;
public class SerializableTransactionService
{
private readonly string _connectionString;
public SerializableTransactionService(string connectionString) { _connectionString = connectionString; }
public async Task TransferFundsAsync(long fromId, long toId, decimal amount)
{
var sw = Stopwatch.StartNew();
for (int attempt = 1; attempt <= 5; attempt++)
{
try
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var tran = await conn.BeginTransactionAsync();
var readCmd = new NpgsqlCommand(
"SELECT balance FROM accounts WHERE account_id=@id FOR UPDATE;", conn, tran);
readCmd.Parameters.AddWithValue("@id", fromId);
var balance = (decimal)(await readCmd.ExecuteScalarAsync() ?? 0m);
if (balance < amount) { await tran.RollbackAsync(); throw new InvalidOperationException("Insufficient funds"); }
var debit = new NpgsqlCommand(
"UPDATE accounts SET balance=balance-@amt WHERE account_id=@id;", conn, tran);
debit.Parameters.AddWithValue("@amt", amount); debit.Parameters.AddWithValue("@id", fromId);
await debit.ExecuteNonQueryAsync();
var credit = new NpgsqlCommand(
"UPDATE accounts SET balance=balance+@amt WHERE account_id=@id;", conn, tran);
credit.Parameters.AddWithValue("@amt", amount); credit.Parameters.AddWithValue("@id", toId);
await credit.ExecuteNonQueryAsync();
var audit = new NpgsqlCommand(
"INSERT INTO transfers (from_acct, to_acct, amount, created_at) VALUES (@f,@t,@a,NOW());", conn, tran);
audit.Parameters.AddWithValue("@f", fromId); audit.Parameters.AddWithValue("@t", toId);
audit.Parameters.AddWithValue("@a", amount);
await audit.ExecuteNonQueryAsync();
await tran.CommitAsync();
sw.Stop();
Console.WriteLine($"Transfer committed on attempt {attempt} in {sw.ElapsedMilliseconds}ms");
return;
}
catch (PostgresException ex) when (ex.SqlState == "40001")
{
Console.WriteLine($"Serialization conflict on attempt {attempt}, retrying...");
}
}
}
}
| Isolation Level | Dirty Read | Non-Repeatable | Phantom | Write Skew | CockroachDB |
|---|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | Possible | Emulated via SERIALIZABLE |
| Read Committed | Prevented | Possible | Possible | Possible | Emulated via SERIALIZABLE |
| Snapshot Isolation | Prevented | Prevented | Possible | Possible | Not offered |
| SERIALIZABLE | Prevented | Prevented | Prevented | Prevented | Default and only |
7. Geo-Partitioning and Data Locality
Geo-partitioning allows pinning specific rows to specific geographic regions at the row level. A user in Tokyo has data stored exclusively on APAC nodes, while a user in Berlin has data on EU nodes — all within the same logical table. This is implemented through table partitioning, zone constraints, and locality-optimized interleaving. The region column must be part of the primary key (or every index prefix) to ensure index colocated placement, eliminating cross-region round trips for single-row queries.
Regional Table Configuration
A regional table partitions rows by a region column with each partition constrained to that region's nodes. The REGIONAL BY ROW locality setting automates this. Each partition is configured with zone constraints specifying target regions and availability zones, ensuring replicas stay within the designated region. This provides strong data residency guarantees for GDPR, LGPD, and similar regulations while optimizing read latency to 1-5ms for region-local queries.
C#
using Npgsql;
public class GeoPartitioningSetupService
{
private readonly string _connectionString;
public GeoPartitioningSetupService(string connectionString) { _connectionString = connectionString; }
public async Task CreateRegionalUsersTableAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(@"CREATE TABLE users (
user_id UUID NOT NULL DEFAULT gen_random_uuid(),
email STRING NOT NULL, name STRING NOT NULL, region STRING NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT users_pkey PRIMARY KEY (region, user_id)
) LOCALITY REGIONAL BY ROW;", conn);
await cmd.ExecuteNonQueryAsync();
Console.WriteLine("Geo-partitioned users table created.");
}
public async Task ConfigureRegionalPartitionsAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var c1 = new NpgsqlCommand(@"ALTER PARTITION us_east OF TABLE users CONFIGURE ZONE USING
num_replicas=3, constraints='{+region=us-east1:1}',
lease_preferences='[[+region=us-east1]]';", conn);
await c1.ExecuteNonQueryAsync();
await using var c2 = new NpgsqlCommand(@"ALTER PARTITION eu_west OF TABLE users CONFIGURE ZONE USING
num_replicas=3, constraints='{+region=europe-west1:1}',
lease_preferences='[[+region=europe-west1]]';", conn);
await c2.ExecuteNonQueryAsync();
await using var c3 = new NpgsqlCommand(@"ALTER PARTITION ap_south OF TABLE users CONFIGURE ZONE USING
num_replicas=3, constraints='{+region=asia-south1:1}',
lease_preferences='[[+region=asia-south1]]';", conn);
await c3.ExecuteNonQueryAsync();
Console.WriteLine("Regional partitions configured.");
}
}
Locality-Optimized Queries
When querying geo-partitioned tables, the optimizer is partition-aware and routes queries to relevant partitions only. A query filtering by region touches only that region's nodes. Joins on the region column execute locally within each region with results merged at the gateway. For global queries (dashboards aggregating all regions), CockroachDB uses scatter-gather: parallel requests to each region, partial results collected at the gateway. This optimizes the common case while supporting global queries without special application logic.
| Strategy | Use Case | Read Latency | Write Latency |
|---|---|---|---|
| Regional by Row | OLTP with regional users | 1-5ms | 5-15ms |
| Regional by Table | Dedicated regional datasets | 1-5ms | 5-15ms |
| Global Tables | Reference/lookup data | 1-5ms (local) | 50-200ms (async) |
| Follower Reads | Analytics, dashboards | 1-5ms (stale) | N/A |
8. Multi-Region Deployment
Multi-region CockroachDB provides primitives for precise data replication control: regional tables, global tables, survival goals (zone and region), and online partition movement. The foundation is regions (geographic areas like us-east1) and availability zones (us-east1-a, us-east1-b, us-east1-c). CockroachDB uses this hierarchy for intelligent replica placement. Survival goals define failure tolerance: zone survival (loss of one AZ) requires 3 replicas across 3 AZs; region survival (loss of entire region) requires replicas across 2+ regions with writes committed to both before acknowledgment.
Survival Goals
Zone survival ensures availability when one AZ fails — writes need only reach replicas within a region (5-15ms latency). Region survival ensures availability when an entire region is lost — writes must reach replicas in multiple regions (adding 50-100ms cross-region latency), but reads remain local. Most applications use zone survival for the best balance. Global tables are replicated to all regions for fast local reads of reference data, with async replication providing eventual consistency. Follower reads serve stale data from local replicas, useful for analytics and dashboards that tolerate slight staleness.
C#
using Npgsql;
public class MultiRegionDeploymentService
{
private readonly string _connectionString;
public MultiRegionDeploymentService(string connectionString) { _connectionString = connectionString; }
public async Task SetupMultiRegionDatabaseAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(@"ALTER DATABASE myapp PRIMARY REGION us-east1;
ALTER DATABASE myapp ADD REGION europe-west1;
ALTER DATABASE myapp ADD REGION asia-south1;", conn);
await cmd.ExecuteNonQueryAsync();
await using var surv = new NpgsqlCommand("ALTER DATABASE myapp SURVIVE REGION FAILURE;", conn);
await surv.ExecuteNonQueryAsync();
Console.WriteLine("Multi-region database with region survival configured.");
}
public async Task CreateRegionalOrderTableAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(@"CREATE TABLE orders (
order_id UUID NOT NULL DEFAULT gen_random_uuid(), user_id UUID NOT NULL,
region STRING NOT NULL, total_amount DECIMAL(12,2) NOT NULL,
status STRING NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT pk_orders PRIMARY KEY (region, order_id)
) LOCALITY REGIONAL BY ROW AS region;", conn);
await cmd.ExecuteNonQueryAsync();
}
public async Task ExecuteFollowerReadAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(@"SELECT region, COUNT(*), SUM(total_amount)
FROM orders AS OF SYSTEM TIME '-10s' GROUP BY region ORDER BY 3 DESC;", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
Console.WriteLine($"{reader.GetString(0)}: {reader.GetInt32(1)} orders, {reader.GetDecimal(2):C}");
}
}
| Configuration | Write Latency | Read Latency | Survivability | Best For |
|---|---|---|---|---|
| Zone Survival | 5-15ms | 1-5ms | AZ failure | Most OLTP |
| Region Survival | 50-150ms | 1-5ms | Region failure | Mission-critical |
| Global Tables | 50-200ms | 1-5ms local | High | Reference data |
| Follower Reads | N/A | 1-5ms stale | N/A | Analytics |
9. Schema Changes (Online DDL)
CockroachDB implements all DDL operations as online, non-blocking operations. Schema changes are multi-step state machines transitioning through intermediate states. Adding a column first adds metadata in the "delete only" state (writes include column, reads ignore it), then transitions to "write only" (new indexes active), and finally "public" (reads see the column). Throughout, concurrent reads and writes continue uninterrupted. Creating indexes runs in the background with parallel range backfill, never blocking queries. All schema changes are atomic, idempotent, and replicated through Raft for consistent cluster-wide visibility.
Adding Columns and Indexes Online
Adding a nullable column is instantaneous — no backfill needed. Creating an index on a large table uses parallel backfill across ranges, taking 10-60 minutes for terabyte-scale tables but never blocking operations. Column type changes for compatible types are also online. The jobs system tracks progress, visible via SHOW JOBS. Hash partitioning distributes data evenly for high-cardinality keys, while range partitioning groups related data for efficient scans.
C#
using Npgsql;
public class OnlineDdlService
{
private readonly string _connectionString;
public OnlineDdlService(string connectionString) { _connectionString = connectionString; }
public async Task AddColumnOnlineAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(
"ALTER TABLE users ADD COLUMN phone_number STRING NULL DEFAULT 'N/A';", conn);
await cmd.ExecuteNonQueryAsync();
Console.WriteLine("Column added online.");
}
public async Task CreateIndexOnlineAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(
"CREATE INDEX CONCURRENTLY idx_users_phone ON users (region, phone_number) WHERE phone_number != 'N/A';", conn);
await cmd.ExecuteNonQueryAsync();
Console.WriteLine("Index created online with concurrent backfill.");
}
public async Task MonitorSchemaChangeProgressAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(
"SELECT job_id, status, fraction_completed, statement FROM [SHOW JOBS] WHERE job_type='SCHEMA CHANGE' ORDER BY start DESC LIMIT 10;", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
Console.WriteLine($"Job {reader.GetInt64(0)}: {reader.GetString(1)} ({reader.GetDouble(2):P0}) - {reader.GetString(3)}");
}
}
| DDL Operation | Online | Backfill | Duration (1TB) |
|---|---|---|---|
| ADD COLUMN nullable | Yes | No | <1 second |
| ADD COLUMN NOT NULL default | Yes | No | <1 second |
| CREATE INDEX | Yes | Parallel range scan | 10-60 min |
| DROP COLUMN | Yes | No | <1 second |
| ALTER COLUMN TYPE | Yes (compatible) | Depends | Variable |
| ADD CONSTRAINT NOT NULL | Yes | Validates data | 10-60 min |
10. Changefeeds (CDC)
Change Data Capture through changefeeds provides continuous streams of row-level changes (inserts, updates, deletes) for specified tables. Built on MVCC infrastructure, changefeeds monitor timestamp history without scanning entire tables. Each event includes primary key, updated columns, timestamp, and operation type. Resolved timestamps indicate all changes up to that point have been emitted. Changefeeds support Kafka, S3, GCS, Azure Blob, and webhook sinks with JSON or Avro output formats.
Changefeed Configuration
Two modes exist: resolved (default) emits changes plus periodic resolved timestamps for progress tracking; immediate emits changes as committed for lower latency without range ordering guarantees. Options include envelope (wrapped/bare), format (JSON/Avro), compression (gzip/none), and resolved interval. Avro is preferred for Kafka sinks for efficient serialization. Cloud storage sinks partition output by date for efficient querying. The min_checkpoint_interval balances checkpoint overhead against recovery time.
C#
using Npgsql;
public class ChangefeedConsumerService
{
private readonly string _connectionString;
public ChangefeedConsumerService(string connectionString) { _connectionString = connectionString; }
public async Task CreateChangefeedAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(@"CREATE CHANGEFEED FOR TABLE orders, payments
INTO 'kafka://broker1:9092,broker2:9092'
WITH updated, resolved='10s', envelope=wrapped, format=avro,
topic_prefix='cockroachdb-changes';", conn);
await cmd.ExecuteNonQueryAsync();
Console.WriteLine("Changefeed created.");
}
public async Task MonitorChangefeedStatusAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(
"SELECT job_id, status, high_water_timestamp FROM [SHOW JOBS] WHERE job_type='CHANGEFEED' ORDER BY start DESC;", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
Console.WriteLine($"Job {reader.GetInt64(0)}: {reader.GetString(1)} HW={reader.GetDateTime(2):O}");
}
}
| Option | Values | Default | Description |
|---|---|---|---|
| envelope | wrapped, bare | wrapped | Include key/value/metadata |
| format | json, avro | json | Output format |
| resolved | Duration | Disabled | Resolved timestamp interval |
| compression | gzip, none | none | Cloud storage compression |
| updated | Present/absent | Absent | Include updated timestamp |
| topic_prefix | String | Table name | Kafka topic prefix |
11. Performance Tuning
Performance tuning in CockroachDB addresses query optimization, index design, zone configuration, and cluster resource management. Unlike single-node databases, distributed databases require additional considerations: data locality, range distribution, leaseholder placement, and inter-node network latency. The CBO uses table statistics (histograms, distinct counts, null fractions) to estimate plan costs. EXPLAIN (DISTSQL) shows distributed execution plans with estimated vs actual row counts. ANALYZE refreshes stale statistics. Index design must consider that indexes affect range partitioning — composite indexes should prefix the partition key for geo-partitioned tables, and partial indexes reduce size and maintenance overhead.
Index Strategies
Covering indexes (STORING clause) avoid table lookups by including all needed columns in the index. Partial indexes with WHERE clauses index only matching rows, dramatically reducing size. Inverted indexes enable efficient JSONB queries. Hash-sharded indexes distribute sequential keys evenly to prevent hotspots. The EXPLAIN ANALYZE output provides actual execution statistics including rows read, bytes processed, and execution time per operator, enabling precise bottleneck identification. CockroachDB's built-in statement statistics track p50/p90/p99 latencies, helping identify slow queries over time.
Zone configurations play a critical role in performance tuning for geo-distributed deployments. The lease_preferences setting controls which nodes are preferred for range leases, keeping reads local to a specific region. The constraints setting controls replica placement, ensuring data is stored in the correct geographic location. The gc.ttlseconds setting controls how long old MVCC versions are retained, directly impacting storage usage and the availability of historical reads. The range_min_bytes and range_max_bytes settings control when ranges split and merge, affecting the granularity of data distribution and the overhead of managing many small ranges. Tuning these configurations based on workload patterns — read-heavy vs. write-heavy, regional vs. global, small transactions vs. batch operations — is essential for optimal performance.
Query Plan Optimization
The CockroachDB optimizer supports several advanced features that significantly impact query performance. Lookup joins are preferred when one side of a join is small and the other has an index on the join key, avoiding full table scans. Inverted joins enable efficient containment queries on array and JSONB columns. Zigzag joins exploit interleaved index structures for multi-column prefix lookups. The optimizer also supports predicate pushdown, projection pruning, and join filtering to minimize data movement across the network. When the optimizer's statistics are stale, it may choose suboptimal plans — regular ANALYZE operations are critical for maintaining query performance. The statement diagnostics feature captures the actual execution plan and statistics for any query, enabling deep performance analysis without reproducing the workload.
C#
using Npgsql;
public class PerformanceTuningService
{
private readonly string _connectionString;
public PerformanceTuningService(string connectionString) { _connectionString = connectionString; }
public async Task AnalyzeQueryPerformanceAsync(string query)
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand($"EXPLAIN ANALYZE {query}", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync()) Console.WriteLine(reader.GetString(0));
}
public async Task CreateOptimalIndexesAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var c1 = new NpgsqlCommand(@"CREATE INDEX idx_orders_region_cust_date
ON orders (region, customer_id, created_at DESC) STORING (total_amount, status);", conn);
await c1.ExecuteNonQueryAsync();
await using var c2 = new NpgsqlCommand(@"CREATE INDEX idx_orders_active
ON orders (region, customer_id) WHERE status IN ('pending','processing');", conn);
await c2.ExecuteNonQueryAsync();
await using var c3 = new NpgsqlCommand(
"CREATE INVERTED INDEX idx_events_payload ON events (payload);", conn);
await c3.ExecuteNonQueryAsync();
}
public async Task GetClusterStatisticsAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(
"SELECT node_id, address, bytes_used, range_count FROM crdb_internal.gossip_nodes WHERE is_live=true;", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
Console.WriteLine($"Node {reader.GetInt64(0)}: Ranges={reader.GetInt64(3)}, Size={reader.GetInt64(2)/(1024*1024)}MB");
}
}
| Metric | Tool | Target | Action if Exceeded |
|---|---|---|---|
| Query p99 Latency | Statement Statistics | <100ms OLTP | Add indexes, optimize plan |
| Range Split Rate | Metrics | <10/s/node | Pre-split hot keys |
| Transaction Retry Rate | Txn Statistics | <1% | Reduce contention |
| Replication Lag | Range Statistics | <500ms | Check network, increase ticks |
| GC Pause Time | Pebble Metrics | <10ms | Adjust GC TTL |
| Memory Usage | Node Status | <80% allocated | Add nodes |
12. Backup and Restore
CockroachDB provides full backups, incremental backups, and scheduled recurring backups as distributed operations leveraging its own SQL engine. Backups are consistent point-in-time snapshots using MVCC timestamps without downtime or blocking. Full backups capture entire database state encoded as files in cloud storage (S3, GCS, Azure Blob) organized by key range for parallel restore. Incremental backups read only MVCC-modified key-value pairs since the last backup, making them efficient regardless of total database size. Point-in-time restore applies the full backup then incrementals sequentially until the desired timestamp, parallelized across ranges.
Encryption and Scheduling
Backups support AES-256-GCM encryption via passphrase or AWS KMS/GCP Cloud KMS keys. The key hierarchy uses a master key protecting data encryption keys, enabling efficient key rotation without re-encrypting the entire dataset. Scheduled backups use cron expressions (e.g., 0 */6 * * * for every 6 hours) with configurable failure handling (retry, skip) and concurrency control. The SHOW JOBS command monitors backup progress including bytes read/written and fraction completed. Cross-region backup storage is recommended for disaster recovery.
C#
using Npgsql;
public class BackupRestoreService
{
private readonly string _connectionString;
public BackupRestoreService(string connectionString) { _connectionString = connectionString; }
public async Task CreateFullBackupAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(@"BACKUP DATABASE myapp, analytics
INTO 's3://backup-bucket/crdb/full?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=y'
WITH detached, encryption_passphrase='secure-key', kms_key_arn='arn:aws:kms:us-east-1:123:key/id';", conn);
await cmd.ExecuteNonQueryAsync();
}
public async Task ScheduleRecurringBackupAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand(@"CREATE SCHEDULE my_backup
FOR BACKUP INTO 's3://backup-bucket/crdb/scheduled?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=y'
WITH encryption_passphrase='secure-key'
RECURRENCE BYCRON '0 */6 * * *'
WITH SCHEDULE OPTIONS first_run='now', on_execution_failure='retry';", conn);
await cmd.ExecuteNonQueryAsync();
}
public async Task RestoreToPointInTimeAsync(DateTime target)
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand($@"RESTORE DATABASE myapp FROM LATEST IN
's3://backup-bucket/crdb/full?AWS_ACCESS_KEY_ID=x&AWS_SECRET_ACCESS_KEY=y'
AS OF SYSTEM TIME '{target:O}'
WITH encryption_passphrase='secure-key', new_db_name='myapp_restored', detached;", conn);
await cmd.ExecuteNonQueryAsync();
}
}
| Feature | Options | Best Practice |
|---|---|---|
| Type | Full, Incremental, Scheduled | Weekly full + hourly incremental |
| Storage | S3, GCS, Azure, NFS | Different region than cluster |
| Encryption | Passphrase, KMS | Always use KMS |
| Retention | GC TTL configurable | 30 days minimum |
| Restore Speed | Parallel range restore | Scale restore cluster |
13. Security
CockroachDB provides enterprise-grade security across multiple layers. TLS 1.3 is enforced for all client-to-node connections, and mutual TLS (mTLS) secures node-to-node communication (Raft, gossip, DistSQL). Organizations can use custom CA certificates with internal PKI. RBAC uses SQL-standard GRANT/REVOKE with hierarchical roles — CREATE ROLE for named privilege sets, CREATE USER for authentication principals. Privileges scope to databases, tables, schemas, sequences, and types. Encryption at rest uses AES-256-GCM with KMS integration (AWS KMS, GCP Cloud KMS) through a master key / data encryption key hierarchy enabling efficient rotation without full re-encryption.
Audit and Compliance
CockroachDB logs all DDL operations and authentication events to system.eventlog. Per-table audit logging captures row-level changes for compliance. SQL audit logging records all statements executed by specific users. Network security includes IP allowlisting, VPC peering, and private connectivity via AWS PrivateLink or GCP Private Service Connect for Serverless. The HTTP admin UI (port 8080) supports its own authentication and should be restricted to internal networks.
C#
using Npgsql;
public class SecurityConfigurationService
{
private readonly string _connectionString;
public SecurityConfigurationService(string connectionString) { _connectionString = connectionString; }
public async Task CreateRolesAndUsersAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
await using var c1 = new NpgsqlCommand(@"CREATE ROLE app_readonly; CREATE ROLE app_readwrite;
CREATE ROLE app_admin; CREATE ROLE audit_reader;", conn);
await c1.ExecuteNonQueryAsync();
await using var c2 = new NpgsqlCommand(@"GRANT SELECT ON ALL TABLES IN DATABASE myapp TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN DATABASE myapp TO app_readwrite;
GRANT ALL ON DATABASE myapp TO app_admin;", conn);
await c2.ExecuteNonQueryAsync();
await using var c3 = new NpgsqlCommand(@"CREATE USER app_svc WITH PASSWORD 'secure_pass!';
CREATE USER analyst_ro WITH PASSWORD 'analyst_pass!';
GRANT app_readwrite TO app_svc; GRANT app_readonly TO analyst_ro;", conn);
await c3.ExecuteNonQueryAsync();
}
public async Task SetupTlsConnectionAsync()
{
var builder = new NpgsqlConnectionStringBuilder(_connectionString)
{ SslMode = SslMode.Require, RootCertificate = @"C:\certs\ca.crt",
ClientCertificate = @"C:\certs\client.crt", ClientKey = @"C:\certs\client.key" };
await using var conn = new NpgsqlConnection(builder.ConnectionString);
await conn.OpenAsync();
Console.WriteLine("Connected with TLS.");
}
}
| Security Feature | Implementation | Enterprise Required |
|---|---|---|
| TLS Transport | mTLS all connections TLS 1.3 | No |
| RBAC | GRANT/REVOKE hierarchical roles | No |
| Encryption at Rest | AES-256-GCM + KMS | Yes |
| Audit Logging | system.eventlog + per-table | Partial |
| IP Allowlisting | SQL IP allowlist + network policies | No |
| Private Connectivity | AWS PrivateLink / GCP PSC | Serverless only |
14. CockroachDB Serverless vs Dedicated
CockroachDB offers Serverless (fully managed, consumption-based, pay-per-use with scale-to-zero) and Dedicated (reserved compute/storage, more configuration, predictable pricing). Both run the same engine and SQL features. Serverless charges per request, GiB storage, and egress — ideal for variable workloads and development. Dedicated charges for reserved nodes and storage — better for steady-state enterprise workloads requiring guaranteed performance, compliance certifications, and full enterprise features.
Migration Planning
Starting with Serverless for prototyping and early production, then migrating to Dedicated as workloads stabilize and requirements grow, is a common pattern. Serverless supports up to 3 regions, limited changefeed options, and has SQL user limits. Dedicated offers unlimited regions, full changefeed support with all sinks, VPC peering across all clouds, configurable backup retention up to 90 days, and all enterprise features including encryption at rest. Cost comparison depends on traffic patterns: Serverless wins for bursty/low traffic, Dedicated wins for sustained high throughput.
| Feature | Serverless | Dedicated |
|---|---|---|
| Pricing | Pay-per-use | Reserved nodes+storage/hr |
| Scaling | Automatic, scale-to-zero | Manual node changes |
| Minimum Cost | $0 (free tier) | ~$500/month |
| Multi-Region | Up to 3 | Unlimited |
| Backup Retention | 30 days | Up to 90 days |
| VPC Peering | AWS/GCP | AWS/GCP/Azure |
| Changefeeds | Limited | Full support all sinks |
| Enterprise Features | Partial | Full |
| SQL User Limit | 200 | Unlimited |
15. Comparison with TiDB, YugabyteDB, Spanner
CockroachDB competes with several distributed SQL databases, each with distinct architectural choices. Google Cloud Spanner pioneered the distributed SQL space with TrueTime (atomic clocks + GPS) for externally consistent transactions, but is GCP-only and proprietary. CockroachDB is open source and cloud-agnostic, using HLC instead of TrueTime, achieving similar guarantees through algorithmic means rather than specialized hardware. TiDB from PingCAP is MySQL-compatible with a TiKV (RocksDB-based) storage layer and a separate TiFlash columnar engine for HTAP workloads. It offers weaker default isolation (snapshot) but provides better analytical query performance through its columnar engine. YugabyteDB is PostgreSQL-compatible with a docdb storage layer based on RocksDB, supporting bothYSQL (Cassandra-compatible) and YSQL (PostgreSQL-compatible) APIs, offering flexible multi-API access but with a more complex operational surface.
Architecture Comparison
All three databases use some form of Raft or Paxos consensus for replication, but differ in storage engine design, query optimization, and geo-distribution capabilities. CockroachDB's Pebble is a custom Go LSM engine optimized for its workload. TiDB's TiKV uses Rust-based RocksDB with Raft groups per region. YugabyteDB's DocDB is a modified RocksDB with hash/range sharding. Spanner uses Colossus (Google's distributed file system) with B-tree-like storage. For geo-distribution, CockroachDB offers the finest granularity with row-level geo-partitioning, while Spanner uses database-level placement and TiDB/YugabyteDB offer zone-level configurations.
The choice between these databases depends on several factors. If you need MySQL compatibility and HTAP (hybrid transactional-analytical processing), TiDB is the strongest option due to its native TiFlash columnar engine. If you need both PostgreSQL and Cassandra (YSQL and YQL) compatibility, YugabyteDB provides the most flexibility. If you need the strongest consistency guarantees with Google's infrastructure and are comfortable with GCP lock-in, Spanner offers unmatched consistency through TrueTime. If you need cloud-agnostic deployment, row-level geo-partitioning, and serializable isolation with open-source licensing, CockroachDB is the optimal choice. CockroachDB also has the most mature online schema change implementation, supporting all DDL operations non-blocking, while TiDB and YugabyteDB have more limited DDL support in certain scenarios.
Cost considerations also differ significantly. Spanner charges per node-hour and storage, with no free tier and significant cross-region replication costs. TiDB and YugabyteDB can be self-hosted on any infrastructure, with costs depending on your operational capabilities. CockroachDB Serverless provides a generous free tier for development and small production workloads, while Dedicated offers predictable pricing for enterprise deployments. For large-scale deployments (100+ nodes), self-hosted TiDB or YugabyteDB may be more cost-effective than managed CockroachDB or Spanner, but require dedicated database operations expertise. The total cost of ownership should factor in not just infrastructure costs but also engineering time for schema design, performance tuning, operational monitoring, and incident response.
| Feature | CockroachDB | TiDB | YugabyteDB | Cloud Spanner |
|---|---|---|---|---|
| SQL Compatibility | PostgreSQL | MySQL | PostgreSQL + Cassandra | Google SQL + PostgreSQL |
| Default Isolation | Serializable | Snapshot | Serializable | External Consistency |
| Storage Engine | Pebble (Go) | TiKV/TiFlash (Rust) | DocDB (C++) | Colossus (proprietary) |
| Geo-Partitioning | Row-level | Zone-level | Table/Partition-level | Database-level |
| Open Source | Yes (Apache 2.0) | Yes (Apache 2.0) | Yes (Apache 2.0) | No (proprietary) |
| Cloud | AWS/GCP/Azure/self-hosted | Self-hosted/Cloud | AWS/GCP/self-hosted | GCP only |
| HTAP Support | Via follower reads | Native (TiFlash) | Via follower reads | Via read replicas |
| Online DDL | All operations | Most operations | Most operations | Most operations |
16. Migration from PostgreSQL / MySQL
Migrating to CockroachDB from PostgreSQL or MySQL is streamlined by its PostgreSQL wire protocol compatibility. PostgreSQL migrations are typically straightforward — most ORMs (Entity Framework, Dapper, Npgsql) and tools (pg_dump, pgAdmin) work with CockroachDB with minimal changes. MySQL migrations require more effort due to SQL syntax differences, but CockroachDB's MySQL compatibility layer handles most common patterns. The migration process typically involves schema migration (DDL translation), data migration (bulk import via IMPORT or COPY), application code updates (driver/connection string changes), and validation (data integrity checks and performance benchmarking).
PostgreSQL Migration Checklist
Key considerations include: CockroachDB does not support stored procedures, functions, or triggers (use application logic instead); SERIAL columns should use UUID or unique_rowid() for better distribution; sequence behavior differs slightly (global sequences vs per-node); some PostgreSQL extensions are not supported; and JSONB operations have minor syntax differences. The crdb_internal tables provide migration validation queries. For large datasets, useIMPORT INTO with CSV or Parquet files for bulk loading, which is significantly faster than INSERT statements.
The migration process should be carefully planned and executed in phases. Phase 1 involves schema compatibility analysis using the SQL Schema Converter tool to identify incompatible SQL constructs. Phase 2 sets up the CockroachDB cluster with appropriate topology and creates the schema with optimized data types. Phase 3 performs the initial data load using bulk import tools processing millions of rows per second. Phase 4 establishes continuous replication from PostgreSQL using logical replication or CDC tools. Phase 5 runs parallel testing comparing both databases. Phase 6 performs the cutover, switching reads then writes to CockroachDB. Phase 7 decommissions PostgreSQL after 2-4 weeks of monitoring. MySQL migrations require additional attention: AUTO_INCREMENT must be replaced with UUID or unique_rowid(), backtick quoting converted to double-quote quoting, and implicit type coercion explicitly handled. CockroachDB is stricter about type matching than MySQL, and case-sensitive by default unlike MySQL's default case-insensitive collation. Testing the full query surface area against CockroachDB before migration is essential.
C#
using Npgsql;
public class MigrationService
{
private readonly string _connectionString;
public MigrationService(string connectionString) { _connectionString = connectionString; }
public async Task MigrateSchemaFromPostgresAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
// Use UUID instead of SERIAL for better distribution
await using var cmd = new NpgsqlCommand(@"CREATE TABLE IF NOT EXISTS users (
id UUID NOT NULL DEFAULT gen_random_uuid(),
email STRING NOT NULL UNIQUE,
name STRING NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT pk_users PRIMARY KEY (id)
) LOCALITY REGIONAL BY ROW;", conn);
await cmd.ExecuteNonQueryAsync();
}
public async Task ValidateMigrationAsync()
{
await using var conn = new NpgsqlConnection(_connectionString);
await conn.OpenAsync();
// Check table row counts match
await using var cmd = new NpgsqlCommand(
"SELECT table_name, estimated_row_count FROM crdb_internal.tables WHERE database_name='myapp';", conn);
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
Console.WriteLine($"{reader.GetString(0)}: ~{reader.GetInt64(1)} rows");
}
}
| Migration Aspect | PostgreSQL to CRDB | MySQL to CRDB |
|---|---|---|
| Driver | Npgsql (works as-is) | Change to Npgsql or MySQL adapter |
| Schema | Mostly compatible, minor adjustments | Significant syntax changes |
| Data Types | SERIAL to UUID, minor type mapping | AUTO_INCREMENT to UUID, type mapping |
| Stored Procedures | Not supported, move to app logic | Not supported, move to app logic |
| Triggers | Not supported, use changefeeds | Not supported, use changefeeds |
| Sequences | Global sequences, use unique_rowid() | Use UUID or unique_rowid() |
| Bulk Import | IMPORT with CSV/Parquet | IMPORT with CSV/Parquet |
| ORM Support | Entity Framework, Dapper work | Switch ORM to Npgsql-compatible |
17. Interview Q&A
Q1: How does CockroachDB achieve serializable isolation without atomic clocks?
Google Spanner uses TrueTime (atomic clocks + GPS receivers) for externally consistent timestamps. CockroachDB achieves equivalent serializable isolation using Hybrid Logical Clocks (HLC), which combine physical wall clock time with a logical counter. The HLC ensures causal ordering across nodes and provides approximately synchronized timestamps. When clock skew is detected (beyond a configurable uncertainty interval), CockroachDB resolves uncertainty by buffering reads and pushing transaction timestamps. While TrueTime provides tighter timestamp bounds, HLC-based serializable isolation is sufficient for most workloads and doesn't require specialized hardware.
Q2: Explain the difference between a range lease and Raft leadership.
Raft leadership handles log replication and ordering — the leader sequences writes and replicates entries to followers. The range lease is a CockroachDB-specific concept layered on top of Raft, providing a single point for serving reads. The leaseholder can serve consistent reads without going through the full Raft protocol (no proposal overhead). Typically the leaseholder and Raft leader are the same node, but they can differ. This separation allows CockroachDB to serve reads efficiently while maintaining write consistency through Raft.
Q3: How does CockroachDB handle hotspots caused by sequential key writes?
Sequential keys (e.g., auto-incrementing IDs) concentrate writes on a single range, creating hotspots. CockroachDB mitigates this through: (1) Hash-sharded indexes that distribute sequential keys across multiple ranges; (2) UUID primary keys with gen_random_uuid() for random distribution; (3) Range splits triggered by the split controller when a range becomes too hot; (4) Automatic range rebalancing that moves hot ranges to less loaded nodes. For applications that must use sequential keys, hash-sharding or composite keys with a random prefix are the recommended patterns.
Q4: Describe the two-phase commit process for a cross-range transaction.
In Phase 1 (Prepare), the transaction coordinator collects write intents from all participating ranges and records their participation in the transaction record. Each range stages its writes, creating intents visible only to the writing transaction. In Phase 2 (Commit), if all ranges staged successfully, the coordinator updates the transaction record to COMMITTED, and all intents are resolved asynchronously to committed values. If any range fails to stage, the coordinator marks the transaction ABORTED and cleans up all intents. The transaction record, colocated with the first written key, serves as the single source of truth — even if the coordinator crashes, other nodes can check it to resolve or clean up intents.
Q5: How do you design a schema for a multi-tenant SaaS application on CockroachDB?
The recommended pattern uses row-level geo-partitioning with tenant_id as part of the primary key. Create a tenants table with tenant_id, region, and configuration. All application tables (orders, payments, etc.) include tenant_id and region as the primary key prefix. Use LOCALITY REGIONAL BY ROW AS region to pin each tenant's data to their region. This ensures tenant-local queries are served from the nearest region, cross-tenant queries are isolated, and data residency requirements are met. Use partial indexes for tenant-specific queries and zone configurations for per-tenant performance tuning.
Q6: What happens when a CockroachDB node fails during a write transaction?
If the failed node holds the Raft leader for a range involved in the transaction, the remaining replicas detect the leader absence after the election timeout, elect a new leader, and continue. If the failed node held the leaseholder, a new leaseholder is elected from the surviving replicas. The in-flight transaction's intents are cleaned up through the transaction record: if the transaction was already committed, the new leader resolves intents to committed values; if it was still pending, the intents are cleaned up as aborted. The application sees either a successful commit or a retryable error (code 40001), which it should handle with retry logic.
Q7: Explain follower reads and their consistency guarantees.
Follower reads serve reads from follower replicas without going to the leaseholder, reducing latency for geo-distributed reads. They use AS OF SYSTEM TIME with a timestamp in the past (e.g., '-10s'), guaranteeing that the follower has applied all committed entries up to that timestamp. The staleness is bounded and predictable — typically 10 seconds or more. Follower reads are ideal for analytics dashboards, reporting queries, and any workload that tolerates slightly stale data. They reduce load on the leaseholder and provide region-local read latency. The tradeoff is eventual consistency within the staleness bound.
Q8: How would you migrate a 5TB PostgreSQL database to CockroachDB with zero downtime?
The recommended approach uses a dual-write pattern: (1) Set up CockroachDB cluster with equivalent schema using UUID keys and REGIONAL BY ROW locality; (2) Use pg_dump + IMPORT for initial bulk data load; (3) Enable change data capture on PostgreSQL (logical replication) to stream ongoing changes; (4) Build a change processor that transforms PostgreSQL CDC events into CockroachDB-compatible writes; (5) Run validation queries comparing both databases; (6) Once validated, switch application reads to CockroachDB; (7) Switch writes; (8) Decommission PostgreSQL after a safety period. The entire process typically takes weeks for a 5TB database, with the bulk load taking 1-2 days and validation taking several days.
Q9: Compare CockroachDB's approach to Google Spanner's TrueTime.
TrueTime provides externally consistent timestamps using atomic clocks and GPS, giving tight bounds on clock uncertainty (typically <7ms). CockroachDB's HLC uses standard NTP-synchronized clocks with a logical counter, providing causal ordering without specialized hardware. Spanner's advantage is tighter timestamp bounds, reducing the need for uncertainty-driven transaction restarts. CockroachDB compensates with a larger uncertainty interval and retry budget. For most workloads, the performance difference is negligible. Spanner requires GCP infrastructure; CockroachDB runs anywhere. Both achieve serializable isolation — the mechanism differs but the guarantee is equivalent.
Q10: Design a real-time analytics pipeline using CockroachDB changefeeds.
The architecture uses CockroachDB as the OLTP source, changefeeds streaming to Kafka, stream processing (Flink/Spark) for aggregation, and a serving layer (ClickHouse/BigQuery) for analytics. CockroachDB changefeeds emit row-level changes with resolved timestamps, enabling exactly-once processing in the stream processor. The processor maintains running aggregates (revenue per region, user activity metrics) and sinks to the analytics store. Resolved timestamps serve as watermarks, ensuring no data is missed. For sub-second analytics, use the immediate changefeed mode; for guaranteed ordering, use resolved mode with a 10-second interval. This architecture separates OLTP and OLAP workloads while maintaining real-time data flow.