CockroachDB vs. Postgres: An Open Source Showdown

Clipartmax

The open source Relational Databases (RDMS) market is exploding with huge adoption of mature solutions, and new, innovative tools making their debut. One of them, CockroachDB, is gaining popularity with its improved functionality and automatic scalability.

In this article, we’ll introduce you to CockroachDB, compare CockroachDB vs PostgreSQL on features and specific use cases, and help you understand if you should stick with good old Postgres or make the switch to the strangely named contender.

What Is CockroachDB (CRDB)?

An open source, cloud-based transactional datastore designed to store copies of data in multiple locations to increase the speed of access. The database automatically scales and rebalances servers according to data workload.

You can combine CRDB automatic sharding with open source orchestration systems such as Kubernetes, to automate the deployment and management of containerized applications. The database claims to be resilient to failures and outages, supports and the most common languages such as Python and C/C++, and platforms including Windows and Linux.

Key Features

• Distributed SQL engine
• Cloud neutral, can be run on any public or private cloud
• Automated scalability
• Multi-Active availability system provides resilience in the event of downtime, by assuring that the majority of servers are online at any given time
• Supports the following data types: ARRAY, BOOL, BYTES, COLLATE, DATE, DECIMAL, FLOAT, INET, INT, INTERVAL JSONB, SERIAL, STRING, TIME, TIMESTAMP, and UUID.
• The JSONB/JSON data type is like Postgres’ JSONB.

What Is PostgreSQL?

PostgreSQL is the world’s fourth most popular database. It is an open source object-relational database management system (ORDBMS) that is designed to handle large workloads and complex queries and to focus on standard compliance.

Postgres has a proactive community of users, who regularly develop extensions and drive database innovations.

One of the key features of PostgreSQL is Multiversion Concurrency Control, (MVCC) which allows for concurrent access to the database and read and write permissions, for large numbers of concurrent users.

This capability is one of the reasons behind its growing popularity.

EnterpriseDB offers a commercial distribution of the database with enterprise features like scalable replication for Postgres databases and automated backups, as well as support for container technologies.

Key Features

• Multi-version concurrency control
• General-purpose, lets you add custom functions using different languages such as C++ or JavaScript
• Great support base from the huge Postgres community
• You can define your own data and index types
• Runs on Mac, X, Solaris, and Windowsv
• Supports the following 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.
• JSONB and JSON data types enable integration with JSON data streams

CoackroachDB and PostgreSQL Compared

Use Cases

CockroachDB

CRDB works well for applications that need reliable and scalable data. It relies heavily on automated replication and rebalancing processes and is easy to install and configure.

Recommended use cases:

• Replicated Online Transaction Processing (OLTP)
• Deployments across several regions or data centers
• Migrating databases to the cloud

Not recommended for:

• When you need low latency
• OLAP

Examples of Use Cases

• Streaming video platforms — as it is multi-region and multicloud, users can benefit from the high availability (HA) architecture of CRDB, and minimal database downtime.
• Online gaming websites — such companies need to provide high performance to users around the world while complying with regulations in an array of countries. CRDB can be deployed anywhere and automate compliance with regulations.
• Technology and app developers — companies such as Baidu, who require SQL to support huge volumes of data and various apps and services, can use CRDB to horizontally scale their databases.

PostgreSQL

Postgres this database is more suitable for complex queries and custom procedures because it is extensible — it lets you create your own data types, create your own functions, create your own aggregates, create your own operators, and more. It offers the strongest compliance with SQL, allowing for easy integration with proprietary solutions. However, it can be complicated to configure and set up.

Recommended use cases:

• Complex, custom queries
• Maintain data integrity
• Massive workloads

Not recommended use cases:

• Fast read operations
• Fast and easy setups

Examples

• Financial industry — is ACID compliant and works well for OLTP. Can integrate with Matlab and R. Can be used for analytics and business intelligence.
• Web technology — works well with Django, node.js, Ruby on rails and even PHP by using Postgres replication capabilities.
• Scientific data — Postgres analytical capabilities and powerful SQL engine can deal with terabytes of data generated by research projects.

Which One to Choose?

While both systems gained popularity in the last years, PostgreSQL is established as an extremely mature RDBMS system, treated as a de facto standard by many enterprises and famed for its ability to deal with complex queries and big data.

CRDB, although still a young product, has attracted large clients such as Baidu because of its horizontal automated scalability feature. While both systems have attractive features for both large and smaller organizations, each has its sweet spots in specific use cases, and your choice will probably depend on the specific application and data processing needs.