CockroachDB Best Practices: 12 Production Tips for 2026

CockroachDB is a distributed SQL database built for resilience and horizontal scalability. But scaling a globally distributed system introduces challenges most teams only discover after hitting production: query latency that compounds with multi-region replication, indexing decisions that create hotspots instead of spreading load, and transaction retries that silently degrade throughput when contention goes unmonitored.

According to the CNCF 2025 Annual Survey, 68% of organizations run stateful workloads in production Kubernetes clusters, with distributed databases like CockroachDB increasingly replacing traditional RDBMS for cloud native applications. That shift brings new operational complexity around schema design, cluster topology, and observability.

This guide covers 12 production tested best practices for running CockroachDB at scale, from schema and index design through query optimization, cluster configuration, backup strategy, and monitoring. Each practice includes why it matters, what breaks when you ignore it, and how to implement it correctly.

Quick Summary: 12 CockroachDB Best Practices

Practice	Why It Matters	What Breaks Without It
Use composite primary keys with non-sequential first column	Prevents write hotspots	Single range hotspot kills throughput
Index only what queries need	Reduces write amplification	Slower writes, higher storage cost
Avoid SELECT DISTINCT on large tables	Reduces memory load	Memory spikes, slow queries
Use UPSERT on tables without secondary indexes	Skips read before write	Slower insert/update operations
Batch multi-row DML statements	Reduces transaction overhead	Higher latency, lower throughput
Place frequently updated columns in separate column families	Minimizes rewrite cost	Unnecessary data rewrites
Configure cluster regions based on read/write patterns	Lowers cross-region latency	High tail latencies, poor UX
Set appropriate GC TTL for workload retention needs	Balances storage and query performance	Bloated storage or slow historical queries
Monitor transaction contention and retries	Detects hidden throughput loss	Silent performance degradation
Use changefeed for downstream integration	Decouples event propagation	Complex polling logic, stale data
Take incremental backups with tested restore paths	Reduces backup time and RTO	Long recovery windows
Track query performance with statement diagnostics	Surfaces slow query root causes	Blind debugging, long MTTR

1. Use Composite Primary Keys with Non-Sequential First Column

CockroachDB distributes data across ranges, with each range containing a contiguous segment of the primary key space. If your primary key is a single auto incrementing column like SERIAL or UUID v1 with timestamp prefix, every write goes to the same range. That range becomes a write hotspot, throttling throughput and creating imbalance across the cluster.

Why this matters

A single range can handle around 1,000 writes per second before latency increases. If all writes target one range because the primary key is sequential, you cannot scale horizontally by adding nodes. The entire table is bottlenecked by a single range.

What to do instead

Use a composite primary key where the first column has high cardinality and spreads writes across the keyspace. Common patterns include:

• (tenant_id, timestamp) for multi-tenant SaaS
• (user_id, event_id) for event logs
• (region, order_id) for geographically partitioned data

Example from CockroachDB documentation:

CREATE TABLE events (
  user_id UUID,
  event_timestamp TIMESTAMP,
  event_id UUID DEFAULT gen_random_uuid(),
  event_data JSONB,
  PRIMARY KEY (user_id, event_timestamp)
);

This distributes writes by user_id, avoiding a single range hotspot. The monotonically increasing event_timestamp is the second column, so it provides ordering within each user’s events without creating a global hotspot.

What breaks without this

A social media analytics platform using event_id SERIAL as primary key hit a write ceiling at 800 inserts per second despite running a 12 node cluster. The bottleneck was a single range receiving all writes. Switching to (user_id, event_timestamp) spread writes across 150+ ranges and scaled throughput to 15,000 inserts per second with no other changes.

2. Index Only What Queries Actually Need

Every secondary index adds write cost. When you insert a row, CockroachDB writes the row data plus an entry to each index. For a table with five indexes, one insert becomes six distributed writes. That compounds in multi-region setups where each write replicates across zones.

Why this matters

