CockroachDB vs Postgres
February 19, 2024
Overview
CockroachDB is a relatively new distributed database (initially released in 2015) that uses the same SQL dialect as Postgres. Both write and read nodes of a cluster can be located around the world. This is in contrast to Postgres (initially released in 1996), which can only have its read nodes distributed around the world.
Use Cases
CockroachDB is specifically designed for high-scale applications with a global user base. This isn't to say that Postgres can't handle such a task, but it requires more architecture around it to be global at the same scale. Postgres's limitation to a single write node when it's replicated means that its ideal use case is an application with a regionalized user base. This application could even be data warehousing and analytics, given Postgres's advanced indexing and complex query capabilities.
Table of Contents
Comparison
| CockroachDB | Postgres | |
|---|---|---|
| Architecture | Distributed Object-relational database. | Object-relational database. |
| Data Distribution | Automatic distribution across nodes natively. | Automatic distribution across nodes (single write node) with extensions/third-party. |
| Scaling | Horizontal scaling for both read and write operations. | Horizontal scaling for read operations. |
| Latency | Low (single digit millisecond) for reads. Write latency rises as nodes become distributed across the globe. | Low (single digit millisecond) if user is co-located to a read node for reads and write node for writes. |
| Throughput | Reads and writes scale with number of nodes. | Reads scale with number of nodes, but writes are capped by single write node. |
| Reliability | Very high availability (without additional tuning). | Write node is a single point of failure. Failover strategy can mitigate this with additional tooling. |
| Consistency | Strong, because write transactions don't return until consensus is reached across all nodes (negative trade-off discussed below). | Strong. |
| Fault Tolerance | Designed to survive region failures. | Highly dependent on the setup. |
| Geo-Partitioning | Native support. | Requires additional tooling. |
| Operational Complexity | High for global distribution due to the infrastructural complexity to support the application. | Relatively low in single-node setups. Higher in replicated and especially global setups. |
| Indexing | Subset of what Postgres offers. | Extensive. |
| Data Types | Subset of what Postgres offers. | Extensive. |
| Security | Not as extensive as Postgres. Does include RBAC. | Extensive, including Zero-Knowledge Proof authentication and row-level security. Does include RBAC. |
| Backup and Recovery | Backups. Point-in-time recovery. | Extensive backups options. Point-in-time recovery. |
| Community and Support | Growing community. Commercial support available. | Large, mature, and active community. Commercial support through third-parties. |
CockroachDB
CockroachDB was designed from the ground-up as a distributed SQL database. One would normally expect that a distributed data store that accepts writes at multiple nodes would be eventually consistent. This means that if one were to write an update to the data store at one region on one side of the globe, and simultaneously query the data store in regions at opposite ends of the globe, the data would be different. CockroachDB has a solution for this. CockroachDB's transactions don't return until the entire data store is consistent. This means that it provides strong consistency. The downside to this, however is that there's a higher chance of transactions failing due to conflicting transactions if each transaction takes longer to complete. In these scenarios, the client responsible for making the update must handle the failure. Important to note that various levels of read consistency are available.
Learn more at CockroachDB's blog (Consistency Model).
Postgres
Postgres is a very mature technology. Its documentation, community, and ecosystem reflect years of development and refinement through iteration. Its learning curve is much lower than CockroachDB. Furthermore, Cloud Platforms offer services to handle its maintenance, setup, and replication for you. This is not the case with CockroachDB.
Conclusion
CockroachDB and Postgres cater to different needs. CockroachDB is intended for global distribution whereas Postgres shines in more regional use cases.
To be updated with diagrams.
Updated: 2024-02-20