Almost every application needs data to be stored. This data invariably needs to be fetched, updated and searched upon. This makes it necessary for applications to use some sort of data store. For a long time, solutions based on RDBMS like SQL ruled the roost. SQL supports basic operation like CRUD on the data along with some complex operations like aggregation. However, NoSQL is now seriously challenging the RDBMS solutions to perform similar operations.
Let’s look at some of the features that each of these solutions provides on the underlying data.
SQL works on strictly typed schema where each relation (table) has predefined columns. SQL has been optimized for executing complex queries like aggregation. NoSQL on the other hand does not conform to any strongly tied schema. So, in theory, every NoSQL record can have different schemas. This makes storing unstructured data easier. Most of the NoSQL solutions provide some kind of solution, like Map Reduce to provide support for complex features.
Since NoSQL does not have any strict schema, it could be possible that the data might be stored in a wrong key erroneously and while fetching data, the result might not be as expected. However, in SQL, altering the schema might be a tricky operation as it locks the whole table till the alter operation completes which could block the transactions on that table.
Any database should provide features to query on the underlying data.
SQL is a standard query language, which gives a standard way to execute queries on the data. Every database provider provides some more optimization on SQL. Queries can be written to perform complex computation on the data present in the database.
NoSQL on the other hand, queries are not as exhaustive as SQL. Even though more support has been added for querying NoSQL data, it still has a lot of catching up to do. Each NoSQL solution, varies from each other in a way data can be queried, hence is not consistent. Moreover, for providing complex computation on data, NoSQL depends upon some underlying technologies like Map Reduce.
Transactions form a very important feature for any database.
SQL provides a strong support for atomic transactions. A set of SQL queries can be grouped under a transaction and the transaction can be committed after all the queries have been executed. If any of the queries throw any error, then the whole transaction can be rolled back. This provides strong support for atomic transaction.
NoSQL’s support for transaction is still in a very nascent stage. Many of the NoSQL solutions still don’t provide support for transactions.
SQL provides a strong support for consistency. The data is completely committed in a SQL server. With master-slave architecture, data can be replicated to multiple slaves from the master asynchronously. However, if the master crashes before the replication, there is a risk of losing data.
NoSQL follows eventually consistence approach for providing consistency. This means that data will be eventually replicated to all the nodes and the data will be consistent across all the nodes. This also means that there is no guarantee that data fetched would be up to date.
SQL provides vertical scalability. That means to make SQL more scalable the hardware capacity of the server needs to be increased. This makes the database servers expensive and difficult to maintain.
NoSQL scales horizontally. If the volume of the data grows, then by just adding another server can handle the increase in the volume. Mostly NOSQL server are commodity servers and are much more easier to maintain. This also makes NoSQL servers capable of handling very large amount of data.
What to use
Having seen the features that each of these solutions provide, we need to look at which is a more appropriate solution for storing data in an application. Application where the data can be handled in memory, SQL makes a better choice. Parts where data is not clearly defined and the data is too huge, then NoSQL makes a better choice. Joins on SQL are slow and costly. Since data can be stored without any strict schema, we can store any kind of data in NoSQL, which could help us get data by avoiding joins. Also, where scalability is of primary importance NoSQL databases offer a very good option.
Both the solutions can coexist in an application. We can leverage the benefits of each of these solutions in different parts of the application.