In this Post we’ll overview SQL vs noSQL (relational vs non-relational) databases comparison and cases, when particular DataBase is recommended (preferable).
SQL and noSQL databases are also frequently referred to as relational and non-relational.
In 2023, I was involved into a platform DataBases usage review and unification initiative. Within the initiative, we had to reconsider platform usage of relational (Postgres) and non-relational document-oriented (Mongo) database, and figure out whether they could be unified.
The choice in favor of Mongo was initially made by project dev team with somewhat arguable justification (like necessity of fast prototyping and development speed), and later the team witnessed itself all the advantages and drawbacks, but it appeared too costly to migrate several micro-services to relational database at that stage.
Though later the project Development team realized, that relational database would be the only right choice in that case.
Fast prototyping was appropriate argument in short-term, but in long-term SQL database with all it’s advantages would be the only right choice.
Before the Cloud providers boom (by approximately 2015) noSQL databases were frequently preferred due easier horizontal scaling, which used to be much more complex with relational databases, if they had been running in an on-premise infrastructure. But nowadays auto-scaling is guaranteed by all enterprise-grade cloud providers.
With emergency of cloud providers, things considerably changed, I attempted to find some exhaustive comparison, but still those found by me few reviews seemed to be pretty fragmented and incomplete.
So I decided to compose detailed review and comparison of SQL vs noSQL databases for to highlight key points, advantages and drawbacks.
SQL vs noSQL DataBases comparison
| Comparison item | SQL Database (e.g. Postgres) | No SQL Database (e.g. Mongo) |
| Data Structures / Models | Table based, provides highly structured (usually normalized) data, distributed across multiple tables | Document-oriented Database |
| Inter-entities relations | Possibility to define foreign keys and constraints, which would enforce referential integrity & prevent potential data errors / incorrect inserts | – Unstructured data |
| ACID compliance | Fully conforms | Claims to support ACID transactions since v. 4.0 release (June 2018), and since v. 4.2 supports distributed transactions on sharded clusters. However as per e.g. this analysis, MongoDB’s transaction mechanism violates snapshot isolation |
| OLAP / OLTP support | SQL DBs fully support both modes | MongoDB was designed for OLTP workloads |
| Flexibility / extensibility | Low, as it requires defining tables with pre-defined structure | High |
| Maintainability | More complex (require scripts management tool with versioning capabilities). See Liquibase caveats post | Easier |
| Horizontal Scaling (overall, no need to worry if DB is provided as a managed service in a Cloud provider, say AWS) | Now AWS (same as other Clouds) provides horizontal scaling capabilities for Postgres too (Aurora) | NoSQL databases were out of the box designed to scale horizontally (via sharding). |
| Cases, in which particular DataBase type is recommended to use | – highly structured (usually normalized) data; – where data integrity is critical | – Complex & diverse data structures; – Prototyping (development speed); – Huge throughput due OOTB horizontal scaling capabilities |
The above comparison outlines major cnceptual differences between SQL & noSQL databases, and should help you make the right choice with your functional and non-functional requirements in mind!