As we move toward general availability of both CockroachDB and PostgreSQL, we’ve had a number of folks asking “Which should I choose?” for relational workloads. CockroachDB offers true global-scale SQL with distributed transactions and horizontal read/write scaling built in for unprecedented scale, resiliency, and performance for SQL workloads, but there are still some scenarios where PostgreSQL may just be a better fit.

Similarities between CockroachDB and PostgreSQL

There is surely a lot of overlap between these two technologies. First and foremost, both boast:

• SQL compliance
• ACID transactions
• PostgreSQL wire protocol compatibility 
• Broad client driver and ORM support

This covers a lot of major functionality, which means the choice comes down to more nuanced decision points. With only a few exceptions you can try an app on top of either database, and then later transition pretty easily to the other without having to rewrite your app. Want to check out CockroachDB, but decide later that PostgreSQL is a better fit? The chances are pretty good that you won’t run into any big problems. Do you have an app running on PostgreSQL, but you’re looking for better scaling and failover? CockroachDB could be a better fit.

Differentiators to Help You Decide

Again, the choice is yours, but when it comes to compatibility, complex queries, and scale versus flexibility, you may want to choose one over the other.

Scale

The biggest difference is how the products handle scaling, replication, and HA. CockroachDB was built from the ground up to be able to provide a global SQL datastore with distributed transactions. You can spread your data across clouds and regions with fine-grained control over what data is allowed to reside in each region/cloud. Though solutions and extensions do exist for providing some of the same functionality with PostgreSQL, it’s not as complete and definitely not as straightforward to configure and manage.

CockroachDB’s focus on global scale allows it to be a lot more cloud-friendly. Using the old Pets vs. Cattle metaphor, CockroachDB nodes are built to operate like cattle, while PostgreSQL instances are a lot closer to pets. In other words, CockroachDB expects a large number of smaller, disposable nodes, so scaling and HA are just handled effortlessly behind the scenes for you.

PostgreSQL, on the other hand, does provide a number of solutions for multi-master, scaling, and HA, but most of that functionality is not included in PostgreSQL itself and instead requires third-party extensions. Also, the setup, configuration, and day to day management is a bit more complex for an HA PostgreSQL deployment.

Flexibility

A real benefit of PostgreSQL is its many years of battle hardening and the scope of tunables and extensions. Just compare the documentation for Server Configuration on PostgreSQL and you’ll see a vast array of options and tuning parameters for your cluster. Want Isolation levels? You’ve got four options, where as CockroachDB gives you one. Want replication? PostgreSQL gives you 8 alternatives, Cockroach handles it behind the scenes.

PostgreSQL also offers a huge ecosystem of extensions to expand the capabilities of the product. There are hundreds of extensions publicly available and you can also write your own. These range from adding clustering (like Citus) to new objects (postGIS), to optimizing PostgreSQL for a specific use case (Timescale). 

This flexibility definitely comes with a cost though; it can introduce complexity and have an impact on the performance and stability of your instance. There’s a lot more opportunity to misconfigure your environment. The CockroachDB approach simplifies things so that building and managing a cluster is a lot more straightforward.

Compatibility

The TL;DR here is that if you need complete PostgreSQL or fuller SQL conformance, PostgreSQL is a better bet. Whether you’ve got a legacy app or you need a backend store for a standard CMS or web framework, there could be a heavy reliance on the corners of PostgreSQL or SQL that Cockroach may not support yet. Though Cockroach Labs continues to add more features and functionality to the product, it’s not 100% compatible with PostgreSQL yet.

Another area where you may see a difference is in the way that CockroachDB handles roles, users, groups, and authentication. PostgreSQL’s authentication options are extremely robust and though CockroachDB does offer RBAC, there are some significant differences between the two technologies.

Compatibility challenges can in many cases be overcome, but it is ultimately up to your app and your ability to identify and change those potential incompatibilities.

Complex Queries

An area where Cockroach Labs has invested a ton of effort has been in the ability to perform joins and improve performance with their cost-based SQL query optimizer. However, if you’ve got a Business Intelligence or analytics workload where you’re performing extremely complex queries with multiple joins, views, aggregations, expression evaluations, PostgreSQL may simply be the better option.

This is an area where you can easily test with an evaluation or proof of concept. The closer your use case leans towards analytics queries and OLAP, PostgreSQL may be the better bet, while quick OLTP style transactions is where CockroachDB can help the most.

Summary Comparison

 

	CockroachDB	PostgreSQL
Year of Initial Release	2015	1996
Current Stable Version	19.1.3	11.4
Licensing	Business Source License (BSL) – core components Cockroach Community License (CCL) – enterprise features	PostgreSQL License
SQL Conformance*	Full or partial conformance with ~244 SQL:2011 features.	Conforms with ~385 SQL:2011 features.
Bonus points	Online Schema changes, distributed transactions	Stored procedures, cursors, triggers, materialized views
Wire Protocol	PostgreSQL wire protocol	PostgreSQL wire protocol
Client Drivers	Tested options plus large ecosystem due to PostgreSQL wire prorotcol	Large ecosystem of official and community provided drivers
CMS/framework compatibility	Partial, via PostgreSQL wire compatibility	Broad support and compatibility for major frameworks
Plugins/Extensions	Enterprise feature pack	Large ecosystem of community extensions
Replication/HA	Yes – built in	Via open source additions/extensions
Multi-Master/Clustering	Yes – built in	Via open source additions/extensions
Multi-datacenter/geo	Yes – built in	Custom or via open source additions/extensions
Authentication	Password, Certificate	Password, Certificate, GSSAPI, SSPI, Ident, LDAP, RADIUS, PAM, BSD
Permissions	User-based permissions and RBAC	RBAC with row and column level permissions, full SQL:2011 privilege support
Strengths	Cloud-native scaling, high availability, geo-distribution, straightforward configuration and deployment	Flexibility, tunability, compatibility, and ecosystem
Weaknesses	Latency and richness of SQL support/features	Complexity at scale, read performance
The Bottom Line	Choose CockroachDB when you are building a highly transactional, OLTP-style workload and/or ability to scale out (locally or globally) is important	Choose PostgreSQL if your workload will include heavier analytics queries and/or you need access to a huge feature set, ecosystem, and compatible apps.

* Since no datastore that I know of 100% conforms to the full SQL:2011 spec, I just looked at how much of the spec each product supports.

You have two great options before you and nothing says you have to pick one. Both can be used in concert to create an amazing datastore portfolio. If you’re building from scratch today and you don’t have extremely unique or PostgreSQL-specific needs and scaling matters, then look seriously at CockroachDB as an amazing opportunity. However, if you already know what you need and want to focus on the broad flexibility of PostgreSQL, then we can help there as well.

Both PostgreSQL and CockroachDB are available now at no cost for a limited time. Try them out now and find which is the right fit for you. And contact us with any questions you have, we are here for you!