Indexes are not free. They consume storage, increase write latency, and add replication overhead. Over-indexing is one of the most common causes of slow insert performance in production CockroachDB clusters.

What to do instead

Index only the columns your queries filter, join, or sort on. Use EXPLAIN to verify that queries actually use the index. If an index is not referenced in query plans over a month, drop it.

For queries that filter on multiple columns, create a composite index covering those columns in the order they appear in the WHERE clause. Example:

CREATE INDEX idx_orders_user_status ON orders (user_id, status, created_at);

This index covers:

SELECT * FROM orders WHERE user_id = $1 AND status = 'pending' ORDER BY created_at DESC LIMIT 10;

Without the composite index, CockroachDB would need separate indexes on user_id and status, or a full table scan.

What breaks without this

An e-commerce platform added indexes on every column “just in case” during initial development. By the time they reached production, the orders table had 9 secondary indexes. Insert latency averaged 45ms at 500 orders per second. After auditing query plans and dropping unused indexes, they reduced the index count to 3. Insert latency dropped to 12ms with no query performance loss.

3. Avoid SELECT DISTINCT on Large Tables

SELECT DISTINCT forces CockroachDB to load all matching rows into memory, deduplicate them, and return the unique set. On tables with millions of rows, this can exhaust node memory and trigger out-of-memory errors or slow queries that block other workloads.

Why this matters

Unlike WHERE filters that can use indexes to limit the rows scanned, DISTINCT operates after rows are retrieved. The database must compare every row to every other row in the result set to identify duplicates.

What to do instead

Rewrite queries to use indexed filters instead of DISTINCT. If you need unique values from a column, create a separate lookup table with a unique constraint or use GROUP BY with an aggregate function, which can leverage indexes.

Example of rewriting DISTINCT:

Instead of:

SELECT DISTINCT user_id FROM events WHERE event_type = 'login';

Use:

SELECT user_id FROM events WHERE event_type = 'login' GROUP BY user_id;

GROUP BY can use an index on (event_type, user_id) and avoid loading all rows into memory.

What breaks without this

A SaaS analytics dashboard ran SELECT DISTINCT user_id FROM page_views WHERE date = CURRENT_DATE to count daily active users. The page_views table grew to 80 million rows. The query took 45 seconds and spiked memory usage to 18 GB on the coordinating node. After switching to GROUP BY user_id and adding an index on (date, user_id), query time dropped to 1.2 seconds with memory usage under 500 MB.

4. Use UPSERT on Tables Without Secondary Indexes

When inserting a row that might already exist, many applications use INSERT ... ON CONFLICT DO UPDATE. That statement performs a read to check if the row exists, then writes the row. On tables without secondary indexes, UPSERT skips the read and writes directly, reducing latency.

Why this matters

INSERT ON CONFLICT always checks for conflicts before writing. UPSERT writes immediately without checking. On tables with no secondary indexes, there is no risk of violating a unique constraint on a secondary index, so the read is unnecessary overhead.

CockroachDB documentation explicitly recommends UPSERT for this case.

What to do instead

Use UPSERT instead of INSERT ON CONFLICT on tables with no secondary indexes:

UPSERT INTO sessions (session_id, user_id, last_active) VALUES ($1, $2, NOW());

This writes the row without reading first. If a session with that session_id exists, it updates last_active. If not, it inserts a new row.

What breaks without this

A session tracking system used INSERT ... ON CONFLICT DO UPDATE to record user activity. Each write took two round trips: one to check for an existing session, one to write. Switching to UPSERT cut write latency from 8ms to 3ms, increasing throughput by 60% with no other changes.

5. Batch Multi-Row DML Statements

Executing multiple single-row INSERT, UPDATE, or DELETE statements in separate transactions creates unnecessary overhead. Each statement requires a separate transaction, which includes distributed coordination, consensus, and replication. Batching multiple rows into a single multi-row statement reduces that overhead significantly.

Why this matters

A single-row insert in a three-region CockroachDB cluster involves:

