Recipe

CockroachDB primer

CockroachDB is a distributed SQL database built for horizontal scale, strong consistency, and survivability across regions. This primer walks through provisioning a serverless cluster, wiring it into a Meridian backend, and shipping a schema that survives node failure without operator intervention.

1. Provision a serverless cluster

Sign in to Cockroach Cloud and create a serverless cluster in the region closest to your Meridian workers. Serverless scales to zero, so you only pay for the request units you actually consume during the night.

# Connect with the auto-generated connection string
cockroach sql --url "postgresql://user:pw@host:26257/meridian?sslmode=verify-full"

# Confirm cluster topology
> SHOW REGIONS FROM CLUSTER;

2. Model a multi-region schema

CockroachDB lets you pin rows to a home region. Use REGIONAL BY ROW for tenant-scoped data and GLOBAL for reference tables that every region reads but rarely writes.

CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name STRING NOT NULL,
  region crdb_internal_region NOT NULL AS (
    CASE WHEN created_in = 'eu' THEN 'eu-west-1' ELSE 'us-east-1' END
  ) STORED
) LOCALITY REGIONAL BY ROW;

3. Wire Meridian workers with retries

Distributed transactions occasionally hit serialization conflicts. Always retry on 40001 with exponential backoff, and prefer short, batched statements over long-running transactions to keep contention low.

async function withRetry(fn, max = 5) {
  for (let i = 0; i < max; i++) {
    try { return await fn(); }
    catch (e) {
      if (e.code !== '40001') throw e;
      await new Promise(r => setTimeout(r, 50 * 2 ** i));
    }
  }
  throw new Error('exceeded retry budget');
}