Cockroachdb Patterns

Production engineering guide for cockroachdb patterns covering patterns, implementation strategies, and operational best practices.

CockroachDB Patterns is a collection of proven, repeatable solutions for common database challenges in CockroachDB deployments. These patterns address the full lifecycle of database usage—from initial setup to scaling, fault tolerance, and query optimization—when using CockroachDB as the primary data store in a distributed, cloud-native system. They are essential when you need predictable performance, strong consistency, and automatic handling of node and network failures across geographically distributed clusters.

Handling Distributed Transactions with Multi-Region Topologies

When data must be globally accessible with strong consistency, CockroachDB’s multi-region capabilities become central. Use CREATE DATABASE with REGIONAL and GLOBAL tables to place data near users and minimize latency.

CREATE DATABASE bank
  WITH
    DEFAULT REGION = 'us-east1',
    DEFAULT REGIONALITY = 'REGIONAL',
    DEFAULT TABLE REGIONALITY = 'REGIONAL';

CREATE TABLE bank.accounts (
  id UUID PRIMARY KEY,
  balance DECIMAL(18, 2),
  region STRING NOT NULL
) REGIONAL BY TABLE IN 'us-east1' AS 'us-east1';

CREATE TABLE bank.transactions (
  id UUID PRIMARY KEY,
  from_account UUID,
  to_account UUID,
  amount DECIMAL(18, 2),
  executed_at TIMESTAMP
) GLOBAL;

A transaction spanning multiple regions—such as transferring funds from New York to London—automatically includes all required REPLICA and ZONE configurations.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;

UPDATE bank.accounts
SET balance = balance - 100.00
WHERE id = 'a1b2c3d4-5678-90ef-1234-567890abcdef'
  AND region = 'us-east1';

UPDATE bank.accounts
SET balance = balance + 100.00
WHERE id = 'b2c3d4e5-6789-0abc-1234-567890abcdef'
  AND region = 'eu-west1';

INSERT INTO bank.transactions (id, from_account, to_account, amount, executed_at)
VALUES (gen_random_uuid(), 'a1b2c3d4-5678-90ef-1234-567890abcdef',
       'b2c3d4e5-6789-0abc-1234-567890abcdef', 100.00, now());

COMMIT;

The REGIONAL table writes to the local region’s replicas and replicates data to other regions using REPLICA settings. The GLOBAL table ensures a single copy across all regions, used for cross-region coordination. A failure in the us-east1 region during a transaction may cause the REPLICA to become unavailable, leading to CRDB-1234 error: failed to resolve region 'us-east1' in cluster. This error appears when a zone configuration is missing or a node is unreachable.

To recover, ensure cockroach zone set is used to define zone configurations per table:

cockroach zone set bank.accounts --zone='{
  "region": "us-east1",
  "num_replicas": 5,
  "constraints": ["+region=us-east1"],
  "lease_holder": true
}'

The num_replicas value must be at least 3 to tolerate a single region failure. A common oversight is setting num_replicas = 3 but forgetting to configure constraints to pin replicas to specific regions. Without this, replicas may float across regions, increasing latency and reducing availability.

Optimizing Query Performance with Secondary Indexes

When a query on a WHERE clause on a non-primary key column is slow, create a secondary index. Use CREATE INDEX with STORING to avoid index-only scans and reduce I/O.

CREATE INDEX ON bank.transactions (executed_at)
  STORING (from_account, to_account, amount)
  USING B TREE;

This index supports queries like:

SELECT * FROM bank.transactions
WHERE executed_at BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY executed_at;

CockroachDB uses the index to fetch only required columns, avoiding a JOIN to the base table. However, if the STORING list is incomplete, the query may fall back to a full row scan. A silent failure mode: a query that performs well during development but degrades in production because STORING did not include a frequently accessed column.

To detect such issues, use EXPLAIN with VERBOSE and ANALYZE:

