Database / CockroachDB Interview questions

CockroachDB is a distributed SQL database built to scale horizontally and keep running when machines, zones, or even whole regions fail. You talk to it with standard SQL over the PostgreSQL wire protocol, but underneath, data is split into pieces and replicated across many nodes.

It gives you ACID transactions with serializable isolation by default, so you do not have to trade consistency for scale the way many NoSQL stores force you to.

Typical uses are financial systems, order management, SaaS back ends, and anything that needs a relational model with strong correctness and multi-region reach.

The headline features are scale, survivability, and strong consistency, all behind a familiar SQL interface.

• Horizontal scaling: add nodes and data rebalances automatically.
• Replication and self-healing: three copies of every range by default, using Raft.
• Serializable ACID transactions across nodes.
• PostgreSQL compatibility for drivers and ORMs.
• Multi-region support with locality-aware tables and survival goals.
• Online schema changes without blocking reads or writes.
• Change data capture through changefeeds.

In interviews, tie each feature to a problem it solves: Raft replication answers node failure, range splitting answers growth, and serializable isolation answers correctness under concurrency.

Largely yes. CockroachDB speaks the PostgreSQL wire protocol and supports most of the PostgreSQL SQL dialect, so drivers such as pgx, psycopg, and the PostgreSQL JDBC driver, plus many ORMs, work with small or no changes.

It is not a drop-in clone though. Some PostgreSQL features and extensions are missing or behave differently, and the default isolation level is SERIALIZABLE, which means your application must be ready to retry transactions that hit conflicts.

Always check the docs for your version before migrating a feature-heavy PostgreSQL schema.

A cluster is made of nodes, each running the same cockroach binary. Every node holds one or more stores (disks) and can serve SQL.

Data is divided into ranges, and each range has several replicas placed on different nodes. One replica holds the lease (the leaseholder) and coordinates reads and writes for that range.

The node a client connects to is the gateway node. Nodes also share cluster state using a gossip protocol.

The five terms to remember: node, range, replica, leaseholder, gateway.

A range is a contiguous slice of the sorted key-value space that stores table and index data. CockroachDB starts with few ranges and splits a range in two once it grows past the maximum size, which defaults to 512 MiB.

Each range is replicated, three times by default, and the replicas form their own Raft group. That is what makes ranges the unit of replication, rebalancing, and failover.

You can inspect them with:

SHOW RANGES FROM TABLE orders;

Ranges also split under heavy traffic (load-based splitting) and merge when they shrink, so the range count tracks both data size and load.

The leaseholder is the replica of a range that holds the range lease. It serves all reads for the range and proposes writes through Raft, so reads do not need a quorum round trip.

The lease is time-bound and can move to another replica when a node fails or when the system wants it closer to the traffic. The leaseholder is usually, but not always, the same replica as the Raft leader.

When you hear "hot leaseholder", it means one node is serving a disproportionate share of a range's traffic.

For quick experiments, a single insecure node is enough:

cockroach start-single-node --insecure --listen-addr=localhost:26257

For a real multi-node cluster, start each node with --join pointing at the others, then initialize once:

cockroach start --insecure --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259
cockroach init --insecure --host=localhost:26257

Production clusters should use TLS certificates instead of --insecure. Also, cockroach demo spins up a temporary in-memory cluster with a SQL shell.

After starting, connect with cockroach sql --insecure and open the DB Console at http://localhost:8080 to confirm all nodes are live.

The default is SERIALIZABLE, the strongest standard SQL isolation level. Concurrent transactions behave as if they ran one at a time, which rules out anomalies like write skew.

The price is that conflicting transactions may be aborted with a retryable error (SQLSTATE 40001), so clients must retry.

Newer versions also offer READ COMMITTED when enabled through a cluster setting, which cuts retries for workloads that can tolerate weaker guarantees.

This default is stricter than PostgreSQL, which uses READ COMMITTED. It is a common point of surprise when porting applications.