• Transaction coordinator selection
• Raft consensus across replicas
• Write to storage layer
• Replication acknowledgment

Repeating that for 100 rows means 100 separate transactions. A single 100-row insert executes one transaction covering all rows, cutting overhead by over 90%.

What to do instead

Use multi-row INSERT statements:

INSERT INTO events (user_id, event_type, event_data)
VALUES
  ($1, $2, $3),
  ($4, $5, $6),
  ($7, $8, $9);

For UPDATE and DELETE, batch operations in explicit transactions:

BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = ANY(ARRAY[$1, $2, $3]);
COMMIT;

Benchmark different batch sizes (10, 100, 1000 rows) to find the optimal size for your workload. Batches that are too large can hit transaction size limits or cause long-running transactions that block other queries.

What breaks without this

An IoT data pipeline inserted sensor readings one row at a time using a loop in application code. Each insert took 6ms, limiting throughput to 166 rows per second per application instance. Batching 100 rows per insert reduced per-batch latency to 25ms, increasing throughput to 4,000 rows per second per instance.

6. Place Frequently Updated Columns in Separate Column Families

CockroachDB stores each row as a key-value pair. By default, all columns in a row are stored together in a single column family. When you update any column, CockroachDB rewrites the entire row. If a table has large columns that are rarely updated alongside small columns that change frequently, every update rewrites the large columns unnecessarily.

Why this matters

Updating a single column in a row with 10 other columns triggers a full row rewrite. If one of those columns is a 50 KB JSON blob, every small update rewrites 50 KB of data plus replication overhead.

What to do instead

Assign frequently updated columns to a separate column family. CockroachDB will store that column family as a distinct key-value pair, so updates only rewrite the changed column family.

Example from CockroachDB column family documentation:

CREATE TABLE products (
  product_id UUID PRIMARY KEY,
  name STRING,
  description TEXT,
  inventory_count INT,
  last_updated TIMESTAMP,
  FAMILY main (product_id, name, description),
  FAMILY inventory (inventory_count, last_updated)
);

Updating inventory_count now rewrites only the inventory family, not the description text.

What breaks without this

An e-commerce catalog stored product metadata and real-time inventory counts in the same table. The description column averaged 20 KB. Inventory updates happened every few seconds, rewriting the full row each time. After splitting inventory_count into a separate column family, update throughput increased by 40% and replication traffic dropped by 35%.

7. Configure Cluster Regions Based on Read/Write Patterns

CockroachDB supports multi-region deployments where data is replicated across geographically distributed zones. Default configurations replicate data to all regions, which maximizes availability but increases write latency because every write must achieve consensus across regions separated by 50ms to 150ms of network latency.

Why this matters

A three-region cluster spanning US-East, US-West, and EU-Central will see write latencies of at least 100ms due to cross-region replication. If most users are in US-East and rarely access data from EU, replicating every write to EU adds latency without benefit.

What to do instead

Use CockroachDB multi-region features to control replica placement:

• Set database or table LOCALITY to REGIONAL BY ROW and partition data by user region
• Use REGIONAL BY TABLE to pin tables to specific regions
• Configure ZONE constraints to control replica placement

Example:

ALTER DATABASE app SET PRIMARY REGION "us-east";
ALTER DATABASE app ADD REGION "us-west";
ALTER TABLE users SET LOCALITY REGIONAL BY ROW AS region;

This places each user’s data in their home region, reducing read latency and avoiding unnecessary cross-region writes.

What breaks without this

A SaaS platform with 80% of users in North America replicated all data to three regions including APAC. Write latency averaged 120ms. After configuring REGIONAL BY ROW and setting most user data to us-east, write latency dropped to 8ms for North American users with no availability loss.

8. Set Appropriate GC TTL for Workload Retention Needs

CockroachDB retains historical versions of rows to support time-travel queries and CDC changefeeds. The garbage collection time to live (GC TTL) determines how long old row versions are kept. Default is 25 hours. Longer retention supports historical queries but increases storage. Shorter retention reduces storage but limits time-travel query windows.

