System Design : SQL vs NoSQL databases
I am Nandan, And you probably know me as a "Software Engineer who "hacked" an Airline to retrieve his luggage".
I am a full-time Software Engineer, Tech Speaker, and mentor. I enjoy talking about Web Development, Machine Learning, Natural language Processing, Machine learning Accelerated Mobile Pages, Progressive Web Apps, Cybersecurity, Chatbots, etc.
My claim to fame was when I posted a series of tweets on Twitter about data privacy issues on an airline’s website and the tweet got viral for all the good reasons. The story was covered by all major media portals all around the world including BBC, Saudi Gazette, Times of India, Boing Boing, Lallantop etc. and I have been interviewed by some major radio channels and podcasts.
In my free time, I like to indulge myself in activities like Photography, Gardening, Snooker, or Boxing. I am a proud owner of many plants, I sometimes talk to them (mostly pep talks).
In the realm of databases, two primary types of solutions exist, SQL (relational) and NoSQL (non-relational) databases. These two categories differ significantly in their construction, the nature of the data they store, and their storage methods. Relational databases are structured with predefined schemas, while non-relational databases are unstructured, distributed, and feature dynamic schemas.
High-level differences
Here are some high-level differences between SQL and NoSQL:
Storage
SQL stores data in tables where each row represents an entity, and each column represents a data point related to that entity.
NoSQL databases utilize various data storage models, including key-value, graph, and document-oriented approaches.
Schema
In SQL, each record follows a fixed schema, which means that the columns must be determined and chosen before data entry, and each row must contain data for each column. The schema can be modified later, but this involves modifying the database using migrations.
NoSQL schemas are dynamic. Fields can be added on the fly, and each record doesn’t have to contain data for each field.
Querying
SQL databases use Structured Query Language (SQL) to define and manipulate data, which is highly powerful.
In a NoSQL database, queries focus on a collection of documents, with different databases having different syntax for querying.
Scalability
In most cases, SQL databases can be scaled vertically, which can become very costly. While it's feasible to scale a relational database across multiple servers, it's a difficult and time-consuming process.
NoSQL databases are horizontally scalable, which means it's easy to add more servers to handle large traffic. They can be hosted on inexpensive hardware or cloud instances, making them cost-effective compared to vertical scaling. Many NoSQL technologies also distribute data across servers automatically.
Reliability
The majority of relational databases are ACID compliant. Therefore, SQL databases are still the best choice for data reliability and transaction safety.
Many NoSQL solutions prioritize performance and scalability over ACID compliance.
Reasons
As always we should always pick the technology that fits the requirements better. So, let’s look at some reasons for picking SQL or NoSQL based database:
For SQL
Structured data with strict schema
Relational data
Need for complex joins
Transactions
Lookups by index are very fast
For NoSQL
Dynamic or flexible schema
Non-relational data
No need for complex joins
Very data-intensive workload
Very high throughput for IOPS
That's all folks.
Feel free to comment on how you like my blog on the system design series or shoot me an email at connect@nandan.dev If you have any queries I will try to answer them.
You can also visit my website to read some of the articles at https://nandan.dev/
Stay tuned & connect with me on my social media channels. Subscribe to my newsletter to get regular updates on my upcoming posts.
If you're interested in learning about system design, you can join Design Guru's course on System Design Fundamentals.
