Premium Video

This video is available to premium subscribers only

Unlock Full Access

Suppose we are starting a new application. One of the first decisions we face is where to store the data. Should we use a relational database like PostgreSQL or MySQL? Or should we use a NoSQL database like MongoDB, Cassandra, or DynamoDB?

In this chapter, we will compare SQL and NoSQL databases, look at how they differ in data modeling, querying, consistency, and scaling, and discuss how to decide which one fits your system.

The choice between SQL and NoSQL affects:

It is also a common discussion point in system design interviews.

But the answer is rarely that one is simply better than the other. Each makes a different set of trade-offs.

So let's start by understanding what each term actually means.

A SQL database is a relational database. It stores data in tables made of rows and columns. Each table has a schema that defines its columns and their data types.

For example, a users table might have an id, a name, and an email. An orders table might have an id, a user_id, an amount, and a status.

The user_id in the orders table is a foreign key. It links each order back to the user who placed it.

We read and write this data using SQL, a standard query language supported by nearly every relational database. Examples include PostgreSQL, MySQL, Oracle, and SQL Server.

NoSQL is not a single type of database. It is an umbrella term for databases that do not use the traditional relational table model.

There are four common families:

These families are quite different from each other. So when someone says "NoSQL", it helps to ask which kind they mean.

For the rest of this chapter, we'll mostly compare relational databases with document and wide-column databases, because those are the most common alternatives for general application data.

The first big difference is how the database treats the structure of the data.

Schema-on-Write

A SQL database enforces the schema when data is written. If the users table says email is a text column that cannot be empty, the database rejects any row that breaks that rule.

This is sometimes called schema-on-write. It keeps the data clean and predictable.

But changing the structure requires a migration. Suppose we want to add a phone number to every user. We need to alter the table first:

On very large tables, that change has to be planned carefully.

Schema-on-Read

Most NoSQL databases take a more flexible approach. In MongoDB, two documents in the same collection can have different fields. One user document might have a phone number, and another might not:

The application decides how to interpret the data when it reads it. This is sometimes called schema-on-read.

It makes it easier to evolve the data model quickly. But the responsibility for keeping the data consistent moves from the database into the application code.

The second difference is how relationships are handled.

SQL: Normalize and Join

SQL databases encourage normalization. Each piece of information is stored in one place, and tables are connected through keys.

Suppose we want to show a user's recent orders with product names. We store users, orders, and products in separate tables. Then we join them together at query time:

Because the product name is stored only once, updating it is simple.

NoSQL: Denormalize and Embed

NoSQL databases usually favor denormalization. Instead of joining at read time, we store related data together.

For example, an order document might embed the user's shipping address and a copy of each product's name and price:

Now we can load the entire order with a single read. That is fast and simple.

But the same data may now exist in many places. If a product name changes, every document that copied it may need to be updated.

So SQL optimizes for storing data once and combining it flexibly. NoSQL often optimizes for reading data in the shape the application needs.

This leads to the third difference, which is querying.

SQL: Ask Any Question

SQL is very expressive. We can filter rows, join tables, group results, sort them, and compute aggregates in a single query.

For example, we can ask for the top ten customers by total spending last month:

We don't need to know that question in advance. As long as the data is in the tables, we can write a query for it.

NoSQL: Design Around the Questions

NoSQL databases are usually more limited here.

In a wide-column database like Cassandra, efficient queries generally need to include the partition key. So we design tables around the questions the application will ask.

If we need to find messages by conversation and also by sender, we might create two separate tables that store the same messages in different ways:

Document databases sit somewhere in the middle. MongoDB supports secondary indexes and an aggregation pipeline, so it can answer many kinds of queries. But complex joins across collections are still less natural than in SQL.

So a useful rule is this:

The fourth difference is how each handles transactions and consistency.

SQL: ACID Transactions

Relational databases are built around ACID transactions.

Suppose a user transfers $100 from one account to another. We need to subtract the money from one account and add it to the other:

A transaction guarantees that both changes happen together, or neither happens. Other users never see a state where the money has left one account but not yet arrived in the other.

NoSQL: Availability and Scale First

Many NoSQL databases were originally designed with a different priority. They focused on availability and horizontal scale, and often provided weaker guarantees across multiple records.

Some use eventual consistency. This means that after a write, different replicas may briefly return different values, but they converge to the same value over time.

Cassandra, for example, lets you tune consistency per query. You can require a majority of replicas to respond for stronger guarantees, or just one replica for lower latency.

It is worth noting that this gap has narrowed. MongoDB supports multi-document transactions, and DynamoDB offers transactional APIs as well. But in many NoSQL systems, transactions come with more restrictions or a higher performance cost than in a relational database.

The fifth difference is how each type scales.

SQL: Scale Up First

A traditional SQL database usually runs with a single primary server that handles all writes.

To scale, we typically start by upgrading that server with more CPU, memory, and faster disks. This is vertical scaling.

We can also add read replicas to spread out read traffic.

But once write traffic or data size exceeds what one primary can handle, we need to shard the data across multiple databases. With most SQL databases, sharding is added on top, either in the application or through extra tooling. Joins and transactions across shards then become much harder.

NoSQL: Built to Scale Out

Many NoSQL databases were designed for horizontal scaling from the start. In Cassandra or DynamoDB, data is automatically partitioned across many nodes using a partition key.

When we need more capacity, we add more nodes and the data is redistributed. This makes it much easier to handle very large datasets and very high write throughput.

The trade-off is that this design is also the reason for the limits we saw earlier on joins and flexible queries.

Let's make this concrete with two different parts of a system.

Payments Service: SQL

First, consider a banking or payments service.

A SQL database is a natural fit here.

Chat Application: Wide-Column

Now consider a chat application with millions of users sending messages all day.

A wide-column database like Cassandra is a strong fit here. We partition messages by conversation ID and sort them by time. Each read goes to one partition and returns messages already in order.

The same company might use both databases, each for the part of the system it suits best.

A few ideas about SQL and NoSQL come up often but are not accurate.

"NoSQL is always faster"

A NoSQL database can be very fast for the access pattern it was designed for. But a well-indexed relational database is also fast for key lookups, and it can be much faster for queries that need joins or aggregations.

"SQL databases cannot scale"

Relational databases can handle large workloads with good indexing, caching, read replicas, and sharding. Instagram, for example, has described running on sharded PostgreSQL. It takes more effort, but it is possible.

"The two categories are completely separate"

In practice, the lines have blurred. PostgreSQL supports JSON columns, so it can store flexible documents. And databases like Google Spanner and CockroachDB, sometimes called NewSQL, provide SQL and ACID transactions while scaling horizontally across many machines.

So it is better to compare specific databases and their trade-offs rather than the labels alone.

A SQL database is usually a good choice when:

For most new applications, this covers a lot of ground. That is why a relational database is often the default starting point.

A NoSQL database is usually a better fit when:

Common examples include session stores, activity feeds, chat messages, event logs, and IoT sensor data.

Many real systems do not choose just one. An e-commerce platform might use three databases:

This approach is called polyglot persistence. It lets each part of the system use the database that fits its workload.

But every extra database adds operational work. Each one has to be deployed, monitored, backed up, and understood by the team. So it is usually better to add a new database only when there is a clear need for it, rather than in advance.

SQL databases give you fixed schemas, flexible queries, joins, and strong transactions, and they usually scale vertically first.

NoSQL databases give you flexible schemas, query patterns designed in advance, and built-in horizontal scaling, often with weaker consistency across records.

The right choice depends on the shape of your data, how you query it, how much consistency you need, and how much the system needs to scale.