Why this matters

Every update creates a new row version. Old versions consume storage until garbage collection removes them. A table with frequent updates and long GC TTL can grow to 3x the size of the current row count.

What to do instead

Set GC TTL based on your actual time-travel and CDC needs. If you do not query historical data older than 1 hour, set GC TTL to 1 hour:

ALTER TABLE events CONFIGURE ZONE USING gc.ttlseconds = 3600;

For tables with rare updates, the default 25 hours is fine. For high-churn tables like session state or real-time metrics, reduce GC TTL to minimize storage bloat.

What breaks without this

A real-time messaging platform stored chat history with default 25-hour GC TTL. The messages table received 50,000 updates per second. Storage grew to 12 TB even though current data was only 2 TB. After reducing GC TTL to 2 hours (sufficient for CDC lag), storage dropped to 3.5 TB, cutting infrastructure cost by 70%.

9. Monitor Transaction Contention and Retries

CockroachDB uses optimistic concurrency control. When two transactions modify the same row simultaneously, one succeeds and the other retries automatically. Retries are invisible to the application but reduce effective throughput. High retry rates indicate contention hotspots that degrade performance.

Why this matters

A 20% retry rate means 20% of your transactions are doing duplicate work. In a workload at 1,000 transactions per second, that is 200 wasted transactions per second, increasing latency and reducing throughput.

What to do instead

Track transaction retries using CockroachDB metrics exposed via Prometheus or SQL queries. Query the crdb_internal.node_transaction_statistics table to identify high-retry transactions:

SELECT query, count, retries
FROM crdb_internal.node_transaction_statistics
WHERE retries > 0
ORDER BY retries DESC
LIMIT 10;

For workloads with high contention, consider:

• Redesigning schema to reduce row-level conflicts
• Using SELECT ... FOR UPDATE to lock rows explicitly
• Batching updates to reduce transaction count

Tools like distributed tracing tools can surface transaction-level latency and retry patterns across services, helping you correlate database retries with application-level slowdowns.

What breaks without this

A ticket booking system allowed multiple users to reserve the same seat simultaneously, relying on optimistic locking. During peak traffic, retry rates spiked to 35%, causing user-facing latency to triple. After adding explicit row locks with SELECT ... FOR UPDATE and surfacing contention metrics in their real user monitoring tool, they reduced retry rates to under 5% and latency returned to baseline.

10. Use Changefeed for Downstream Integration

Many applications need to propagate database changes to downstream systems: search indexes, caches, analytics warehouses, or event streams. Polling the database for changes is inefficient and introduces lag. CockroachDB changefeeds stream row-level changes in real time to Kafka, cloud storage, or webhooks.

Why this matters

Polling-based change detection requires frequent queries that scan for updated rows, creating database load and introducing seconds or minutes of lag. Changefeeds push changes as they occur with sub-second latency and zero query overhead.

What to do instead

Create a changefeed targeting the tables you need to replicate:

CREATE CHANGEFEED FOR TABLE orders INTO 'kafka://kafka-broker:9092';

Changefeeds support filtering, transformations, and delivery to multiple sinks. For analytics pipelines, streaming changes to a data lake removes the need for batch ETL jobs.

What breaks without this

An e-commerce platform polled the orders table every 30 seconds to update their search index. During peak traffic, the polling query scanned 50,000 rows per poll, adding 15% load to the database. Switching to a Kafka changefeed eliminated polling queries entirely, reduced index lag from 30 seconds to under 1 second, and removed 15% of database CPU load.

11. Take Incremental Backups with Tested Restore Paths

CockroachDB supports full and incremental backups. Full backups copy the entire database. Incremental backups copy only changes since the last backup. A typical strategy is daily full backups plus hourly incrementals. But backups are useless if restores fail or take too long.

Why this matters

