Divyanshu’s Blog

Welcome to my blog 👋

Two types of databases dominate almost every system design discussion. SQL and NoSQL. And at some point, the question always comes up: which one should you use?

The answer you'll hear most often is: "It depends."

Which is frustrating. Depends on what, exactly?

That's what this post is about. We'll go through what each one actually is, the theory behind the trade-offs, and how to think about the decision when designing a system.

Both have real strengths. Real production systems at Netflix, Uber, and banks around the world use both. The goal is to understand the trade-offs, not pick a side.

Let's get into it.

What is a SQL Database?

SQL databases store data in tables with rows and columns, like a spreadsheet. Each row is a record, each column is a field. Before you can store any data, you define a schema, which is a fixed structure that every record must follow.

They use Structured Query Language (SQL) to read and write data, which supports powerful operations like JOINs (linking data across multiple tables), aggregations, and complex filtering.

Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.

What is a NoSQL Database?

NoSQL stands for "Not Only SQL", not "No SQL." These databases store data in flexible models beyond the traditional table structure. The schema is dynamic, meaning fields can be added on the fly and records don't have to follow the same structure.

NoSQL databases come in different types, here are a few:

Type	Best For	Example
Key-Value	Caching, sessions, counters	Redis
Document	User profiles, product catalogs	MongoDB
Wide-Column	High-write event data, analytics	Cassandra
Graph	Social networks, fraud detection	Neo4j

ACID vs BASE

This is where the real difference between SQL and NoSQL starts to show up.

ACID (what SQL follows)

Atomicity: all steps of a transaction succeed, or all of them fail. No half-done operations.
Consistency: data is always valid before and after a transaction.
Isolation: concurrent transactions don't interfere with each other.
Durability: once a transaction is committed, it's never lost.

This is why banks use SQL. If you transfer money from account A to account B, both the debit and the credit must succeed together, or neither happens.

BASE (what most NoSQL databases follow)

Basically Available: the system always responds, even if some nodes are down.
Soft-state: the system doesn't guarantee that data stays consistent at every moment. It stays that way until all nodes finish syncing.
Eventually Consistent: all nodes will sync up and have the same data, just not immediately.

In plain terms: you might read slightly stale data for a short window of time, but the system will always respond and will always catch up.

CAP Theorem

The CAP theorem (also called Brewer's Theorem) says that any distributed database can guarantee only two of these three properties at the same time:

Consistency: every read returns the result of the most recent write
Availability: every request gets a response
Partition Tolerance: the system works even when some nodes can't communicate with each other

Network partitions in distributed systems are unavoidable. So the real trade-off is always between Consistency or Availability.

SQL = CP (Consistency + Partition Tolerance)

NoSQL = AP (Availability + Partition Tolerance)

This is the theoretical foundation that explains why NoSQL databases make the trade-offs they do. They're not poorly designed. They're intentionally optimized for availability at scale.

How They Scale

SQL scales vertically. You upgrade the existing machine by adding more RAM, a faster CPU, or bigger storage. This gets expensive fast and has a hard physical limit.

NoSQL scales horizontally. You add more servers to the cluster and distribute the data across them. This is cheaper and theoretically unlimited.

The old rule 'SQL can't scale' is oversimplified. SQL databases have ways to scale beyond a single machine, and many high-traffic systems run SQL for a very long time. NoSQL makes horizontal scaling easier, but it's not automatically fast or efficient. A poorly designed NoSQL setup can still run into serious performance problems.

Data Modeling Mindset

Picking a database also means committing to a certain way of designing your data.

SQL: design around relationships. You define your tables, enforce relationships with foreign keys, and trust the database to handle flexible querying later. You don't need to predict every access pattern upfront because SQL handles that.

NoSQL: design around access patterns. The data model is shaped around how the application reads and writes data. You need to know your access patterns before you design your schema.

The key trade-off comes down to how you store related data.

Normalizing data (SQL approach) means storing data without repetition. Each piece of information lives in exactly one place. If you need related data, you JOIN tables together at query time. This keeps things clean but at massive scale, those JOINs get slow and expensive.

Denormalizing data (NoSQL approach) means deliberately duplicating data so that everything you need for a read is already sitting together in one place. No JOINs needed, which makes reads extremely fast. But now if that duplicated data changes, you have to update every copy of it. Miss one, and your data becomes inconsistent.

One important thing to clear up: NoSQL doesn't mean "no schema." The schema still exists, it just lives in your application code instead of the database. The risk is inconsistent records and hidden data quality problems that the database won't catch for you.

When to Pick What

Pick SQL when:

Data is highly structured with clear relationships (banking, e-commerce orders, inventory)
You need complex queries, JOINs, or reporting
ACID compliance is non-negotiable (financial transactions, regulatory requirements)
Query patterns are unpredictable or varied

Pick NoSQL when:

Schema is flexible or will evolve frequently (product catalogs, CMS, user-generated content)
You need extreme horizontal scale (social feeds, IoT data, real-time analytics)
Read and write access patterns are well-known upfront and relatively simple
Speed and availability matter more than immediate consistency (caching, session management)

Some real-world examples to make this concrete:

Use Case	Database Choice	Why
Banking / Payments	SQL	ACID transactions, strict consistency
E-commerce orders	SQL	Relational integrity, complex queries
Social media feeds	NoSQL (Document)	Scale, flexible schema
Caching / Sessions	NoSQL (Key-Value)	Speed, simple lookups
IoT / Sensor data	NoSQL (Wide-Column)	Extremely high write throughput
Fraud detection	NoSQL (Graph)	Relationship traversal across entities

Polyglot Persistence

Most production systems use both, each for what it's best at. This approach is called Polyglot Persistence.

Take an e-commerce platform as an example:

PostgreSQL for orders and transactions (accuracy is non-negotiable)
Redis for caching and session management (speed is the priority)
MongoDB for the product catalog and activity logs (flexible schema, frequent changes)
Cassandra for clickstream data and analytics (massive write throughput needed)

Each database is doing the job it's actually designed for.

The One Rule to Remember

Start with SQL. Move to NoSQL only when a specific access pattern or scale requirement proves it necessary.

Most systems are better served by a relational database early on. You can always migrate later when you have real data about your bottlenecks. What you can't easily fix is a data model that was never properly designed.

Wrapping Up

SQL and NoSQL are not competitors. They're tools, each with a job they're good at.

SQL gives you reliability, structured relationships, and powerful querying. NoSQL gives you flexibility, speed, and horizontal scale. Real systems lean on both.

The question 'SQL or NoSQL?' is really just the starting point. The more useful questions are: What are the access patterns? How critical is consistency? What does the scale actually look like? How much does the schema need to evolve?

The answer to those questions is what drives the decision. Not the hype.

See you in the next post.