Cockroachdb

The primary interface is SQL over the PostgreSQL wire protocol — no proprietary API to learn. Standard SQL DDL/DML works. CockroachDB adds REGIONAL BY ROW for geo-partitioning and AS OF SYSTEM TIME for historical reads at specific timestamps — both uniquely useful for agents operating across regions or needing point-in-time data views. IMPORT and EXPORT commands handle bulk data operations. The DB Console exposes a REST API for cluster metrics and statement diagnostics. JSON column support (JSONB) enables flexible schema patterns. Key SQL gaps agents may hit: no custom aggregates, limited trigger support, no LISTEN/NOTIFY for real-time notifications (agents needing event-driven patterns should use changefeeds instead).

SQL errors follow PostgreSQL error code conventions (SQLSTATE). Transaction retry errors (code 40001, serialization failure) are the most agent-relevant: CockroachDB's serializable isolation means contention causes transaction restarts, and agents must implement retry loops — client libraries like cockroachdb-python include retry helpers. Connection-level errors on serverless cold starts require connection pool configuration with appropriate timeouts. Changefeed errors can include schema change conflicts. The DB Console surfaces slow query analytics. Distributed architecture means network partitions can cause increased latency rather than hard failures — agents see higher p99 latencies rather than errors. Error messages are generally clear with HINT fields.

CockroachDB's core value for agents is PostgreSQL wire compatibility layered on a distributed, strongly consistent storage engine. Agents using psycopg2, node-postgres, or any standard Postgres driver connect without special libraries. The distributed architecture means agents get multi-region data access, automatic failover, and horizontal scaling without application-level sharding logic. CockroachDB Serverless offers consumption-based pricing suitable for variable agent workloads, though cold-start latency on idle clusters (up to several seconds) is a real concern for latency-sensitive operations. CockroachDB Dedicated provides reserved capacity. SQL compatibility is high but not total — some PostgreSQL extensions and features are unsupported, requiring agents to test their specific query patterns.

CockroachDB Cloud uses client certificates or username/password authentication over TLS-encrypted connections. Connection strings follow standard PostgreSQL format with sslmode=verify-full. SQL-level GRANT/REVOKE provides role-based access control. For agents, a dedicated SQL user with scoped privileges per database or schema is the recommended pattern. CockroachDB Cloud manages TLS certificates automatically. API keys for the Cloud API (cluster management, billing) are separate from SQL credentials. Single sign-on is available for console access but doesn't affect agent SQL connections. No built-in secrets rotation — agents must handle credential rotation at the application or orchestration layer.

Documentation is comprehensive and well-maintained. The 'Start a CockroachDB Cluster' quickstart is functional. Migration guides from PostgreSQL, MySQL, and Oracle exist with schema conversion tools. SQL reference documentation is thorough and notes deviations from PostgreSQL explicitly. The interactive tutorials at cockroachlabs.com/docs provide sandbox environments. University (free online courses) covers distributed systems concepts. For agents, the most useful docs are the connection guides per language/driver and the transaction retry documentation. The main gap: documentation for the serverless tier's cold-start behavior and capacity planning is thinner than it should be given its impact on agent workload patterns.