System Design

System Design Adventure - 13

SQL vs NoSQL: Choosing the Right Database

Posted by Afsal on 25 Sep 2026

Hi Pythonistas!

Every application stores data.Users. Orders. Messages. Posts. Events.

How you store that data affects everything:

  • How fast you can query it
  • How easily you can scale it
  • How flexible your data model is
  • How consistent your data is

Two fundamental approaches exist.
SQL. And NoSQL.

Most developers start with SQL.Then hear about NoSQL.
Get confused about which to use.
This post clears that up.

SQL

Data stored in tables.Tables have rows and columns.Like a spreadsheet.

id | name  | email             | created_at
---|-------|-------------------|------------
1  | Afsal | afsal@example.com | 2024-01-15
2  | abcde | abcde@example.com | 2024-01-16
  • Every row has the same columns.
  • Every column has a defined type.
  • This structure is called a schema.
  • Defined upfront.
  • Enforced strictly.

The Power of SQL - Relationships

Tables can relate to each other.

users table:          posts table:

id | name             id | user_id | title
1  | Afsal            1  | 1       | "System Design"
2  | ABCDE            2  | 1       | "Python Tips"
                      3  | 2       | "ML Basics"

posts.user_id references users.id.Called a foreign key.

Now you can ask:

SELECT users.name, posts.title FROM users JOIN posts ON users.id = posts.user_id WHERE users.id = 1;

Result:

name  | title
------|----------------
Afsal | System Design
Afsal | Python Tips

One query. Two tables. Combined.This is called a JOIN.

The foundation of relational databases.

ACID

SQL databases guarantee ACID.

Atomicity

All or nothing.

Transfer $100 from Afsal to Abcde:

Step 1: Deduct $100 from Afsal
Step 2: Add $100 to Abcde

Step 2 fails?

→ Step 1 rolled back
→ Afsal keeps his money

Consistency

Data always follows defined rules.Rule: email must be unique
→ duplicate email rejected always

Isolation

Concurrent transactions don't interfere.
Transaction 1: reading balance
Transaction 2: updating balance
→ they don't see each other's partial changes

Durability

Committed data survives crashes.
Transaction committed.
Server crashes immediately after.
Data still there on restart.

Popular SQL Databases

PostgreSQL  → most feature-rich, open source
MySQL       → most widely used
SQLite      → embedded, mobile apps
Oracle      → enterprise
SQL Server  → Microsoft ecosystem

SQL Problems at Scale

Schema rigidity:

Need to add a column to 100 million rows?

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

Can take hours.
Table locked.
No writes allowed.
Downtime.
Horizontal scaling is hard:
SQL loves one powerful machine.

JOINs across shards = practically impossible.

NoSQL

Not Only SQL.Not one thing.

A family of databases.

Four main types:

  • Document stores    → MongoDB, CouchDB
  • Key-value stores   → Redis, DynamoDB
  • Wide-column stores → Cassandra, HBase
  • Graph databases    → Neo4j, Amazon Neptune

Each solves a different problem.

Document Stores

Data stored as JSON documents.

{
  "_id": "123",
  "name": "Afsal",
  "email": "afsal@example.com",
  "posts": [
    {"title": "System Design", "tags": ["backend"]},
    {"title": "Python Tips", "tags": ["python"]}
  ],
  "address": {
    "city": "Kottayam",
    "state": "Kerala"
  }
}

Everything about a user one document.No joins needed.
One read → all data.

Schema flexible:

{"name": "Afsal", "email": "afsal@..."}
{"name": "Abcde", "email": "abcde@...", "phone": "9876543210"}
{"name": "Xyz", "google_id": "xyz123"}

Different fields per document.No migration needed.

Good for:

  • User profiles
  • Product catalogs
  • Blog posts
  • Content management

Key-Value Stores

Simplest NoSQL type.Just a dictionary.

"user:123"      → {"name": "Afsal"}
"session:abc"   → {"user_id": 123}
"views:post:1"  → 1000000

