SQL & NoSQL comparison: MySQL vs (MongoDB & Cassandra), PostgreSQL vs (MongoDB & Cassandra)

As an entrepreneur or an enterprise IT leader, you are likely thinking about the technology stack to use in your planned project. Your decision-making processes cover databases. Here, you think about SQL vs NoSQL databases. There are popular databases in both categories. Examples are MySQL and PostgreSQL among SQL databases. Among NoSQL databases, MongoDB and Cassandra are well-known. You might be weighing between MySQL vs MongoDB, MySQL vs Cassandra, PostgreSQL vs MongoDB, and PostgreSQL vs Cassandra. Which one should you use and when? Read on, as we compare these popular databases.  

SQL & NoSQL databases: How they differ

“Relational Database Management Systems” (RDBMSs) emerged in the 1970s. Edgar Frank Codd, a British computer scientist working for IBM had invented the concept of RDBMS. RDBMSs are often called “SQL databases” since they use SQL (“Structured Query Language). 

In comparison, NoSQL databases emerged in the late 2000s. The term “NoSQL” means “non-SQL” or not only SQL”, which we will discuss shortly. 

The differences between SQL and NoSQL databases are as follows:

• Data storage model: SQL databases store data in tables with fixed rows and columns. NoSQL databases store data like documents, key-value pairs, wide-columns, and graphs. Note that “Wide-column” data refers to tables with rows and dynamic columns.
• The kind of data to be stored: SQL databases allow you to store structured data only. NoSQL databases enable you to store unstructured data too.
• Objective: SQL databases are general-purpose databases. NoSQL databases can be for general purposes, however, they can be used for storing documents, key-value pairs, wide-column data, etc. 
• Schema: SQL databases use a rigid schema, however, NoSQL databases are flexible.
• Scaling: SQL databases scale up with larger servers, which is vertical scaling. NoSQL servers scale horizontally, i.e., with more servers. 
• “Joins”: SQL databases support “Joins”, however, NoSQL databases don’t support them.
• Multi-record ACID compliance: SQL databases support multi-record ACID (“Atomicity”, “Consistency”, “Isolation”, and “Durability”) properties, however, NoSQL databases often don’t support them.

MySQL, PostgreSQL, SQLite, Oracle, and Microsoft SQL Server are popular examples of SQL databases. MongoDB, Cassandra, Redis, Memcached, and Amazon DynamoDB are popular examples of NoSQL databases.

SQL Databases

MySQL: An overview

A Swedish company named MySQL AB had created MySQL. David Axmark, Allan Larsson, and Micheal Widenius had founded this company jointly. Widenius and Axmark had started developing MySQL in 1994, and they first launched it in 1995. 

Later, Sun Microsystems acquired MySQL AB. Oracle acquired Sun Microsystems in 2010. While MySQL is open-source, Oracle provides noticeable support to it. Note that MySQL is available under commercial licenses too. 

At the time of writing this, the latest stable release of MySQL is 5.7.32. This release was launched in October 2020.

MySQL occupies a prominent place among open-source RDBMSs. Many prominent companies use it since MySQL offers several key advantages. 

PostgreSQL: An overview

The development of PostgreSQL started in the 1980s, and this RDBMS is a successor of Ingres. A team of researchers and developers at the University of California, Berkeley had developed Ingres. Michael Stonebraker, the leader of this team, had started the development of Postgres. 

Stonebraker and his team had observed several limitations of RDBMSs, and they wanted to address them as part of this new project. They named the new project as “POSTGRES”. After several years of research and development, they launched PostgreSQL in 1996. 

PostgreSQL enjoys significant support from committed developers. This group of developers is known as the “PostgreSQL Global Development Group”, and it continues to enhance PostgreSQL. 

At the time of writing, the latest stable release of PostgreSQL is 13.1. The PostgreSQL Global Development Group launched this release in November 2020.

Among the established open-source RDBMSs, PostgreSQL enjoys a lot of prominences. This RDBMS is also known as “Postgres”, and it enjoys high popularity.

To learn more regarding SQL databases (MySQL, PostgreSQL & SQLite) read this comprehensive article – MySQL vs PostgreSQL vs SQLite: A comparison between 3 popular RDBMS.

NoSQL Databases:

MongoDB: An overview

MongoDB is one of the most popular NoSQL databases. It’s a general-purpose database that’s document-based. As a document database, MongoDB stores data in JSON-like documents.

10gen, a software company started developing MongoDB in 2007. This company transitioned to an open-source development model in 2009, and it launched MongoDB in the same year. 10gen changed its name to MongoDB Inc. in 2013. 

The creators of MongoDB developed it using C++. They used Go, JavaScript, and Python for several tasks too. MongoDB uses a “source-available” licensing model. 

This database went through several iterations of development. Its latest stable release came in December 2020, and this release is 4.4.3. 

Cassandra: An overview

Apache Cassandra, or Cassandra as we commonly call it, is a popular open-source NoSQL database. This distributed database can handle a large volume of data. This scalable database offers high availability, and it doesn’t have a “Single Point of Failure” (SPoF).