The default is 3, so every range is stored on three nodes. A range stays available as long as a majority, two of the three, can talk to each other.

You can change it per database, table, or index through zone configurations:

ALTER TABLE payments CONFIGURE ZONE USING num_replicas = 5;

Five replicas tolerate two simultaneous failures, at the cost of more storage and slightly more write work.

Odd numbers are the sensible choice. Four replicas still need three for quorum, so they tolerate one failure, the same as three.

MVCC (multi-version concurrency control) means CockroachDB never overwrites a value in place. Each write adds a new version tagged with a timestamp, and readers see the version valid at their own timestamp.

The effect is that reads do not block writes and writes do not block reads. It also powers AS OF SYSTEM TIME queries and consistent backups.

Old versions pile up, so a background garbage collector removes versions older than the gc.ttlseconds setting, 4 hours by default.

Deletes work the same way: a delete writes a tombstone version, and the old data stays readable by older timestamps until garbage collection removes it.

The syntax is standard SQL. A good habit is to pick a primary key that spreads writes, such as a UUID:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    INDEX idx_customer (customer_id)
);

Every table has a primary key. If you do not declare one, CockroachDB adds a hidden rowid column and uses it.

You can add secondary indexes inline as shown, or later with CREATE INDEX. Check the result with SHOW CREATE TABLE orders;.

A zone configuration controls how ranges are stored: replica count, placement constraints, lease preferences, range size limits, and the GC TTL. You can set one for the cluster, a database, a table, an index, or a partition, and the most specific one wins.

ALTER TABLE audit_log CONFIGURE ZONE USING
    num_replicas = 5,
    gc.ttlseconds = 86400,
    constraints = '[+region=us-east1]';

In multi-region databases, many of these settings are managed for you through high-level SQL, so hand-written zone configs are usually reserved for special cases.

AS OF SYSTEM TIME is a clause that runs a read at a timestamp in the past. Since CockroachDB keeps old MVCC versions, it can show the data as it looked then, as long as that time is within the GC TTL.

SELECT * FROM orders AS OF SYSTEM TIME '-10m' WHERE customer_id = $1;

It is useful for undoing mistakes, auditing, long reports that should not block writers, and follower reads, where slightly stale data is read from the nearest replica for lower latency.

A changefeed streams row-level changes from tables to an external sink, so downstream systems can react to inserts, updates, and deletes without polling. It is CockroachDB's change data capture (CDC) feature.

CREATE CHANGEFEED FOR TABLE orders
INTO 'kafka://broker:9092'
WITH updated, resolved = '10s';

Supported sinks include Kafka, webhooks, cloud storage, and Google Pub/Sub. The resolved option emits timestamps that tell consumers everything before that time has been delivered.

Changefeeds deliver changes at least once, so consumers should be idempotent. They also resume from a checkpoint after node failures instead of starting over.

CockroachDB supports several index types, each with a clear job:

• Primary index: stores the table rows themselves, ordered by primary key.
• Secondary index: speeds lookups on other columns; can be UNIQUE.
• Covering index: uses STORING to include extra columns and avoid an index join.
• Partial index: indexes only rows matching a WHERE predicate.
• Expression index: indexes the result of an expression such as lower(email).
• Inverted index: for JSONB, arrays, and spatial data.
• Hash-sharded index: spreads sequential keys to avoid hotspots.

High availability comes from replication plus consensus. Every range has multiple replicas on different nodes, and Raft lets the survivors elect a new leader and keep serving as long as a majority is reachable.

When a node fails, its leases expire and move to surviving replicas within seconds. If the node stays gone for about five minutes by default, the cluster re-creates its replicas elsewhere to restore the replication factor.

You choose the failure domain you want to survive with locality flags and survival goals: node, zone, or region.

Each range is its own Raft group. One replica is the leader and receives proposed writes. It appends them to its log, sends them to followers, and once a majority has persisted the entry, the write is committed and applied to the storage engine.

If the leader becomes unreachable, followers hold an election, and a replica with an up-to-date log wins. Terms prevent two leaders from acting at once.