Extremely fast.
O(1) lookup.
No complex queries.
Just get and set.
We covered Redis in Post 8.

Good for:

  • Caching
  • Sessions
  • Rate limiting
  • Leaderboards
  • Real-time counters

Wide-Column Stores

Like SQL tables.But columns vary per row.Designed for massive scale.

Row key  | profile              | activity
---------|----------------------|------------------
user:1   | name=Afsal           | last_login=Jan 15
user:2   | name=Abcde,phone=... | last_login=Jan 16
user:3   | name=Xyzab           |

Billions of rows.
Petabytes of data.
Still fast.

Good for:

  • Time-series data
  • Event logs
  • Message history
  • Analytics

Real world:

WhatsApp  → Cassandra for messages
Netflix   → Cassandra for watch history
Discord   → Cassandra for chat

Graph Databases

Data stored as nodes and edges.
(Afsal) --follows--> (Abcde)
(Afsal) --likes-->   (Post:System Design)
(Abcde) --wrote-->   (Post:System Design)

Perfect for highly connected data.SQL can model this.
But gets slow with complex relationship queries.

SQL would need multiple JOINs.
Gets exponentially slower with depth.
Graph database stays fast.

Good for:

  • Social networks
  • Recommendations
  • Fraud detection
  • Knowledge graphs

BASE

NoSQL databases follow BASE instead of ACID.
Basically Available   → system always responds
Soft state            → data might change while syncing
Eventually consistent → all nodes agree eventually

This is the AP side of CAP theorem.
Trading consistency for availability and speed.

SQL vs NoSQL

               SQL              NoSQL
────────────────────────────────────────────
Data model      Tables           Varies
Schema          Fixed            Flexible
Relationships   Excellent        Limited
Scaling         Vertical         Horizontal
ACID            Yes              Usually no
Consistency     Strong           Eventual
Speed           Good             Often faster


The Cheat Sheet

Situation                      Database
───────────────────────────────────────────────
Default / starting out         PostgreSQL
Money / transactions           PostgreSQL (ACID)
Caching                        Redis
Sessions                       Redis
Real-time counters             Redis
Leaderboards                   Redis (sorted sets)
Geolocation / nearby search    Redis (geo commands)
Flexible schema                MongoDB
Product catalogs               MongoDB
Billions of rows / high writes Cassandra
Time-series / sensor data      Cassandra / InfluxDB
Message history                Cassandra
Full text search               Elasticsearch
Autocomplete / suggestions     Elasticsearch
Social graph                   Neo4j
Fraud detection                Neo4j
File / image / video storage   S3 (not a DB)
Analytics / data warehouse     BigQuery / Redshift
Real-time analytics            ClickHouse


The Honest Truth

SQL is the right default.Start with PostgreSQL.
It handles:

  • millions of users.
  • complex queries.
  • ACID transactions.
  • JSON columns for flexible schema.
  • Full text search

PostgreSQL alone can take you very far.

Add NoSQL for specific problems:

Add Redis     → almost always (caching)
Add Cassandra → write throughput exceeds SQL limits
Add MongoDB   → schema flexibility is critical
Add Neo4j     → graph traversal is core feature

Mental Model


SQL           → tables, rows, columns, JOINs, ACID
Schema        → structure defined upfront
JOIN          → combine data from multiple tables
ACID          → atomic, consistent, isolated, durable
Document      → JSON documents, flexible, MongoDB
Key-value     → simple get/set, Redis
Wide-column   → massive scale, Cassandra
Graph         → nodes and edges, Neo4j
BASE          → basically available, eventually consistent
Default       → PostgreSQL + Redis

What Changed for Me

Before this: 

I thought NoSQL was better than SQL.
Newer.
Faster.
More scalable.

After this:

I realized they solve different problems.
SQL for structure, relationships, money.
NoSQL for scale, flexibility, speed.
The best engineers don't pick one.
They pick the right one
for the right problem.

What's Coming Next

Database Indexing. The single biggest performance improvement inside a database.

← Previous Post

Recent posts