Two developers at Facebook had created Cassandra. Avinash Lakshman was one of them, who was also one of the creators of the Dynamo storage system offered by Amazon. Prashant Malik was the other developer. They developed it using Java. 

Facebook offered Cassandra as an open-source project in 2008. Several developers continued its development and enhancement. At the time of writing, the latest stable release of Cassandra is 3.11.9. This was released in November 2020.     

Apache Software Foundation oversees the development and support of Cassandra. This open-source database is available under the Apache License 2.0.  

To learn more regarding NoSQL databases (MongoDB, Cassandra, Redis, Memcached & DynamoDB) read this comprehensive article – NoSQL databases: MongoDB vs Cassandra vs Redis vs Memcached vs DynamoDB.

MySQL vs MongoDB: A comparison

We now compare MySQL vs MongoDB. 

The similarities between MySQL and MongoDB:

MySQL and MongoDB have the following similarities:

• Popularity: Both MySQL and MongoDB enjoy considerable popularity.
• Index: Both databases support indexes. Using indexes smartly can improve their performance.
• Deployment: Both MySQL and MongoDB support both on-premises and cloud deployment.
• Development and support: Oracle supports the development of MySQL, furthermore, there’s a strong developer community supporting this open-source database. MongoDB Inc. supports the development of MongoDB, and this open-source database has a strong developer community too. 

The differences between MySQL vs MongoDB

MySQL and MongoDB have the following differences:

• Database structure: MySQL stores data in tables that have rows and columns. In comparison, MongoDB stores data as documents in BSON (Binary JavaScript Object Notation) format. The system translates that to JSON. MySQL allows you to store structured data only, whereas, MongoDB can store unstructured data.
• The use of schema: MySQL uses schemas to define the columns and the types of data stored in them. MongoDB doesn’t use a schema. Users can store a document in MongoDB without a need to define its structure. Documents in MongoDB can have their own structure. Users can add or delete fields to modify these documents.
• Flexibility: You need to create a database in MySQL using the Entity-Relationship (ER) concepts that are central to RDBMSs. On the other hand, MongoDB allows you to create hierarchical relationships, data arrays, and other structures. MongoDB offers more flexibility.
• “Normalization”: The fundamental concepts of RDBMSs require the database designers to eliminate the duplication of data. Database designers should “normalize” data. MongoDB is a NoSQL database, therefore, it doesn’t impose such restrictions.
• “ACID” compliance: MySQL is ACID-compliant but MongoDB isn’t.
• The language for querying: Developers use SQL for querying MySQL. MongoDB developers use JavaScript for querying databases. 
• The support for a “JOIN”: MySQL supports a “JOIN”, however, MongoDB doesn’t support it.
• The kind of replication allowed: MySQL allows “Master-Slave” and “Master-Master” replication. In effect, it allows multi-source replication. MongoDB supports built-in replication and sharding. Additionally, MongoDB allows a process called the “auto-election”. This allows you to set up a secondary database, which takes over automatically if the primary database experiences a failure.
• Scaling: You need to use “vertical scaling” techniques for MySQL. This requires you to provide bigger servers. MongoDB allows “horizontal scaling”, which means that you can add servers. 

MySQL vs Cassandra: A comparison

Let’s compare MySQL vs Cassandra. 

The similarities between MySQL and Cassandra

MySQL and Cassandra have a few similarities:

• Popularity: Both of these open-source databases enjoy high popularity. 
• Deployment: You can deploy MySQL on cloud and on-premises, and it’s the same with Cassandra.
• Community support: Both MySQL and Cassandra enjoy robust community support.

The differences between MySQL vs Cassandra

MySQL vs Cassandra differences are as follows:

• The common SQL-vs-NoSQL differences: The common SQL-vs-NoSQL differences are applicable when you compare MySQL and Cassandra. MySQL requires tables with pre-defined rows and columns. It stores structured data, supports “JOINS”, and demonstrates ACID-compliance. On the other hand, Cassandra is a wide-column data store. It stores unstructured data, can’t support “JOINS”, and has limitations vis-a-vis ACID-compliance.
• Query language: MySQL uses SQL. Cassandra uses “Cassandra Query Language” (CQL), an easy-to-use query language.
• Performance: Cassandra provides robust performance in both “READ” and “WRITE” operations. In the case of MySQL, reading from multiple tables requires SQL operations like “JOIN”. This can sometimes be slow. Writing in any SQL database first requires a search, which takes time. This applies to MySQL too.
• Architecture: Cassandra uses a “Peer-to-Peer” (P2P) architecture. If one server goes down, the entire Cassandra database is still fully available. This is different from the “Master-Slave” architecture of MySQL.
• Scaling: Cassandra allows both horizontal and vertical scaling. MySQL allows vertical scaling. It can support horizontal scaling, however, the DBA (Database Administrator) needs to use sharding or “Master/Slave” replication for this. 

PostgreSQL vs MongoDB: A comparison

We now compare PostgreSQL vs MongoDB. 