EXPLAIN (VERBOSE, ANALYZE)
SELECT * FROM bank.transactions
WHERE executed_at BETWEEN '2024-01-01' AND '2024-12-31';

The output shows Index Scan on transactions_executed_at_idx, but also includes Index Only Scan and Index Scan nodes. If the Index Only Scan is missing, it indicates the index is not covering all required columns.

Use cockroach sql to load and analyze the query plan:

cockroach sql --insecure -e "
EXPLAIN (VERBOSE, ANALYZE)
SELECT * FROM bank.transactions
WHERE executed_at BETWEEN '2024-01-01' AND '2024-12-31'
"

The ANALYZE option collects runtime statistics and returns execution time, number of rows, and I/O metrics per node. A plan showing high Index Scan cost but low Index Only Scan cost suggests missing STORING columns.

Managing Schema Changes with Zero-Downtime Migrations

Schema changes in CockroachDB must be performed with minimal disruption. Use ALTER TABLE with CONCURRENT to avoid locking the entire table.

ALTER TABLE bank.accounts
ADD COLUMN created_at TIMESTAMP DEFAULT now()
CONCURRENTLY;

This command starts the schema change without blocking reads or writes. The table remains available during the migration. However, CONCURRENTLY does not guarantee immediate availability. During the migration, new rows may be written to the old schema while the new schema is being populated. A common failure: a new column created_at is added, but a query reads NULL for created_at on rows created during the migration.

This happens because CONCURRENTLY creates a new table, copies existing data, and switches to the new version in a two-phase process. The REPLICA state must be synchronized between the old and new tables. Use SHOW TABLES to monitor progress:

SHOW TABLES FROM bank;

The output includes status column: PENDING, IN PROGRESS, FINALIZING, READY. A migration stuck in FINALIZING means it is waiting for the REPLICA to catch up, but the REPLICA is not updated. Check cockroach node status:

cockroach node status --insecure

The output shows replica count, load, and range count. A replica that is not caught up appears with unavailable state, often due to a slow network or high load.

To ensure a smooth migration, use ALTER TABLE ... SET INCREMENTAL to make schema changes incremental.

ALTER TABLE bank.accounts
SET INCREMENTAL
CONCURRENTLY;

This enables incremental updates to the table, so new rows are written to the new schema immediately, and old rows are updated in the background.

Handling Failures with Automatic Rebalancing and Repair

CockroachDB automatically rebalances data across nodes when a node fails or is added. Use cockroach node status and cockroach node ls to monitor node health.

cockroach node status --insecure

The output shows load, replica count, range count, and status. A node marked UNAVAILABLE indicates a failure. Use cockroach debug to inspect the internal state.

cockroach debug dump --node=1 --insecure

This command dumps the node’s internal state, including raft, kv, and sql logs. A common failure: a node becomes unavailable due to a network partition, but the raft leader does not step down. The cockroach debug dump shows a raft log with heartbeat messages but no leader change entries.

To repair, use cockroach node repair:

cockroach node repair 1 --insecure

This command marks the node as available and triggers a rebalancing of its ranges. It also sends REPLICA and ZONE updates to ensure the node is properly integrated.

Use cockroach sql to verify repair:

SELECT
  node_id,
  status,
  load,
  replica_count
FROM crdb_internal.node_status
WHERE status = 'UNAVAILABLE';

If a node remains UNAVAILABLE, check cockroach node ls for last_heartbeat, last_active, and last_updated times. A last_heartbeat that is stale but last_active is recent suggests a network issue, not a node failure.

Scaling with Dynamic Workload Distribution

When a query workload grows unevenly across regions, use COCKROACHDB’s dynamic load balancing and zone configurations.

cockroach zone set bank.transactions --zone='{
  "range_min_bytes": 64000000,
  "range_max_bytes": 128000000,
  "replica_constraints": [
    { "region": "us-east1" },
    { "region": "eu-west1" },
    { "region": "asia-east1" }
  ],
  "num_replicas": 3,
  "constraints": [
    "+region=us-east1",
    "+region=eu-west1",
    "+region=asia-east1"
  ]
}'