sequenceDiagram
    participant L as Leader replica
    participant F1 as Follower 1
    participant F2 as Follower 2
    L->>F1: AppendEntries (write)
    L->>F2: AppendEntries (write)
    F1-->>L: Ack
    Note over L: Majority reached, commit
    L-->>F2: Commit index update

Because thousands of ranges run independent Raft groups, the load spreads across the cluster rather than funnelling through one leader.

CockroachDB is built as a stack of layers, each with one responsibility:

1. SQL layer: parses and optimizes SQL, then turns it into key-value operations.
2. Transactional layer: provides ACID guarantees using MVCC, write intents, and timestamps.
3. Distribution layer: maps keys to ranges and routes requests to the right leaseholder.
4. Replication layer: keeps replicas in sync through Raft.
5. Storage layer: persists data on disk using the Pebble LSM engine.

flowchart TD
    A["SQL layer"] --> B["Transactional layer"]
    B --> C["Distribution layer"]
    C --> D["Replication layer - Raft"]
    D --> E["Storage layer - Pebble"]

The key idea is that tables become sorted key-value pairs, and everything below the SQL layer only sees keys and values.

Take INSERT INTO orders ... sent to some gateway node. The gateway plans the statement and converts the row into key-value writes. It then sends the write to the leaseholder of the target range.

The leaseholder checks for conflicts, assigns a timestamp, writes a provisional write intent, and proposes the change through Raft. Once a majority of replicas have it in their log, it is durable.

On commit, intents are resolved into regular committed values.

sequenceDiagram
    participant C as Client
    participant G as Gateway
    participant LH as Leaseholder
    participant R as Other replicas
    C->>G: INSERT
    G->>LH: KV write batch
    LH->>R: Raft proposal
    R-->>LH: Majority ack
    LH-->>G: Write done
    G-->>C: Success

If the data spans several ranges, each range follows the same path and the transaction protocol ties them together.

The client sends a SELECT to a gateway node. The gateway parses it, the cost-based optimizer picks a plan using table statistics, and the plan is turned into key-value reads.

Requests go to the leaseholders of the ranges involved. Each leaseholder reads from Pebble at the transaction's timestamp, checks for conflicting write intents, and handles the uncertainty interval caused by possible clock offset.

flowchart LR
    A[Client] --> B["Gateway: parse and optimize"]
    B --> C["Leaseholders of needed ranges"]
    C --> D["Pebble read at timestamp"]
    D --> B
    B --> A

For large scans or aggregations, DistSQL pushes filtering and partial aggregation to the nodes holding the data so less crosses the network.

It uses an MVCC-based protocol built on write intents and a transaction record. As a transaction writes, each change is stored as a provisional intent pointing at the transaction record, which lives on the range of the first write.

On commit, the record is marked committed. After that, the intents are resolved into normal values, often asynchronously. Any reader or writer that runs into an intent checks the record to learn whether to wait, push, or ignore it.

No global lock manager or central coordinator is involved, which is why this scales across ranges and regions.

Isolation comes from timestamps. If a transaction's timestamp has to be pushed forward, it first tries a read refresh and only restarts if the refresh finds a newer conflicting write.

Parallel commits is an optimization that cuts commit latency from two consensus round trips to roughly one. The transaction record is first written in a STAGING state at the same time as the final batch of writes, instead of waiting for those writes to finish.

The transaction is implicitly committed as soon as all its writes succeed, so the client can be told "committed" right away. The record is flipped to COMMITTED afterwards, in the background.

If the coordinator dies mid-way, other transactions can check whether all intents landed and finish the job.

The trade-off is a little more bookkeeping during recovery, but the cost is paid only when a coordinator fails, while every commit gets the faster path.

Both speak SQL and the PostgreSQL wire protocol, but their designs target different problems.

Pick PostgreSQL for a single-node, extension-heavy workload. Pick CockroachDB when you need scale-out writes, automatic failover, or multi-region survival.

