SQL vs NoSQL (relational vs non-relational)

SQL databases (relational) are designed for well-understood data models that don’t change often (e.g. banking, OLTP, OLAP etc). They value data consistency over transaction speed. SQL databases are normalized, which means they store data in multiple tables. Tables are connected using JOINS. Normalization reduces data redundancy, and provides better consistency. However, it can be slow, especially when there are complex queries with a lot of data to process. SQL databases are ACID compliant which means data is consistent and stable on the disk after transactions are complete.

NoSQL databases (non-relational) are ideal for unstructured data (e.g. image, email, etc) and generally are faster than SQL databases: They are denormalized i.e. they store a particular data entity together (in one table), so performing operations on a single data entity is faster. However, denormalization causes data redundancy. NoSQL databases are ideal for ever changing data requirements and for those who need to deal with scalable and low latency applications (web apps, gaming etc).

Database Characteristics

Strong consistency (SQL databases) means the latest data is returned, and this results in higher latency as it needs to wait for other replicas to be updated. Eventual consistency (NoSQL databases) means results are less consistent early on, but have lower latency.

According to CAP theorem, databases have to trade-off mostly between availability and consistency, and it’s a business decision:

• When each data write is vital and updates need to be reflected immediately, then you need strong consistency.
• When aggregate speed and scale is more important than correctness of individual queries, then you probably need eventual consistency.

Data Processing Systems (OLTP vs OLAP)

Online Transaction Processing (OLTP) enables real-time execution of large numbers of transactions concurrently. It requires fast response time (through indexing) and frequent backups. It modifies a small amount of data frequently. OLTP is ACID compliant, highly available and scalable with high throughput.

Online Analytical Processing (OLAP) enables complex query of databases for analytical purposes. It’s read intensive and doesn’t modify data. It requires slower response time but significant storage to store historical data.

Summary

SQL databases are ACID compliant and choose consistency over availability. Whereas, NoSQL databases follow the BASE model and choose availability over consistency.

Additional sources for further read

ScyllaDB | NoSQL vs SQL

https://www.geeksforgeeks.org/sql-vs-nosql-which-one-is-better-to-use/

https://www.oracle.com/database/what-is-oltp/

https://www.datastax.com/blog/sql-vs-nosql-pros-cons

https://aws.amazon.com/nosql/

👋 If you find this helpful, please click the clap 👏 button below a few times to show your support for the author 👇

🚀Join FAUN Developer Community & Get Similar Stories in your Inbox Each Week

Demystifying Databases Systems was originally published in FAUN Publication on Medium, where people are continuing the conversation by highlighting and responding to this story.