This configuration ensures that each transaction range is between 64 MB and 128 MB, and replicas are distributed across three regions. The num_replicas value controls how many copies of each range are maintained.

To monitor, use cockroach sql:

SELECT
  table_name,
  range_id,
  start_key,
  end_key,
  replica_count,
  node_ids
FROM crdb_internal.ranges
WHERE table_name = 'bank.transactions';

This query returns the current distribution of ranges and their replicas. A range with replica_count = 2 but node_ids showing only two nodes indicates a partial replication.

Use cockroach sql to trigger rebalancing:

ALTER TABLE bank.transactions
REBALANCE
REGIONAL;

This command rebalances the REGIONAL table across all regions, ensuring even distribution of data.

Integrating with External Systems via SQL and REST

CockroachDB exposes a full SQL interface via cockroach sql and REST via cockroach sql with --format=csv or --format=json.

cockroach sql --insecure --format=csv -e "
SELECT * FROM bank.accounts
WHERE balance > 1000
ORDER BY balance DESC
LIMIT 10
"

This command returns CSV output, suitable for ingestion into external systems. Use curl to access the SQL API:

curl -X POST \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM bank.accounts WHERE balance > ?",
    "args": [1000]
  }' \
  http://localhost:8080/sql

The response is a JSON object with rows, columns, and rowCount. A common failure: the args array is not properly ordered. A query expecting ? parameters returns null values when args is an object instead of an array.

Use cockroach sql to export data to a file:

cockroach sql --insecure --format=csv -e "
SELECT * FROM bank.accounts
" > accounts.csv

This command outputs CSV to stdout, which can be piped to gzip, s3, or kafka.

To import, use cockroach import:

cockroach import csv accounts.csv \
  --table=bank.accounts \
  --format=csv \
  --header \
  --insecure

This command imports a CSV file into the bank.accounts table. A failure mode: missing --header leads to the first row being treated as data, not column names. The --format=csv flag is required but often omitted, leading to misparsed data.

Monitoring and Alerting with Built-in Metrics

CockroachDB exposes rich metrics via cockroach node status and cockroach sql. Use cockroach node status to monitor cluster health:

cockroach node status --insecure

The output includes load, replica count, range count, and status. Use cockroach sql to query metrics:

SELECT
  name,
  value,
  unit
FROM crdb_internal.metrics
WHERE name LIKE 'kv.%'
ORDER BY value DESC
LIMIT 10;

This query returns the top 10 key-value metrics, including kv.raft.heartbeat, kv.transaction.commit, and kv.replica.leader.election.

Use cockroach sql to create alerting queries:

CREATE TABLE alerts (
  id UUID PRIMARY KEY,
  metric_name STRING,
  threshold DECIMAL(10, 2),
  alert_time TIMESTAMP,
  message STRING
);

Then, use cron or systemctl to run periodic checks:

#!/bin/bash

COCKROACH_SQL="cockroach sql --insecure --format=csv"

# Check for high transaction latency
$COCKROACH_SQL -e "
SELECT
  'transaction_latency_high',
  avg(latency_ms) > 100,
  'Average transaction latency > 100ms'
FROM crdb_internal.transaction_stats
WHERE start_time > now() - '1h'::interval
" | while IFS=',' read -r metric_name threshold message; do
  if [[ "$threshold" == "true" ]]; then
    cockroach sql --insecure -e "
    INSERT INTO alerts (id, metric_name, threshold, alert_time, message)
    VALUES (gen_random_uuid(), '$metric_name', 100, now(), '$message')
    "
  fi
done

This script runs every 5 minutes, checking for high transaction latency and inserting alerts into the alerts table. Use systemctl to manage the alerting service.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.