A backup that takes 6 hours to restore is not viable for a service with a 1-hour RTO requirement. Many teams discover their backup strategy is inadequate only during an actual outage when restore times exceed SLA limits.

What to do instead

Test restores regularly. Measure restore time for full and incremental backup chains. Automate restore testing in staging environments to verify backups are valid and meet RTO requirements.

Example backup schedule:

CREATE SCHEDULE daily_full FOR BACKUP INTO 's3://backups/full?AUTH=implicit' RECURRING '@daily';
CREATE SCHEDULE hourly_incremental FOR BACKUP INTO LATEST IN 's3://backups/full?AUTH=implicit' RECURRING '@hourly';

Then test restore:

cockroach sql --execute="RESTORE DATABASE app FROM LATEST IN 's3://backups/full?AUTH=implicit';"

Time the restore. If it exceeds your RTO, reduce backup frequency, increase parallelism, or use CockroachDB’s RESTORE ... AS OF SYSTEM TIME to restore to a specific point without replaying the full backup chain.

What breaks without this

A fintech platform took daily full backups and assumed restores would complete in under an hour. During a data corruption incident, the restore took 9 hours because the backup was 800 GB and network throughput from S3 was throttled. After implementing incremental backups and testing restores weekly, they reduced restore time to 45 minutes, meeting their 1-hour RTO.

12. Track Query Performance with Statement Diagnostics

Slow queries are often invisible until they impact users. CockroachDB’s statement diagnostics feature captures execution plans, row counts, and latency distribution for specific queries, making it easier to identify why a query is slow and what index or schema change would fix it.

Why this matters

EXPLAIN shows what the optimizer plans to do, but not what actually happened during execution. Statement diagnostics capture real execution data including rows scanned, memory used, and time spent in each query phase.

What to do instead

Enable diagnostics for slow queries using the DB Console or SQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 10;

For automatic diagnostics on slow queries, configure latency thresholds in the DB Console. CockroachDB will capture diagnostics bundles for queries exceeding the threshold.

Review diagnostics to identify:

• Full table scans that should use an index
• Joins with high row counts that need schema redesign
• Sort operations that could be eliminated with a covering index

What breaks without this

A logistics platform noticed checkout latency spikes during peak traffic but could not isolate the cause. After enabling statement diagnostics, they discovered a query filtering on (order_status, user_id) was scanning 2 million rows because the index was on (user_id, order_status). Reversing the index column order reduced query time from 850ms to 6ms, eliminating the latency spike.

How to Monitor CockroachDB in Production

Following these best practices improves performance, but production systems still require continuous monitoring to detect anomalies, track query performance, and surface issues before they impact users. CockroachDB exposes metrics via Prometheus endpoints and provides a built-in DB Console, but many teams need deeper observability integrated with their broader application monitoring stack.

CubeAPM monitors CockroachDB clusters alongside application traces, logs, and infrastructure metrics in a unified platform. It runs inside your VPC or on-prem, so database telemetry never leaves your infrastructure. Key CockroachDB metrics like query latency, transaction retries, replication lag, and range hotspots are surfaced in real-time dashboards with anomaly detection and context-aware alerting.

Unlike SaaS tools that charge per host or per metric, CubeAPM uses predictable $0.15/GB pricing with unlimited retention and no per-seat fees. Teams monitoring 50 CockroachDB nodes alongside Kubernetes, application services, and logs report 60% to 75% cost savings compared to Datadog or New Relic. CubeAPM integrates with OpenTelemetry, Prometheus, and CockroachDB’s native metrics endpoints, so you can start monitoring without replacing existing agents or instrumentation.

This estimate models a production-ready CockroachDB observability setup with APM, logs, and infrastructure monitoring. Actual costs depend on data volume, retention, and feature usage.

Frequently Asked Questions

Disclaimer: The information in this article reflects the latest details available at the time of publication and may change as technologies and products evolve. Features, pricing, and plan limits can change over time. Always verify the latest information directly with the vendor before making purchasing or deployment decisions.