Migration effort is mostly in the details: retry logic, key design, and any extension or stored-procedure features your schema depends on.

Both are globally distributed SQL databases inspired by similar ideas, but they differ in how they get time and where they run.

The deployment portability of CockroachDB is often the deciding factor, while Spanner's TrueTime gives it tighter timing guarantees.

Neither is simply better. Teams already committed to Google Cloud may prefer Spanner, while multi-cloud or on-prem needs point toward CockroachDB.

A hybrid logical clock (HLC) combines a physical wall-clock time with a logical counter. It gives timestamps that are close to real time, but still preserve causality: if event A caused B, B always gets a larger timestamp.

That matters because CockroachDB cannot assume perfectly synced clocks across nodes. HLCs let nodes adjust when they see a message with a later timestamp, and the transaction layer handles the small remaining uncertainty with uncertainty intervals and read refreshes.

The result is ordered MVCC versions without specialized time hardware.

When a read encounters a value with a timestamp inside its uncertainty window, it cannot tell if that write happened before or after, so the transaction restarts at a later timestamp to stay correct.

The default maximum clock offset is 500 ms. Each node compares its clock with its peers, and if it detects an offset of more than 80% of that limit (400 ms) against at least half of the other nodes, it shuts itself down.

This is deliberate. Running with badly drifted clocks could break the consistency guarantees, so the node removes itself instead of risking stale or out-of-order reads.

The fix is to keep clocks synchronized with NTP or a cloud time service such as chrony, and to monitor offset in the DB Console.

On cloud VMs, prefer the provider's built-in time source, which typically stays far inside the limit, and alert well before offsets approach 400 ms.

Error 40001 (restart transaction) means CockroachDB could not keep a transaction serializable, usually due to a read-write conflict with another transaction or because its timestamp had to move forward.

It is not a bug. It is an instruction to retry the whole transaction. Simple single statements are retried automatically, but multi-statement transactions need client logic.

for attempt in range(max_retries):
    try:
        with conn.cursor() as cur:
            cur.execute("BEGIN")
            # ... statements ...
            cur.execute("COMMIT")
        break
    except SerializationFailure:   # SQLSTATE 40001
        conn.rollback()
        time.sleep(backoff(attempt))

Keep transactions short, avoid user think-time inside them, and use SELECT ... FOR UPDATE on hot rows to reduce conflicts.

Data is stored sorted by key, so a steadily increasing key makes every new insert land on the last range. That range's leaseholder becomes a hotspot, while the rest of the cluster sits idle, and throughput stops scaling with node count.

Better choices are random UUID keys, composite keys that start with a well-distributed column (such as tenant or customer ID), or a hash-sharded primary key when you must keep time-ordered access.

Timestamp-leading indexes cause the same trouble and often need USING HASH.

Symptoms include one node with high CPU while others idle, and write latency that stays flat when you add nodes. The Hot Ranges page makes this easy to spot.

The two behave differently under load, even though both are unique.

Use UUID for write-heavy distributed tables. If you need small numeric IDs, SERIAL is acceptable at modest write rates, or use a cached sequence for lower per-ID cost.

If you need ordering by creation time, store a separate timestamp column and index it, preferably with hash sharding, rather than relying on the key.

A hash-sharded index adds a hidden shard column computed from a hash of the indexed columns, and puts it first in the index key. Rows with adjacent values, such as consecutive timestamps, end up in different buckets and therefore different ranges.

CREATE INDEX idx_events_ts ON events (created_at) USING HASH;

CREATE TABLE logs (
    ts TIMESTAMPTZ NOT NULL,
    msg STRING,
    PRIMARY KEY (ts) USING HASH
);

The trade-off is that range scans over the original column now fan out across buckets, so use it when write throughput matters more than ordered scans.

The bucket count is configurable. More buckets spread writes further but make ordered range scans touch more shards, so size it to your write rate rather than maximizing it.

Start by confirming it. In the DB Console, open the Hot Ranges page to see which ranges, and which nodes, have the highest queries per second or CPU use.

