Original content published on ML4Devs website on Jun 7, 2021

SQL vs. NoSQL Database: When to Use, How to Choose

How do you choose a database? Maybe, you assess whether the use case needs a Relational database. Depending on the answer, you pick your favorite SQL or NoSQL datastore, and make it work. It is a prudent tactic: a known devil is better than an unknown angel.

Picking the right datastore can simplify your application. A wrong choice can add friction. This article will help you expand your list of known devils with an in-depth overview of various datastores. It covers the following:

• Database parts that define a datastore’s characteristics.
• Datastores categorized by data types: deep dive into databases for unstructured, structured (SQL/tabular), and semi-structured (NoSQL) data.
• When to use NoSQL vs. SQL database.
• Differences between SQL and NoSQL databases.
• Datastores specialized for various NoSQL use cases.
• Decision Tree Cheatsheet to navigate the landscape of on-prem and on-cloud datastore choices.

Inside a Database

A high-level understanding of how databases work helps in evaluating alternatives. Databases have 5 components: interface, query processor, metadata, indexes, and storage:

1. Interface Language or API: Each database defines a language or API to interact with it. It covers definition, manipulation, query, and control of data and transactions.
2. Query Processor: The “CPU” of the database. Its job is to process incoming requests, perform needed actions, and return results.
3. Storage: The disk or memory where the data is stored.
4. Indexes: Data structures to quickly locate the queried data in the storage.
5. Metadata: Meta-information of data, storage. and indexes (e.g., catalog, schema, size).

The Query Processor performs the following steps for each incoming request:

1. Parses the request and validates it against the metadata.
2. Creates an efficient execution plan that exploits the indexes.
3. Reads or updates the storage.
4. Updates metadata and indexes.
5. Computes and returns results.

To determine a datastore matches your application needs, you need carefully examine:

• Operations supported by the interface. If the computations you require are in-built, you will need to write less code.
• Available indexes. It will determine how fast your queries run.

In the next sections, let’s examine operations and indexes in datastores for various data types.