Before we dive into the question of why we should use databases, let us consider the question-<\/p>\n\n\n\n
Why should we consider a database and not just any other file system?<\/strong><\/p><\/blockquote>\n\n\n\n
- Databases store data in data structures like B+ Trees, LSM Trees and SSTs so that it is easy to perform CRUD operations.<\/li>
- They also provide interfaces like queries or APIs to manipulate the data while if we use a file system, it is likely that we have to parse the data to perform meaningful operations on them which can be very complex.<\/li><\/ul>\n\n\n\n
The various databases available can be grouped into the following two solutions:<\/p>\n\n\n\n
- SQL<\/li>
- NoSQL<\/li><\/ul>\n\n\n\n
They differ fundamentally on the basis of their kind of information they store and the way they store the data.<\/p>\n\n\n\n
SQL databases have a structured format (or as we call them, schemas) while NoSQL databases are unstructured and generally follow a distributed architecture. NoSQL<\/strong> was developed as a response to web data, the need for processing unstructured data, and the need for faster processing.<\/p>\n\n\n\n
SQL<\/h2>\n\n\n\n
SQL databases store the data in the traditional row and column format. Each row is an entity and the various columns indicate the various properties of the entity.<\/p>\n\n\n\n
Examples<\/strong>: MySQL, Postgres<\/em><\/p>\n\n\n\n
Why so many relations between tables?<\/strong><\/p>\n\n\n\n
- Reduce redundancy in tables.<\/li>
- Ensure consistency.<\/li>
- Make operations less expensive.<\/li><\/ul>\n\n\n\n
Why won\u2019t SQL databases solve our every problem?<\/p>\n\n\n\n
- Because they are not horizontally scalable by a huge factor.<\/li>
- Joins become very expensive as the data grows and then stored across multiple devices.<\/li><\/ul>\n\n\n\n
NoSQL<\/h2>\n\n\n\n
NoSQL databases do not follow the traditional follow the pattern of storing data in rows and columns. They store the data as key-value pairs (DynamoDB<\/em>), documents (MongoDB<\/em>) or using data structures like graphs (InfiniteGraph<\/em>). The data in NoSQL is mostly partitioned horizontally by default which ensures availability.<\/p>\n\n\n\n
Key differences between SQL and NoSQL databases<\/h1>\n\n\n\n
- Storage<\/strong>
As we mentioned earlier, SQL databases store data in form of rows and columns while NoSQL databases store data in form of key-value pairs, documents or various data structures like graphs.<\/li>- Schema<\/strong>
SQL databases or relational databases have to adhere to a strict schema. This essentially means that for each row data has to exist in each of its columns. The schema can be altered but it\u2019s not hassle free.
NoSQL databases on the other hand offer dynamic schemas. This means that for each \u2018row\u2019, some columns may or may not exist and they can be added in real time.<\/li>- Querying<\/strong>
Relational databases offer Structured Query Language to access and modify data on the tables whereas the NoSQL databases generally offer APIs to modify and query the database.<\/li>- Scalability<\/strong>
SQL databases are generally scaled vertically which makes maintaining it a much costlier affair than maintaining a NoSQL database which can be scaled horizontally.<\/li>- Reliability or ACID Compliancy (Atomicity, Consistency, Isolation, Durability)<\/strong>
When operating and handling traffic on a large scale one has to often choose either scalability & performance<\/em> or reliability<\/em>. SQL databases trade reliability for scale and performance, i.e., they guarantee the ACID properties. NoSQL databases on the other hand offer high scalability and performance by sacrificing ACID properties.<\/li><\/ul>\n\n\n\nSome key features of NoSQL databases:<\/p>\n\n\n\n
- They are generally distributed by design.<\/li>
- Each host stores almost equal amount of data so the storage stays balanced.<\/li>
- Data is generally replicated across multiple hosts so that in the event of an outage, the whole system does not go down.<\/li>
- They abide by the CAP theorem.<\/li>
- They have no fixed schema.<\/li><\/ul>\n\n\n\n
P.S. God forbid this happens, but if you need joins in a NoSQL environment then you\u2019ll have to write your own custom logic.<\/p><\/blockquote>\n\n\n\n
Hey! Should I go for SQL or NoSQL?<\/h1>\n\n\n\n
If everyone had the same shoe size, the world be a much simpler place! The trade-offs for the use case should be taken into account before a decision is taken to use either of the databases.<\/p><\/blockquote>\n\n\n\n
When should I use a SQL Database?<\/h4>\n\n\n\n
- If a system has to be designed where the ACID properties are important. E-Commerce websites that handle payment gateways and deal with a database must use a SQL database.<\/em><\/li>
- The scale of the traffic handled is not very high.<\/li>
- The data has a consistent<\/strong> schema.<\/li>
- Joins are important in the performed operations.<\/li><\/ul>\n\n\n\n
When should I use a NoSQL Database?<\/h4>\n\n\n\n
- If the scale at which the systems operate is very large and the databases are the only bottleneck in scaling up the system.<\/li>
- When your data has no particular structure and adding or deleting \u2018columns\u2019 might have a regular occurrence.<\/li>
- If you\u2019re in a team that focuses on rapid development. NoSQL databases do not need to be prepped ahead of time and thus allow the schema to modified on the fly.<\/li>
- When joins are not preferred and should be avoided.<\/li><\/ul>\n\n\n\n
NoSQL bases are not read optimised because the whole blob is read contrary to the fact that SQL databases only read the required data.
Choose a good sort key while making a NoSQL database.<\/p><\/blockquote>\n\n\n\nSources:<\/p>\n\n\n\n
- https:\/\/www.educative.io\/courses\/grokking-the-system-design-interview<\/a><\/li>
- Introduction to NoSQL databases – YouTube<\/a><\/li>
- Relational database index vs. NoSQL index – YouTube<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"
Understanding SQL and NoSQL databases<\/p>\n","protected":false},"author":2,"featured_media":119,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[38,35],"tags":[5,7,6],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/posts\/13"}],"collection":[{"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/comments?post=13"}],"version-history":[{"count":4,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/posts\/13\/revisions"}],"predecessor-version":[{"id":123,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/posts\/13\/revisions\/123"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/media\/119"}],"wp:attachment":[{"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/media?parent=13"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/categories?post=13"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/system.camp\/wp-json\/wp\/v2\/tags?post=13"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}