1. Map the range to a table or index with SHOW RANGES FROM TABLE t WITH DETAILS;
2. Check the access pattern: sequential keys, a single popular row, or a timestamp-leading index.
3. Fix the cause: switch to UUIDs, add USING HASH, reorder composite key columns, or cache hot reads in the application.
4. For a known one-time load, pre-split with ALTER TABLE ... SPLIT AT.

Load-based splitting already divides busy ranges, but it cannot help when the load targets a single key.

Work from evidence, not guesses:

1. Find the culprit in SQL Activity > Statements, sorted by latency or total time.
2. Run EXPLAIN ANALYZE to see actual rows, time per operator, and network hops.
3. Look for full table scans, index joins that could be covered, and large sorts.
4. Add or adjust indexes, using STORING where it removes an index join.
5. Check statistics are fresh. Stale stats lead to bad plans; CREATE STATISTICS refreshes them.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = '...';

Also check the Insights page, which flags slow executions and suggests indexes.

Read the plan from the bottom up. Check estimated versus actual row counts, since a large gap usually points to bad statistics.

Contention appears as slow transactions, frequent 40001 retries, or latency that grows with concurrency on a few rows.

1. Open the Insights page and check for high-contention events, which name the blocking and waiting statements.
2. Query crdb_internal.transaction_contention_events to find the contended table, index, and key.
3. Shorten transactions and remove work that does not need to be inside them.
4. Use SELECT ... FOR UPDATE to take locks early on rows you will modify.
5. Redesign the hot spot: split a counter row into several rows, or use append-only inserts.

Confirm the fix by watching retry counts and p99 latency afterwards. If they do not drop, contention is probably elsewhere.

Use follower reads when slightly stale data is acceptable and you want low latency. Normally reads go to the leaseholder, which may be in a distant region. A follower read lets the nearest replica answer from a timestamp old enough to be safe.

SELECT * FROM products
AS OF SYSTEM TIME follower_read_timestamp()
WHERE id = $1;

The timestamp is roughly 4.8 seconds in the past by default. For a staleness bound instead of a fixed offset, use with_max_staleness('10s').

Good fits are catalogs, dashboards, and reference data. Avoid them for read-your-own-write flows such as checking a balance right after a payment.

You declare regions on the database, and CockroachDB places data accordingly.

ALTER DATABASE app PRIMARY REGION "us-east1";
ALTER DATABASE app ADD REGION "europe-west1";
ALTER DATABASE app ADD REGION "asia-southeast1";
ALTER DATABASE app SURVIVE REGION FAILURE;

Nodes must be started with a matching --locality flag, such as region=us-east1,zone=a. Tables then get a locality: REGIONAL BY TABLE, REGIONAL BY ROW, or GLOBAL. The system adjusts replica and lease placement for you, so local users get low latency and the chosen survival goal is respected.

Once regions are defined, you can inspect placement with SHOW REGIONS FROM DATABASE app; and verify survival settings with SHOW SURVIVAL GOAL FROM DATABASE app;.

A survival goal states which failure the database must tolerate without losing availability.

Choose region survival for systems that cannot go down with a regional outage, accepting the cost of cross-region round trips on writes. Zone survival is cheaper and faster for most apps.

Region survival typically places replicas so that a majority sits outside any single region, which is why writes need to cross a region boundary and take longer.

They solve opposite problems.

A regional-by-row table uses a hidden crdb_region column to decide each row's home. GLOBAL tables suit configuration, currency rates, and lookup tables that are read much more than written.

You can mix both in one database. A common layout is regional-by-row for users and orders, and GLOBAL for countries, plans, and feature flags.

Rebalancing is driven by three background mechanisms working together:

1. Splits: a range larger than the max size, or one under heavy load, is split in two.
2. Merges: small adjacent ranges are merged to avoid too many tiny ranges.
3. Replica and lease rebalancing: an allocator moves replicas and leases toward balanced disk use, replica counts, and query load, while honoring zone constraints.

