CockroachDBPostgresql

How to Choose Between PostgreSQL and CockroachDB

By August 15, 2019 September 3rd, 2019 No Comments

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

 

CockroachDBPostgreSQL
Year of Initial Release20151996
Current Stable Version19.1.311.4
LicensingBusiness 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 pointsOnline Schema changes, distributed transactionsStored procedures, cursors, triggers, materialized views
Wire ProtocolPostgreSQL wire protocolPostgreSQL wire protocol
Client DriversTested options plus large ecosystem due to PostgreSQL wire prorotcolLarge ecosystem of official and community provided drivers
CMS/framework compatibilityPartial, via PostgreSQL wire compatibilityBroad support and compatibility for major frameworks
Plugins/ExtensionsEnterprise feature packLarge ecosystem of community extensions
Replication/HAYes – built inVia open source additions/extensions
Multi-Master/ClusteringYes – built inVia open source additions/extensions
Multi-datacenter/geoYes – built inCustom or via open source additions/extensions
AuthenticationPassword, CertificatePassword, Certificate, GSSAPI, SSPI, Ident, LDAP, RADIUS, PAM, BSD
PermissionsUser-based permissions and RBACRBAC with row and column level permissions, full SQL:2011 privilege support
StrengthsCloud-native scaling, high availability, geo-distribution, straightforward configuration and deploymentFlexibility, tunability, compatibility, and ecosystem
WeaknessesLatency and richness of SQL support/featuresComplexity at scale, read performance
The Bottom LineChoose CockroachDB when you are building a highly transactional, OLTP-style workload and/or ability to scale out (locally or globally) is importantChoose 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!

Steve Croce

Steve Croce

Steve Croce is currently a Senior Product Manager and Head of User Experience at ObjectRocket. Today, Steve leads the UX/UI team through rebuilding out the platform’s user interface, scopes the company’s product and feature roadmap, and oversees the day to day development for ObjectRocket's Elasticsearch and PostgreSQL offerings. A product manager by day, he still likes to embrace his engineer roots by night and develop with Elasticsearch, SQL, Kubernetes, and web application stacks. He's spoken at KubeCon + CloudNativeCon, OpenStack summit, Percona Live, and various ObjectRocket events.