When it comes to databases there are two types that deserve some attention: SQL & NoSQL. These two different types of databases are quite different from each other. Let’s explore some of those differences.
First, let’s talk about SQL, also known as relational databases. Relational databases are structured. When I think of relational databases, a spreadsheet comes to mind. I am not saying that relational databases are spreadsheets, but they are similar. Relational databases are table-based and store their data in rows and columns . . . kinda like a spreasheet. Each row contains all of the information for one element and then each column contains all of the different attributes that can belong to that element. Some of the more well-known relational databases are MySQL and PostreSQL, but there are a few others as well. There is Oracle Database, Microsoft SQL Server, SQLite, and MariaDB as well.
There are a couple different kinds of NoSQL databases that are used today:
These databases use what they call ‘documents’ to store the data. Storing data in documents is a lot less restrictive than storing the data in rows and columns. One document is used to describe one element and the documents are stored in ‘collections’. Each document is capable of storing its own unique attributes to describe an element. This means that documents can be different from one another. Two popular document databases are CouchDB and MongoDB.
Key-value pairs are used to store data. The keys are attribute names and they are linked to values. Some popular key-value stores are Voldemort, Redis, and Dynamo.
So, this one is kind of like relational databases . . . but not really. These databases have column families, which are more or less a way to group different rows. With these databases, it isn’t necessary to know all of the columns when structuring the database. Also, it isn’t necessary for each row to have the same number of columns. Some popular wide-column databases are Cassandra and HBase.
Sometimes data is best represented if it is shown as a graph. This is where graph databases come in to play. Data can be stored with nodes and lines to show the connections between different elements and their attributes. Some popular graph databases are Neo4J and Infinite Graph.
Main Differences between SQL and NoSQL
As mentioned earlier, relational databases store their data in tables. Each row represents an element and each column represents a different attribute of that element.
NoSQL databases store their information in a couple of different ways. These different models are the ones that were mentioned above: document, key-value, wide-column, and graph.
In SQL the schema is fixed. This means that the number or columns and what is represented in the columns must be chosen before any data is stored. The shema could be altered, but that is going to require the database to go offline while it is being modified.
With NoSQL the schema is dynamic. This means that it doesn’t need to be fixed and does’t need to go offline when it is being modified.
SQL databases are vertically scalable. Vertically scaling means to improve performance by increasing the hardware’s capability. This can can result in improved horsepower, higher memory, or CPU. Vertically scaling is generally considered to be expensive and inefficient.
NoSQL databases are horizontally scalable. Horizontal scaling means to add servers and connect them in parallel to distribute the data. With the ability the scale horizontally, NoSQL databases are generally considered to be a cheaper solution when compared to SQL databases.
When thinking about reliability, think ACID. Acid stands for Atomicity, Consistency, Isolation, and Durability. For the most part, relational databases are ACID compliant. NoSQL databases are generally not ACID compliant. ACID compliancy is sacrificed for performance and the ability to scale.
So, which one is better?
When it comes to SQL and NoSQL databases, it needs to be understood that one is not necessarily better than the other. Many companies use both kinds of databases, because they realize that while one kind of database might work for one situation it might not necessarily work for another.
When to choose SQL
- If the database needs to be ACID compliant then choose SQL. NoSQL databases typically sacrifice ACID compliance for performance and scalability. NoSQL should not be chosen if a reliable database is needed.
- If the data is structured and not changing than a SQL database would be a better option than NoSQL.
When to choose NoSQL
- If the data is unstructured, then NoSQL would be a better option than SQL. A document-based database is a great option for storing unstructured data.
- The demand for cloud computing is constantly increasing. However, for cloud-computing to be a cost-effective solution, the data needs to be distributed in parallel across multiple servers. NoSQL is able to scale horizontally and works great in the cloud.
- If the structure of the data is going to undergo frequent changes to its structure, then NoSQL would be a great option. That way, when those changes are made, the database will not need to go offline.
What to remember
Here is a quick summary of the differences between SQL and NoSQL:
- SQL databases are relational. NoSQL databases are non-relational.
- SQL databases have a fixed schema. NoSQL has dynamic schema and can be modified without the database having to go offline.
- SQL databases are vertically scalable. NoSQL databases are horizontally scalable.
- SQL databases use tables to organize the data. NoSQL can use documents, key-values, wide-columns, or graphs to organize data.
- SQL databases are generally more reliable than NoSQL databases. SQL databases are ACID compliant while NoSQL databases are not. NoSQL databases sacrifice reliability for scalability and performance.