Read on Twitter
Do you know what SQL and NoSQL databases are and when they should be used?
Let's discuss it in this thread
Let's discuss it in this thread
In the world of databases, there are mainly two types namely: SQL and NoSQL
Both of them differ in the way they're built and the kind of information they store and the storage method they use.
Both of them differ in the way they're built and the kind of information they store and the storage method they use.
Definition:
SQL (See-Quel) is the standard language for dealing for relational databases.
A relational database (RDBMS) defines relationship between data in the form of entities / tables. In other words RDBMS stores data in a structured manner and have predefined schemas.
SQL (See-Quel) is the standard language for dealing for relational databases.
A relational database (RDBMS) defines relationship between data in the form of entities / tables. In other words RDBMS stores data in a structured manner and have predefined schemas.
Definition:
NoSQL is a non-relational DMS, that does not require a fixed schema, avoids joins, and is easy to scale. NoSQL database is used for distributed data stores with humongous data storage needs.
NoSQL is a non-relational DMS, that does not require a fixed schema, avoids joins, and is easy to scale. NoSQL database is used for distributed data stores with humongous data storage needs.
Storage:
SQL stores data in tables where each row represents an entity and each columns represents a data point.
NoSQL databases can have below storage models:
key-value stores
document databases
wide-column databases
graph database
SQL stores data in tables where each row represents an entity and each columns represents a data point.
NoSQL databases can have below storage models:
key-value storesdocument databaseswide-column databasesgraph database
Schema:
In SQL, column names and types are decided before data entry and each row must follow the data type rules. The schema can be altered later. But it involves updating indexes etc which are costly.
NoSQL databases are dynamic and new columns can be added on the fly.
In SQL, column names and types are decided before data entry and each row must follow the data type rules. The schema can be altered later. But it involves updating indexes etc which are costly.
NoSQL databases are dynamic and new columns can be added on the fly.
Querying:
SQL databases use SQL syntax for defining and manipulating data, which is very powerful.
In NoSQL databases, queries are focused more on collection of documents. Different types of database have different querying methods.
SQL databases use SQL syntax for defining and manipulating data, which is very powerful.
In NoSQL databases, queries are focused more on collection of documents. Different types of database have different querying methods.
Scalability:
In most common scenarios SQL database are vertically scalable which means that you can increase the load on a single server by increasing things like CPU, RAM or SSD, which can be very expensive.
In most common scenarios SQL database are vertically scalable which means that you can increase the load on a single server by increasing things like CPU, RAM or SSD, which can be very expensive.
On the other hand, NoSQL databases are horizontally scalable, meaning we can add more servers easily in our NoSQL database infrastructure to handle a lot of traffic.
This means that you handle more traffic by sharding, or adding more servers in your NoSQL database.
This means that you handle more traffic by sharding, or adding more servers in your NoSQL database.
Reliability (ACID):
The vast majority of relational databases are ACID compliant. So, when it comes to data reliability and safe guarantee of performing transactions, SQL databases are still the better bet.
NoSQL databases sacrifice ACID for performance and scalability.
The vast majority of relational databases are ACID compliant. So, when it comes to data reliability and safe guarantee of performing transactions, SQL databases are still the better bet.
NoSQL databases sacrifice ACID for performance and scalability.
When to use SQL and NoSQL databases?
When it comes to databases there's no single solution which fits all scenarios. That's why most businesses rely on both types of databases for different use cases.
When it comes to databases there's no single solution which fits all scenarios. That's why most businesses rely on both types of databases for different use cases.
Even though NoSQL database are becoming popular for their speed and scalability, there are cases where SQL databases perform better. So choosing the right database is important.
Reasons to use SQL:
When you need ACID compliance
Your data is structured and the structure hardly changes
When you join and execute complex queries
Reasons to NoSQL:
To store large volumes of data
Need scalability
Rapid development
Data is not structured
When you need ACID complianceYour data is structured and the structure hardly changesWhen you join and execute complex queriesReasons to NoSQL:
To store large volumes of dataNeed scalabilityRapid developmentData is not structured
SQL Examples: PostgresSQL, MySQL, Oracle DB, Maria DB, Sqlite etc
NoSQL Examples: Mongo DB, Couch DB, Cassandra, Redis, HBase etc
NoSQL Examples: Mongo DB, Couch DB, Cassandra, Redis, HBase etc