When you add a node, replicas start moving to it gradually, throttled to protect foreground traffic. You do not run a manual reshard.

You can watch progress through the replica and range graphs in the DB Console. A drop in under-replicated ranges to zero means the cluster is settled.

Clients on that node see connection errors and reconnect, usually through a load balancer, to another node. For ranges where the failed node held the lease, the lease expires and another replica takes over within seconds, as long as a Raft majority remains.

Reads and writes then continue. If the node does not come back within roughly 5 minutes (server.time_until_store_dead), the cluster considers it dead and copies its replicas to other nodes to restore the replication factor.

If the node returns sooner, it simply catches up via Raft logs or snapshots.

Applications should use connection pools with retry on connect errors and a load balancer health check on /health?ready=1, so traffic avoids a node that is down or draining.

Adding is simple: start a new node with --join pointing at existing nodes and the right --locality. Replicas flow to it automatically.

Removing should be done by decommissioning, not just stopping the process:

cockroach node decommission 4 --certs-dir=certs --host=node1:26257
cockroach node status --decommission --certs-dir=certs --host=node1:26257

Decommissioning moves all replicas off the node first. Wait until its replica count reaches zero, then stop it. Simply killing a node works too but leaves ranges under-replicated until re-replication completes, which raises risk.

Make sure the remaining nodes have enough disk and CPU to absorb the moved replicas. Decommissioning below the replication factor will stall.

A rolling upgrade replaces the binary on one node at a time so the cluster stays online.

1. Optionally set cluster.preserve_downgrade_option to the current version, so you can roll back if needed.
2. For each node: cockroach node drain, stop it, replace the binary, restart it, and wait for it to rejoin and be healthy.
3. Once every node runs the new version, finalize: reset the setting or let auto-finalization run.

RESET CLUSTER SETTING cluster.preserve_downgrade_option;

Until finalization, the cluster still operates in the old feature set, which is what makes rollback possible.

Upgrade one major version at a time and pause between nodes. Draining first lets in-flight queries finish and leases move away cleanly.

Use the built-in BACKUP and RESTORE statements, writing to cloud storage such as S3, GCS, or Azure Blob.

BACKUP INTO 's3://bucket/crdb?AUTH=implicit'
  AS OF SYSTEM TIME '-10s' WITH revision_history;

RESTORE DATABASE app FROM LATEST IN 's3://bucket/crdb?AUTH=implicit';

CREATE SCHEDULE daily_backup FOR BACKUP INTO 's3://bucket/crdb?AUTH=implicit'
  RECURRING '@daily' FULL BACKUP '@weekly';

Backups are distributed, so every node writes its own part. Using revision_history lets you restore to a point in time within the retained window. Schedules combine periodic full backups with incrementals.

A backup you have never restored is only a hope. Schedule regular restore tests into a scratch cluster and check row counts.

Plan it in three stages.

1. Convert the schema. Review types, sequences, extensions, and unsupported features. Cockroach Labs' MOLT tooling can assess and convert schemas.
2. Load the data. Use IMPORT INTO, COPY, or MOLT Fetch for bulk loads, and verify counts and checksums afterwards.
3. Cut over. Optionally keep systems in sync with replication, test application behavior, then switch traffic.

Before cutover, add retry logic for 40001 errors and replace sequential keys with distribution-friendly ones. These two items cause most migration surprises.

Run a trial migration into a staging cluster first, replay representative traffic, and compare query plans and latencies before committing to a date.

Choose STORING when a query filters on one set of columns but also reads a few others. Without it, CockroachDB finds rows in the secondary index and then does an index join back to the primary index for the extra columns, which is an additional lookup that can be on another node.

CREATE INDEX idx_orders_customer
  ON orders (customer_id) STORING (total, created_at);

The index now covers the query. Reads get faster, but writes cost more and storage grows because those columns are duplicated. Add only the columns that hot queries actually read.

