CockroachDB Patterns

Quick Guide: CockroachDB connects via the standard pg driver (PostgreSQL wire protocol). The single most important difference from PostgreSQL: transaction retries are mandatory. CockroachDB's serializable isolation means any transaction can fail with SQLSTATE 40001 -- your application MUST catch this and retry the entire transaction. Use UUID with gen_random_uuid() for primary keys (never SERIAL -- sequential IDs cause distributed hotspots). DDL runs as online schema changes in background jobs and cannot be inside explicit transactions. Use AS OF SYSTEM TIME for follower reads to reduce latency in multi-region deployments.

<critical_requirements>

CRITICAL: Before Using This Skill

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(You MUST implement transaction retry logic for SQLSTATE 40001 errors -- CockroachDB WILL return serialization errors under normal operation, unlike PostgreSQL where they are rare)

(You MUST use UUID with gen_random_uuid() for primary keys -- NEVER use SERIAL or sequential IDs, which cause distributed write hotspots)

(You MUST NOT put DDL statements inside explicit transactions -- most DDL runs as background jobs and can fail at COMMIT time with a partially applied state. CREATE TABLE/CREATE INDEX are exceptions but the safest practice is always: one DDL statement per implicit transaction)

(You MUST use Pool from pg for all database access -- same as PostgreSQL, but be aware that each node in the cluster is a valid connection target)