English
Menu

Relational Database vs NoSQL: Data Model, Consistency, Scaling

A relational database (RDB) represents data as tables of rows and columns and is queried with SQL. It supports complex queries that join tables, and keeps data strictly consistent through the ACID properties of transactions and constraints such as primary and foreign keys. NoSQL is an umbrella term for databases other than relational ones, including key-value, document, wide-column and graph databases. Most of them make the data format flexible and make it easier to scale out across many servers, at the cost of complex queries such as joins and some degree of strict consistency. Rather than one being better, the choice depends on the shape of the data and the properties you need.

AspectRelational database (RDB)NoSQL
Data representationTables (relations) of rows and columns, linked to each other by shared values (foreign keys)Depends on the type: key-value pairs, JSON-like documents, groups of columns, graphs of vertices and edges and so on
SchemaColumn names and types are defined in advance; changing them means altering the table definition, for example with ALTER TABLEMany products do not require a strict schema in advance (schemaless), so records with different fields can be stored together
QueriesSearching by conditions, aggregation, sorting and so on can be written declaratively in SQL, a standard languageUsually product-specific APIs or query languages; some products offer SQL-like languages
JoinsJOIN allows queries that combine several tablesMany handle joins poorly; data that is read together is often stored together in one document or value
Transactions and consistencyTransactions with the ACID properties can commit updates to several rows and tables togetherMany products limit updates to a single key or document, or allow eventual consistency, where replicas do not agree immediately; some products do offer ACID transactions
How to scaleMainly scaling up by making one server more powerful; reads can be spread across replicas, but spreading writes over many servers is hardEasy to scale out by partitioning data by key across many servers
NormalizationTables are normalized to remove duplicated data and prevent update anomaliesThe same data is often duplicated to favor fast reads
Suitable usesBusiness systems where integrity matters and data relationships are complex, such as accounting, inventory and orderingSessions and caches for very high-traffic web services, data without a fixed shape, large volumes of logs, connections between people or things (graphs)

Figures are typical orders of magnitude and vary by process generation and product.

When to use which

Relational database
For data that must not become inconsistent partway through an update, such as money or inventory, and data you want to join and aggregate under various conditions. It is the standard choice for most business systems.
NoSQL
When the data volume or traffic is too large for one server, when the shape of the data varies and changes often, or when the work is mostly simple reads and writes by key. Choosing the type that fits the purpose matters.

FAQ

Does NoSQL mean "no SQL"?

It is usually explained as "Not only SQL", and is used less as a rejection of SQL than as a collective name for alternatives to relational databases. In fact, some products let you use SQL-like query languages.

What kinds of NoSQL databases are there?

There are four main kinds. Key-value stores specialize in simple reads and writes of values by key. Document databases store documents with nested, JSON-like structures and can also search on fields inside documents. Wide-column stores allow each row to have different columns and suit writing large volumes of data spread over many servers. Graph databases represent connections between data as vertices and edges and suit queries that follow those connections.

How does the CAP theorem relate to this comparison?

The CAP theorem shows that a distributed database that keeps replicas on several servers cannot fully guarantee both consistency (every read returns the latest value) and availability (every request gets a response) when the network is partitioned. Many NoSQL products favor availability and adopt eventual consistency, in which replicas converge over time.

Are both ever used in the same system?

Often. For example, data where integrity matters, such as orders and payments, goes in a relational database; sessions for logged-in users and caches of frequently read data go in a key-value store; and data without a uniform shape, such as product descriptions, goes in a document database.

Check your understanding

Among the databases called NoSQL, which is generally a correct description of the characteristics of a key-value store?

  1. AIt can process complex SQL including table joins and subqueries faster than a relational database
  2. BBefore any data is written, the names and types of all columns must be strictly defined as a schema
  3. CIt specializes in the simple operation of reading and writing a value by specifying its key and is easy to scale out across multiple servers, but it is not suited to complex queries such as table joins
Show answer and explanation

Answer: C. It specializes in the simple operation of reading and writing a value by specifying its key and is easy to scale out across multiple servers, but it is not suited to complex queries such as table joins

NoSQL is a collective term for databases other than relational databases, including key-value, document, column-oriented (wide-column) and graph types. A key-value store saves pairs of a key (identifier) and a value and specializes in the simple operation of retrieving a value by specifying its key. Because data can easily be divided by key and distributed across multiple servers, it is used by web services with very heavy traffic, for example to store information about logged-in users (sessions). On the other hand, it is poor at searching by the contents of values and at complex queries such as table joins, so the application has to work around this. Many products also do not require the format of values to be strictly decided in advance (schemaless). Some products also allow eventual consistency, in which updates are not immediately reflected in all the distributed copies.

