SQL vs NoSQL
Overview
SQL vs NoSQL is a very broad choice that branches out into more narrow choices. Let’s begin at the top.
Table of Contents
SQL vs NoSQL Comparison
| SQL | NoSQL | |
|---|---|---|
| Data Model | Table-based. | Key-Value, Document, Wide-Column, Graph. |
| Schema | Fixed schema that needs to be defined before data ingestion. Updates require potentially complex migrations. | Often flexible schemas. New fields can often be added on-the-fly. |
| Scalability | Vertical scaling unless distributed SQL (like CockroachDB). Read-replicas exist via extensions/third-party. | Typically designed from the ground-up to scale horizontally. |
| Transactions | ACID compliant, supporting complex transactions. | BASE. Some support ACID. |
| Query Languages | SQL. | Depends on the system. SQL-like queries, proprietary languages. |
| Consistency | Strong. | Typically eventually consistent (some offer strong consistency). |
| Use Cases | Complex queries, analytics, typical web applications. | Typical web applications, distributed systems, Big Data. |
| Examples | MySQL, Postgres, Oracle, Microsoft SQL, CockroachDB (distributed). | MongoDB, Cassandra, Redis. |
Table template created by ChatGPT of OpenAI.
SQL Database Engines
Note that Software.Land has already written a blog post comparing CockroachDB and Postgres. The other DB engines are relatively similar compared to the difference between CockroachDB and Postgres, given that it’s a comparison between SQL and Distributed SQL. However, the below subheadings provide some insight into the preferred use cases for each. SQLite is not included because Cloud Providers do not support it.
MySQL
MySQL is known for its simplicity and ease-of-use. This can be observed in:
- Streamlined installation.
- Default configuration (sensible for small to medium-sized applications).
- Intuitive management tools (that provide Graphical User Interfaces).
- Wide language support.
- Beginner-friendly SQL syntax (e.g. implicit type conversion can prevent errors that might occur in Postgres).
Postgres
Postgres is an enterprise, open-source database engine that is known for:
- Advanced data types (e.g. arrays and binary JSON).
- Sophisticated indexing techniques.
- Query optimization features.
- Extensibility. Developers can define their own data types, custom functions, and more.
Oracle
Oracle is an enterprise, proprietary database engine that is known for:
- Professional support and consultancy.
- Oracle RAC (Real Application Clusters) provides features that are similar to a distributed SQL like CockroachDB, but does not support the same width of geographical area.
- Advanced compression (reduces storage space).
- Advanced table partitioning (beyond what Postgres offers).
- Disaster recovery features.
Microsoft SQL
Microsoft SQL Server offers seamless integration with other Microsoft products (including .NET framework), as well as:
- Business intelligence and analytics features (e.g. SQL Server Reporting Services, SQL Server Integration Services, SQL Server Analysis Services).
- User-friendly management (SQL Server Management Studio).
CockroachDB
CockroachDB is often categorized as NoSQL, but it more accurately described as a distributed SQL. It offers true global distribution of both read and write nodes. This is something that none of the above offer. It is also known for:
- Automatic scaling.
- High resilience (entire regions could go down and the cluster’s data would survive in full global distribution).
- Self-healing. In the event of node failure, CockroachDB automatically redistributes data to other nodes. Although data is inherently distributed among many nodes, each node does not contain every piece of data.
NoSQL Databases
NoSQL databases are inherently distributed. This is partly a result of the fact that they were created in the age of the internet.
MongoDB
MongoDB is a document store that provides:
- Flexible schema (that can be complex and nested) that can be modified on-the-fly.
- Query language to support the flexibility of these schemas.
- Strong consistency model.
Cassandra
Cassandra is a wide-column store that provides:
- Very high scalability with no single point of failure.
- Tunable consistency model that allows for choosing between consistency and availability.
However:
- Cassandra’s query language is very limited. It does not support joins, subqueries, and many other SQL features.
- New fields cannot be added on-the-fly.
Wide-column data stores are not to be confused with columnar data stores.
Redis
Redis is a key-value store that provides:
- Extremely fast reads and writes (as it is an in-memory storage).
- Supports data structures like lists and sets.
- Pub/Sub messaging system capabilities.
Conclusion
The choice between SQL and NoSQL is highly dependent on the use-case.
- SQL databases are ideal for applications with complex relational data models.
- Document stores are ideal for applications with flexible and ever-changing schemas. They are also ideal for very simple applications.
- Distributed wide-column stores (like Cassandra) are ideal for time-series data, scalable messaging systems, and Big Data analytics (of denormalized data).
- In-memory key-value stores like Redis are ideal for caches.
To be updated with a greater breakdown and examination of nuances. And to include columnar and graph DBs.
Updated: 2024-02-25
Author
Sam Malayek works in Vancouver, using this space to fill in a few gaps. Opinions are his own.