apache-cassandra-vs-postgresql

Apache Cassandra and PostgreSQL (or Postgres) are popular open-source databases; Apache Cassandra is NoSQL and boasts high availability. PostgreSQL is an object-relational database and overall one of the world’s most adopted database management systems after only MySQL, Oracle, and Microsoft SQL Server. Cassandra is available free under the Apache License 2.0 and PostgreSQL is free under the liberal PostgreSQL License, which is similar to the BSD or MIT licenses.

PostgreSQL and Cassandra are frequently compared but not necessarily competitors as each is suitable for different use cases. PostgreSQL is deployed at companies of all sizes and enterprises, while Cassandra is more often deployed at larger companies and enterprises given its focus on helping users manage large volumes of possibly unstructured data across multiple data centres.

Features

There are differing reasons to deploy either Apache Cassandra or PostgreSQL for a project.

Cassandra is a wide column store suitable for supporting high velocity read and write. It is liked for its core competency, which in its case is scalability and availability. Other advantages users point out is that it is low maintenance, able to perform well on low cost commodity hardware, and that CQL (Cassandra Query Language) is easy to use, being somewhat similar to SQL.

PostgreSQL, unlike Cassandra, is an object-relational database that is typically associated to BI & analytics use cases, and supports sometimes needed complex design with seldom used but powerful features like function overloading and table inheritance. PostgreSQL is popular for a reason: users get, for free and with great open source community support, a highly programmable, feature-rich, fast, JSON & geospatial mapping capable, easy to implement, and all-around good at everything database.

Limitations

For specific use cases, there are reasons to go in a different direction than building with a Cassandra or PostgreSQL database. Drawbacks are somewhat similar, though through the looking glass of comparing a SQL database vs. a NoSQL database.

As a key-value store users note that Cassandra can be inefficient and not as performant as alternatives, and also state that queries that are ad-hoc and don’t closely mirror the database’s particular design process will tend to not work well, or may be impossible. For this reason, users stress that competent data modeling and a basic understanding of NoSQL is a must when working with Cassandra: get an expert to set it up, or risk learning to become an expert by getting it all wrong.

PostgreSQL’s features are powerful, and for inexperienced developers can lead to disastrous, anti-performant design, and it is generally less performant than MySQL, which is why that database is preferred for use cases like powering websites or running simple queries. Also, it lacks in-memory caching. Finally, many users say it can be hard to get started, with a steep learning curve and somewhat lacking documentation.

Pricing

Apache Cassandra and PostgreSQL are open source and both are available at no cost to the user when self-hosted. Cloud hosting may be desirable, in which case pricing will depend on compute costs set by the third-party cloud providers (e.g. the Amazon Keyspaces Cassandra DBaaS, etc.) or managed hosting providers: for example Instaclustr and Aiven, among others, offer managed Cassandra; and, managed PostgreSQL is provided by, to name a few, DigitalOcean, ScaleGrid Aiven.