CockroachDB vs PostgreSQL - A quick database comparison

CockroachDB is a distributed SQL (“NewSQL”) database built on a transactional and strongly-consistent key-value store. It’s heavily inspired by Google’s Spanner with many similarities.

PostgreSQL is the advanced, open-source relational database management system which has the main goal of being standards-compliant and extensible.

CockroachDB uses the PostgreSQL wire protocol and its dialect is based on PostgreSQL as well. In this post, we are going to put CockroachDB and PostgreSQL in comparison to see some differences between the two database systems.

1. Model

CockroachDB: It’s open source and has a Core version which is free, and enterprise version with paid features and support provided by Cockroach Labs.
PostgreSQL: It’s completely free and open source, maintained by PostgreSQL Global Development Group and its prolific community.

2. Popularity & community

Regarding popularity, PostgreSQL is way far ahead. It’s widely known and used by many big to small companies around the world since 1996, while Cockroach Labs, the company behind CockroacDB was just founded in 2015.

PostgreSQL is also supported by a devoted and experienced community and can be extended with strong third party support.

3. Supported data types

CockroachDB supports ARRAY, BOOL, BYTES, COLLATE, DATE, DECIMAL, FLOAT, INET, INT, INTERVAL JSONB, SERIAL, STRING, TIME, TIMESTAMP, and UUID.
PostgreSQL supports a greater deal of data types: bigint, bigserial, bit [(n)], bit varying [(n)], boolean, box, bytea, character varying [(n)], character [(n)], cidr, circle, date, double precision, inet integer, interval [fields] [(p)], line, lseg, macaddr, money, numeric [(p, s)], path, point, polygon, real, smallint, serial, text, time [(p)] [without time zone], time [(p)] with time zone, timestamp [(p)] [without time zone], timestamp [(p)] with time zone, tsquery, tsvector, txid_snapshot, uuid, xml.

In PostgreSQL, JSONB and JSON are two different data types. In CockroachDB, the JSONB / JSON data type is similar to the JSONB data type in PostgreSQL.

4. Key features

CockroachDB has some key features to stack up against PostgreSQL databases. It sounds great in terms of designing distributed data store.

Feature	Meaning	CockroachDB	PostgreSQL
Automated Scaling	Automatic and continuous rebalancing of data between the nodes of a cluster.	Yes	No
Automated Failover	Uninterrupted availability of data through small- and large-scale failures, from server restarts to data center outages.	Yes	Optional
Automated Repair	Automatic repair of missing data after failures, using unaffected replicas as sources.	Yes	No
Strongly Consistent Replication	Once a transaction is committed, all reads are guaranteed to see it.	Yes	No
Consensus-Based Replication	Guarantee that progress can be made as long as any majority of nodes is available.	Yes	No
Distributed Transactions	Correctly committed transactions across a distributed cluster, whether it’s a few nodes in a single location or many nodes in multiple data centers.	Yes	No
Eventually Consistent Reads	Optionally allows reading from replicas that do not have the most recently written data.	No	Yes

In contrast, CockroachDB has some tradeoffs:

Latency: It will always provide better OLTP latency when run as a single machine than as a distributed cluster, particularly if the cluster is spread over multiple geographic regions.
SQL abilities: CockroachDB doesn’t support all the SQL queries available in Postgres, especially as complexity increases. The performance for complex queries in CockroachDB is slower than in PostgreSQL.

5. When to use

CockroachDB is well suited for applications that require reliable, available, and correct data regardless of scale. It is built to automatically replicate, rebalance, and recover with minimal configuration and operational overhead. Specific use cases include:

Distributed or replicated OLTP.
Multi-datacenter deployments.
Multi-region deployments.
Cloud migrations.
Cloud-native infrastructure initiatives.

PostgreSQL is better used for:

Data integrity.
Complex, custom procedures: If you require your database to perform custom procedures, PostgreSQL, being extensible, is the better choice.
Integration: PostgreSQL will be the most compliant and easy to handle base for the switch in case you need to migrate the entire database system to a propriety solution.
Complex designs: PostgreSQL offers the most in terms of functionality and possibilities compared to any other database systems.

6. When not to use

CockroachDB is not a good choice when very low latency reads and writes are critical; use an in-memory database instead. Also, CockroachDB is not yet suitable for heavy analytics / OLAP

PostgreSQL is not recommended when you need:

Speed: PostgreSQL is not optimized for the fast read operations
Simple setups: PostgreSQL is the most advanced database system, thus it’s not simple to set up at all. Unless you require absolute data integrity, ACID compliance or complex designs, PostgreSQL can be an over-kill.

Need a good GUI Tool for PostgreSQL or CockroachDB? Try TablePlus. It’s is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server, CockroachDB… in a fast and easy way. 

TablePlus