-- ❌ The broken pattern: manually sharding Postgres by user ID range
-- Shard 1: users 1–1,000,000 → postgres-shard-1.internal
-- Shard 2: users 1,000,001–2,000,000 → postgres-shard-2.internal

-- Application code must now route every query to the correct shard:
const shard = userId <= 1_000_000 ? db_shard1 : db_shard2
const user = await shard.query('SELECT * FROM users WHERE id = $1', [userId])

-- Cross-shard joins are now impossible:
-- SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id
-- WHERE u.id IN (500000, 1500000)  -- spans two shards — no native JOIN

CockroachDB Cluster (3 nodes):

┌─────────────────────────────────────────────────────────────────┐
│ Key Range: /users/1 → /users/999999                             │
│ Raft Group: Node1 (leader), Node2 (follower), Node3 (follower)  │
│ Write: Client → Node1 (leader) → replicates to Node2+Node3      │
│ Commit: once majority (2 of 3) acknowledge → committed          │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ Key Range: /users/1000000 → /users/1999999                      │
│ Raft Group: Node2 (leader), Node1 (follower), Node3 (follower)  │
└─────────────────────────────────────────────────────────────────┘

-- CRDB looks like PostgreSQL to your application:
CREATE TABLE orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Cross-range queries just work — CRDB routes internally:
SELECT u.name, COUNT(o.id) AS order_count
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 10;

-- CRDB's default isolation: SERIALIZABLE (stronger than Postgres's READ COMMITTED default)
-- Benefit: no phantom reads, no serialization anomalies — correct by default
-- Cost: higher write contention on hot rows

-- ❌ Broken pattern: not handling serialization retries in the application
try {
  await db.query('BEGIN')
  const balance = await db.query('SELECT balance FROM accounts WHERE id = $1', [id])
  await db.query('UPDATE accounts SET balance = $1 WHERE id = $2', [balance - 100, id])
  await db.query('COMMIT')
} catch (err) {
  await db.query('ROLLBACK')
  throw err  // 40001 SQLSTATE (serialization failure) not retried — silent failure
}

// ✅ Correct: detect 40001 and retry with exponential backoff
async function withRetry<T>(fn: () => Promise<T>, maxRetries = 5): Promise<T> {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      return await fn()
    } catch (err: any) {
      if (err.code === '40001' && attempt < maxRetries - 1) {
        // Serialization failure — safe to retry
        await sleep(Math.pow(2, attempt) * 50 + Math.random() * 100)
        continue
      }
      throw err
    }
  }
  throw new Error('Max retries exceeded')
}

-- ❌ Broken: sequential integer primary key — all inserts go to the same Raft leader
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,  -- monotonically increasing: 1, 2, 3, 4...
  -- All inserts hit the highest-value range → single node bottleneck
  payload JSONB
);

-- ✅ Correct: UUID primary key — inserts distributed uniformly across all ranges
CREATE TABLE events (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,  -- random distribution
  payload JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- For time-series with range queries: hash-prefix sharding
CREATE TABLE metrics (
  shard_id  INTEGER NOT NULL,  -- shard_id = abs(hash(sensor_id)) % NUM_SHARDS
  sensor_id UUID NOT NULL,
  ts        TIMESTAMPTZ NOT NULL,
  value     FLOAT8 NOT NULL,
  PRIMARY KEY (shard_id, sensor_id, ts)
);

-- REGIONAL BY TABLE: entire table in one region — lowest read latency for that region
-- Use for: user-owned data where all users are in one geography
ALTER TABLE orders SET LOCALITY REGIONAL BY TABLE IN "us-east1";

-- REGIONAL BY ROW: each row in its home region — balanced global access
-- Use for: global user base where each user's data lives in their home region
ALTER TABLE users ADD COLUMN crdb_region crdb_internal_region AS (
  CASE
    WHEN country_code IN ('US', 'CA', 'MX') THEN 'us-east1'
    WHEN country_code IN ('GB', 'DE', 'FR') THEN 'europe-west1'
    ELSE 'asia-northeast1'
  END
) STORED;
ALTER TABLE users SET LOCALITY REGIONAL BY ROW;

-- GLOBAL: replicated to all regions, reads served locally — pay 2× RTT on writes
-- Use for: reference data (country codes, currencies, config) — rarely written
ALTER TABLE currencies SET LOCALITY GLOBAL;

-- ❌ Expensive: secondary indexes in CRDB require cross-range Raft consensus on every write
-- Every INSERT or UPDATE to a indexed column triggers a write to the index range
-- Only create secondary indexes for access patterns that cannot be served by the primary key

-- ✅ Pattern: cover the secondary index to avoid a second lookup
CREATE INDEX idx_orders_user_id ON orders (user_id) STORING (status, created_at, total_cents);
-- STORING columns eliminate the primary key lookup on the index range
-- One Raft round-trip instead of two

EU Client → CRDB Gateway (EU) → Raft Leader (US-EAST1)
  RTT 1: EU → US-EAST1 (~100ms transatlantic)

CRDB Raft Leader → Followers (US-EAST1 × 2)
  RTT 2: intra-region replication (~2ms)

Leader commits → Acks EU Gateway → EU Client
  RTT 3: US-EAST1 → EU (~100ms return)

Total write latency: ~200ms P50 for cross-region writes

-- Role-based access control — deny by default, grant explicitly
CREATE ROLE app_service;
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_service;
GRANT SELECT ON TABLE users TO app_service;
-- Never grant DELETE to application service roles — use soft deletes (deleted_at TIMESTAMPTZ)

-- Audit logging via CRDB's built-in query audit log
SET CLUSTER SETTING sql.audit.txn.events.enabled = true;
-- Audit log captures: timestamp, user, app_name, statement, latency, error