PostgreSQL vs CockroachDB

PostgreSQL is the gold standard single-node relational database. CockroachDB extends PostgreSQL's SQL interface to a distributed architecture for horizontal scaling.

Architecture Differences

PostgreSQL uses a single-writer architecture where one primary handles all writes, with streaming replication to read replicas. This is simple and performant but limits write throughput to what one machine can handle.

CockroachDB distributes data across nodes using range-based sharding. Each range is replicated via Raft consensus, providing automatic failover and strong consistency across the cluster. Any node can serve reads and writes for the ranges it holds.

Performance Characteristics

For single-node workloads, PostgreSQL is faster because there is no distributed coordination overhead. CockroachDB adds 1-5ms of latency per transaction for consensus. However, CockroachDB can handle orders of magnitude more write throughput by distributing across nodes.

CockroachDB's query optimizer is compatible with PostgreSQL's SQL but less mature for complex analytical queries. For OLAP workloads, PostgreSQL remains superior.

Trade-offs

The fundamental trade-off is simplicity versus resilience. PostgreSQL is simpler to operate, has a richer feature set, and is faster for single-node workloads. CockroachDB provides automatic failover, multi-region deployment, and horizontal scaling that PostgreSQL cannot match natively.

CockroachDB's geo-partitioning allows pinning data to specific regions for compliance and latency optimization, a unique capability for global applications.

Migration Path

CockroachDB uses the PostgreSQL wire protocol, so many applications can migrate with minimal code changes. However, some PostgreSQL features (certain extensions, stored procedures, some data types) may not be supported. Testing is essential before migration.

Real-World Usage

PostgreSQL powers Instagram, Reddit, and Apple's infrastructure. CockroachDB runs at DoorDash, Netflix (for some services), and Bose for globally distributed transactional workloads.

Learn about distributed database design and consensus algorithms. Check our system design interview guide and pricing.