The similarities between PostgreSQL and MongoDB

PostgreSQL and MongoDB have a few similarities, which are as follows:

• Popularity: Both of these open-source databases are established names in the world of databases.
• Deployment: You can deploy both PostgreSQL and MongoDB on the cloud or on your on-premise infrastructure.
• Support: PostgreSQL enjoys the support of a vibrant and large developer community. MongoDB Inc. provides premium support for MongoDB, furthermore, there’s community support available.

PostgreSQL vs MongoDB differences

The differences between PostgreSQL vs MongoDB are as follows:

• The key differences between SQL-vs-NoSQL databases: PostgreSQL is an SQL database. You store data in tables with predefined rows and tables, and you use schemas for this. PostgreSQL supports concepts like referential integrity and entity-relationship. PostgreSQL stores structured data. It supports “JOINS”. On the other hand, MongoDB is a NoSQL database. It doesn’t require a schema, and it can store unstructured data. MongoDB stores data as BSON documents and users can change the structure of the document. 
• Query language: PostgreSQL uses SQL for queries, whereas, MongoDB uses JavaScript. 
• Scaling: MongoDB supports horizontal scaling, therefore, you can add more servers. PostgreSQL supports vertical scaling. You need to use a bigger server, therefore, PostgreSQL requires downtime to upgrade. 
• The underlying needs of the development project: Are you developing an application that needs to interface with existing applications that use relational databases? Do you need to run complex queries? PostgreSQL allows you to test the limits of the SQL and relational databases since it has advanced features. It fits requirements like the ones we just talked about. On the other hand, MongoDB works better if you need a highly scalable distributed database to store unstructured data.

PostgreSQL vs Cassandra: A comparison

Let’s compare PostgreSQL vs Cassandra.

The similarities between PostgreSQL and Cassandra

PostgreSQL and Cassandra are similar in the following ways:

• Prominence: Both of these open-source databases are very prominent.
• Deployment model: You can deploy both PostgreSQL and Cassandra on the cloud as well as on-premises infrastructure. 
• Community support: Both PostgreSQL and Cassandra receive support from vibrant and large developer communities. 

The differences between PostgreSQL vs Cassandra

The differences between PostgreSQL vs Cassandra are as follows:

• The common differences between SQL-vs-NoSQL databases: PostgreSQL is an RDBMS. It complies with the ACID requirements, stores data in pre-defined tables with rows and columns, uses schemas, supports “JOINS”, and stores structured data. Cassandra is a NoSQL database without the need for a schema, and it stores unstructured data. It’s a wide-column data store. Cassandra doesn’t support “JOINS”, and it doesn’t fully comply with ACID requirements.
• Query language: While Cassandra uses CQL (Cassandra Query Language), PostgreSQL uses SQL for queries. 
• Flexibility: PostgreSQL enjoys the reputation of being an advanced RDBMS. It provides powerful features, e.g., it supports XML. PostgreSQL also supports user-defined functions for server-side scripts. Cassandra doesn’t support XML and server-side scripts. 
• Replication: Cassandra supports the selectable replication factor method. PostgreSQL supports the “Master-Master” replication method.
• Partitioning: Cassandra allows partitioning by sharding. PostgreSQL supports partitioning by range, list, and hash. 

When to use which database?

Let’s talk about when to use which database. We make the following recommendations:

Use MySQL if you have the following requirements: 

• You need to store structured data in a robust SQL database that allows both cloud and on-premises deployment.
• You need robust security, scalability, performance, and ACID-compliance.
• The learning curve should be less, and you need robust documentation and community support.

Use PostgreSQL if your requirements are as follows:

• You are developing an application that will interface with multiple applications that use RDBMSs.
• You need to store structured data and run complex queries.
• The database should offer robust scalability, security, performance, and ACID-compliance.
• You need good documentation and robust community support.
• The database should allow both cloud and on-premises deployment.

Use MongoDB if the requirements are as follows:

• You need to store unstructured data. The structure of the data will vary significantly, and you will have key analytics functions based on the unstructured data.
• You need a database that supports both cloud and on-premises deployment.
• ACID-compliance isn’t very important to your project, however, you need scalability and performance.
• You need robust documentation and community support.

Choose Cassandra if you have the following requirements:

• You need to store unstructured data, and you need a wide-column data store that supports both cloud and on-premises deployment.
• You can’t afford to lose data if one server goes down, therefore, you need a distributed peer-to-peer (P2P) architecture.
• Running a “JOIN” or ACID-compliance aren’t important to you, however, you need high scalability and performance. 
• You need ease of use, good documentation, and robust community support.

Conclusion

We talked about the differences between SQL and NoSQL databases. We compared a few popular SQL databases like MySQL and PostgreSQL with a few well-known NoSQL databases like MongoDB and Cassandra. Finally, we suggested when to use which database. Analyze your project and business requirements carefully before choosing a database solution.

Are you looking to get your App built? Contact us at hello@devathon.com or visit our website Devathon to find out how we can breathe life into your vision with beautiful designs, quality development, and continuous testing.