CockroachDB for Global Applications — Distributed SQL Without the Distributed Systems Expertise

Introduction#

CockroachDB delivers distributed SQL across global regions with ACID guarantees and automatic failover — without requiring deep distributed systems expertise. Unlike single-region PostgreSQL, CockroachDB partitions data by region, replicates automatically, and survives node failures transparently. This guide covers when to choose CockroachDB, how to configure table localities for latency optimization, and how to handle transaction retries correctly in application code.

When to Choose CockroachDB vs PostgreSQL#

The core question is whether you need strong consistency across multiple geographic regions.

PostgreSQL is the right choice for single-region deployments. It offers mature tooling, a well-understood operational model, and read replica scaling. CockroachDB becomes compelling when you need write scaling across regions, automatic failover without a manual promotion step, or ACID guarantees across data centers.

Concrete scenarios where CockroachDB wins:

• A SaaS serving EU and US customers that requires each region to read/write with low latency
• A fintech application that needs multi-region redundancy with strong consistency on account balances
• A platform that must survive an entire cloud region going down without data loss

Scenarios where PostgreSQL is simpler:

• A startup MVP where a single-region deployment is sufficient for the next 12-18 months
• An analytics pipeline where eventual consistency on read replicas is acceptable
• Any system where the team is unfamiliar with distributed transaction semantics

Multi-Region Table Localities#

Table locality determines where CockroachDB physically stores rows and which region serves as the leaseholder for reads and writes.

-- GLOBAL: reference data replicated to every region
-- Reads are always local; writes are coordinated globally
CREATE TABLE currencies (
  code STRING PRIMARY KEY,
  name STRING,
  symbol STRING
) LOCALITY GLOBAL;
 
-- REGIONAL BY ROW: each row lives in one region
-- Reads and writes are local to the row's region
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  crdb_region crdb_internal_region NOT NULL,
  customer_id UUID,
  amount DECIMAL(12, 2),
  status STRING DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
) LOCALITY REGIONAL BY ROW;
 
-- REGIONAL: all replicas in the home region only
CREATE TABLE regional_config (
  id INT PRIMARY KEY,
  config_key STRING,
  config_value STRING
) LOCALITY REGIONAL IN "us-east1";

Choose GLOBAL for lookup tables with rare writes (currencies, countries, feature flags). Use REGIONAL BY ROW for user data and transactions where each record belongs to one region. Avoid REGIONAL unless the table is truly region-specific.

Follower Reads for Low-Latency Analytics#

Follower reads serve data from the nearest replica rather than the leaseholder. This trades slight staleness (typically under 5 seconds) for dramatically lower latency.

-- Strong read: always routed to leaseholder (may cross regions)
SELECT * FROM orders WHERE customer_id = '12345';
 
-- Follower read: served from nearest replica, bounded staleness
SELECT *
FROM orders AS OF SYSTEM TIME follower_read_timestamp()
WHERE customer_id = '12345';
 
-- Bounded staleness: specify maximum acceptable staleness
SELECT *
FROM orders AS OF SYSTEM TIME BOUNDED STALENESS '30s'
WHERE customer_id = '12345';

Use follower reads for reporting queries, analytics dashboards, and any read where the most recent milliseconds do not matter. Never use follower reads for transactional reads where you must see the result of a recent write.

Transaction Retries and Retry Loops#

CockroachDB uses optimistic concurrency. Transactions that conflict are aborted with a 40001 serialization error. Applications must retry these transactions.

const { Pool } = require('pg');
 
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
 
function isRetryable(err) {
  return ['40001', '40P01', '57P03'].includes(err.code);
}
 
async function withRetry(fn, maxAttempts = 5) {
  for (let attempt = 0; attempt < maxAttempts; attempt++) {
    try {
      return await fn();
    } catch (err) {
      if (!isRetryable(err) || attempt === maxAttempts - 1) throw err;
      const backoff = Math.min(1000, Math.pow(2, attempt) * 10);
      await new Promise(r => setTimeout(r, backoff));
    }
  }
}
 
async function transferFunds(fromId, toId, amount) {
  return withRetry(async () => {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      await client.query(
        'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
        [amount, fromId]
      );
      await client.query(
        'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
        [amount, toId]
      );
      await client.query('COMMIT');
      return { success: true };
    } catch (err) {
      await client.query('ROLLBACK');
      throw err;
    } finally {
      client.release();
    }
  });
}

Always include an idempotency key for critical operations so that retrying a transaction does not produce duplicate effects (double charges, duplicate emails).

Schema Design for Multi-Region#

Good multi-region schema design minimizes cross-region queries by keeping related data co-located.

-- Users partitioned by home region
CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid(),
  crdb_region crdb_internal_region NOT NULL,
  email STRING NOT NULL,
  name STRING,
  home_region STRING,
  created_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (crdb_region, id)
) LOCALITY REGIONAL BY ROW;
 
-- Orders co-located with users via same crdb_region
CREATE TABLE transactions (
  id UUID DEFAULT gen_random_uuid(),
  crdb_region crdb_internal_region NOT NULL,
  user_id UUID NOT NULL,
  amount DECIMAL(12, 2),
  description STRING,
  created_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (crdb_region, id)
) LOCALITY REGIONAL BY ROW;
 
-- Index for efficient per-user queries within a region
CREATE INDEX idx_txn_user_created
  ON transactions (user_id, created_at DESC);

Avoid foreign keys that reference rows in different regions — each cross-region join adds round-trip latency. Use SHOW RANGES FROM TABLE to verify data lives in the expected region after inserts.

Survivability Goals and Zone Configuration#

CockroachDB replication is controlled through zone configurations. Survivability goals map to replica counts.

-- Survive loss of one region (minimum viable multi-region)
ALTER DATABASE myapp CONFIGURE ZONE USING num_replicas = 3;
 
-- Survive loss of two regions (higher availability cost)
ALTER DATABASE myapp CONFIGURE ZONE USING num_replicas = 5;
 
-- Per-table override: critical tables get extra replicas
ALTER TABLE accounts CONFIGURE ZONE USING
  num_replicas = 5,
  lease_preferences = '[[+region=us-east1]]';
 
-- Verify zone config
SELECT zone_name, config
FROM system.zones
WHERE database_name = 'myapp';
 
-- Check replica distribution
SHOW RANGES FROM TABLE orders WITH DETAILS;

A 3-replica cluster survives the loss of one region. A 5-replica cluster survives two simultaneous region failures at the cost of higher write latency (quorum across more nodes). Match the replica count to your actual SLA, not an aspirational one — extra replicas increase write cost.

Key Takeaways#

• CockroachDB is justified when you need ACID guarantees across multiple geographic regions with automatic failover; PostgreSQL is simpler for single-region deployments.
• GLOBAL locality replicates reference tables to every region for always-local reads; use it for rarely-written data like currencies and country codes.
• REGIONAL BY ROW partitions each row to one region, making reads and writes local for that region's data.
• Follower reads use AS OF SYSTEM TIME follower_read_timestamp() to serve stale data from the nearest replica, cutting cross-region latency for analytics queries.
• CockroachDB uses optimistic concurrency — applications must detect 40001 errors and retry with exponential backoff.
• Always use idempotency keys on retryable transactions to prevent double-effects on retry.
• num_replicas = 3 survives one region failure; num_replicas = 5 survives two, at higher write cost.
• Use SHOW RANGES FROM TABLE to verify data placement matches your locality configuration.