Which is an appropriate description of the CAP theorem for a distributed database that keeps copies of data on multiple servers?

  1. AWhen the network between servers is partitioned, consistency (any server returns the latest value) and availability (every request gets a response) cannot both be fully satisfied at the same time
  2. BWith proper design, consistency, availability and partition tolerance can all be satisfied at the same time, always
  3. CPartition tolerance means duplicating network links so that the network between servers is never partitioned
Show answer and explanation

Answer: A. When the network between servers is partitioned, consistency (any server returns the latest value) and availability (every request gets a response) cannot both be fully satisfied at the same time

The CAP theorem shows that a distributed database cannot fully satisfy all three of the following at once: consistency (reading from any server returns the result of the latest write), availability (every server that has not failed responds to every request) and partition tolerance (the system keeps working even when communication between servers is cut). Since network partitions cannot be avoided in practice, the key point is that when a partition occurs, the system must choose between prioritizing consistency, stopping responses to some requests, and prioritizing availability, continuing to respond while allowing stale values to be returned. When there is no partition, both consistency and availability can be satisfied. Many NoSQL products prioritize availability and adopt eventual consistency, in which the copies converge over time. Partition tolerance is not about duplicating links; it is the property of continuing to operate even when a partition occurs.

What is the main purpose of normalization of tables in a relational database?

  1. ATo reduce the number of tables so that any search can be done quickly without joins
  2. BTo encrypt data so that unauthorized users cannot read it
  3. CTo eliminate duplicated data and prevent anomalies such as contents becoming inconsistent after an update
Show answer and explanation

Answer: C. To eliminate duplicated data and prevent anomalies such as contents becoming inconsistent after an update

Normalization is a design procedure that splits tables based on functional dependencies between columns (a relationship in which the value of one column determines the value of another) so that the same fact is not recorded in several places. For example, if every row of an employee table also holds the department name, renaming a department requires rewriting every row of that department's employees, and missing any of them leaves the contents inconsistent (an update anomaly). Other problems arise as well: a department with no employees yet cannot be registered (an insertion anomaly), and deleting the last employee of a department also erases the department's information (a deletion anomaly). If department information is moved into a separate department table, a department name needs to be corrected in only one place. On the other hand, splitting tables means more joins when searching, so normalization does not necessarily make searches faster.

  1. In a database used mainly for reading and aggregation, normalized tables are sometimes deliberately merged into one so that the same data is held in duplicate (denormalization). Which correctly pairs an advantage and a disadvantage of this design?
  2. Among the ACID properties a transaction should have, which is the property that a transfer debiting account A and crediting account B is either carried out completely or not at all, even if a failure occurs partway through?

Practice Database Basics questions

Key terms explained

Relational database
A database that represents data as tables (relations) of rows and columns and relates tables to each other through shared values. Also called an RDB. Queries and updates are made in SQL, and constraints such as primary keys and foreign keys keep the data consistent.
NoSQL
A collective term for databases other than relational databases, including key-value, document, column-oriented and graph types. Many products are easy to scale out across multiple servers and allow flexible data formats, but they are often poor at complex queries such as table joins and at strict consistency.
SQL
The standard language for defining tables, searching, inserting, updating and deleting data, and controlling privileges in relational databases. It is standardized by ISO/IEC. Its statements include SELECT, INSERT, UPDATE, DELETE, CREATE TABLE and GRANT.
Transaction
A series of operations on a database treated as a single unit. COMMIT makes the results permanent, and ROLLBACK returns the data to its state before the transaction began. The DBMS manages transactions with locks and logs so that they satisfy atomicity, consistency, isolation and durability (the ACID properties).
ACID properties
The initials of four properties a transaction should satisfy: atomicity (everything is executed or nothing is), consistency (integrity constraints keep being satisfied), isolation (concurrent transactions do not interfere with each other) and durability (committed results are not lost even if a failure occurs).
Normalization
A design procedure that splits relational database tables based on functional dependencies between columns so that the same fact is not recorded in several places. It removes duplicated data and prevents update, insertion and deletion anomalies. Its stages include first to third normal form and Boyce-Codd normal form.
Primary key
A column, or set of columns, used to uniquely identify one row in a table. It carries the constraints that its values must not be duplicated and must not include NULL. The primary key is chosen from among the minimal sets of columns that can identify a row uniquely (candidate keys).
Foreign key
A column that refers to the primary key (or a unique column) of another table. Through the referential integrity constraint, which allows only values that exist in the referenced table or NULL, it prevents data that points to nonexistent rows. The behavior when a referenced row is deleted or updated (rejection, cascading deletion and so on) can also be specified.

Related comparisons