Check EXPLAIN afterwards. If the plan no longer shows an index join for that query, the index is covering and doing its job.

Schema changes run as background jobs that move through intermediate states instead of locking the table. For example, adding an index goes through states like delete-only, write-only, and backfill, then becomes public when it is complete and consistent.

At every step, other nodes may be on a version of the schema that differs by at most one state, which keeps concurrent reads and writes correct.

You can watch progress with SHOW JOBS or the Jobs page. Large backfills are throttled to protect traffic. One caution: mixing several schema changes with other statements inside one explicit transaction has limits, so run them separately where possible.

Security has several layers, all of which should be on in production.

1. TLS everywhere: create a CA and node/client certificates (cockroach cert) and start nodes without --insecure.
2. Authentication: use passwords, client certificates, or SSO/JWT where supported.
3. Authorization: create roles and grant least-privilege access with GRANT.
4. Network controls: restrict the SQL and HTTP ports using firewalls and private networking.
5. Encryption at rest and audit logging for compliance needs.

CREATE ROLE app_rw;
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_rw;

Never run production with --insecure; it disables both encryption and authentication.

Rotate certificates before they expire and monitor expiry dates, as an expired node certificate will stop nodes from talking to each other.

The optimizer takes the parsed query and generates many logically equivalent plans, such as different join orders, join algorithms, and index choices. It estimates the cost of each from table statistics (row counts, distinct values, histograms) and picks the cheapest.

Locality matters too: in multi-region clusters, the cost model accounts for cross-region latency, so it prefers plans that read local data.

Statistics are collected automatically, but after large loads you can refresh them with CREATE STATISTICS. If a plan looks wrong, EXPLAIN shows it, and index hints can serve as a last resort.

Plans are cached for prepared statements, but they are re-optimized when statistics, schema, or placeholders change enough to matter.

Combine the built-in tools with an external stack.

• DB Console: node liveness, replica counts, SQL latency, and capacity.
• Prometheus and Grafana: scrape /_status/vars from each node and build alerts.
• Logs and events: use structured logging and send them to your log platform.
• Health endpoints: /health and /health?ready=1 for load balancer checks.

The signals worth alerting on are node liveness, under-replicated ranges, clock offset, disk usage, and SQL p99 latency.

Alert on trends too: a steady rise in disk use or p99 latency is a capacity warning long before it becomes an outage. Keep dashboards per node and per region.

Review the Insights and Statements pages regularly, not just during incidents, to catch regressions after a deploy.

No database fits every job, and CockroachDB has real trade-offs to weigh.

• Write latency: every commit needs a Raft majority, so single-row writes are slower than on a lone PostgreSQL node, especially across regions.
• Retries: serializable isolation means clients must handle 40001.
• Compatibility gaps: some PostgreSQL features and extensions are unsupported or differ by version.
• Hotspots: poor key design can cap scaling.
• Operational overhead: clock sync, certificates, and capacity planning still matter.

For a small app that fits comfortably on one server, a single PostgreSQL instance is often simpler and cheaper.

Most of these limits can be managed with good key design, batching, retry logic, and the right locality settings, but they should be weighed honestly during design.

Start from the requirements: which failures to survive and where users are.

1. Regions: deploy nodes in at least three regions with correct --locality flags.
2. Survival goal: set SURVIVE REGION FAILURE if the app must live through a regional outage.
3. Table localities: REGIONAL BY ROW for user data, GLOBAL for reference data.
4. Reads: use follower reads for stale-tolerant paths.
5. Keys: UUIDs or tenant-prefixed keys to avoid hotspots.
6. Operations: NTP, scheduled backups, monitoring, and tested restores.

flowchart LR
    U1["Users US"] --> R1["Region us-east"]
    U2["Users EU"] --> R2["Region europe-west"]
    U3["Users APAC"] --> R3["Region asia-southeast"]
    R1 <--> R2
    R2 <--> R3
    R1 <--> R3

Test failure scenarios before launch, by taking a region offline in staging and measuring recovery.