SQL vs NoSQL Databases

Deciding the correct database is the most essential part of system design.

There are two categories of the database which are SQL (Structured Query Language) and NoSQL (Not Only SQL). Both have their unique features, and weakness. Choosing the right one is a core architectural decision in system design.

The choice between the two affects not only how data is stored but also how your application scales, performs, and evolves over time.

What Are SQL Databases?

SQL (Structured Query Language) databases, also known as relational databases, store data in table with fixed schemas.

Key Characteristics:

1. Structured Schema: Tables with predefined columns and data types.
2. ACID Transactions: Guarantees Atomicity, Consistency, Isolation, Durability, making SQL ideal for transactional systems.
3. Strong Relationships: Foreign keys and joins enable complex relationships between tables.
4. Query Language: Use SQL for data manipulation and queries.
5. Mature Ecosystem: Long-standing tools for backup, monitoring, and scaling.

Popular SQL Databases:

• MySQL
• PostgreSQL
• Oracle Database
• Microsoft SQL Server

Pros of SQL in System Design

• Data Integrity & Consistency: Ensures transactional reliability.
• Complex Queries: JOINs and aggregates simplify querying relational data.
• Mature Ecosystem: Well-tested, reliable, and supported in production.
• Predictable Behavior: Easy reasoning for financial, inventory, or ERP systems.

Cons of SQL:

• Scalability Challenges: Vertical scaling is often required; horizontal sharding is complex.
• Rigid Schema: Schema changes require migrations, which can be costly.
• Not ideal for Unstructured Data: JSON blobs or rapidly changing schema are harder to manage.

SQL vs NoSQL

Difference between SQL and NoSQL

1 Schema Flexibility

SQL - Predefined (Rigid) Schema

In SQL databases like:

• MySQL
• PostgreSQL
• Oracle Database

You must define the structure before inserting data.

That structure is called a schema.

Example: Users Table in SQL

CREATE TABLE Users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

This means:

Every row must follow this structure:

What Happens If Requirements Change?

Now suppose product team says:

“We need to store user's Instagram handle.”

You must modify the schema:

ALTER TABLE Users ADD COLUMN instagram_handle VARCHAR(100);

Problems:

• Requires migration
• Locks table (in large systems)
• Risky in production
• Needs deployment coordination
• Time-consumng for huge datasets

This is what we mean by:

SQL requires predefined schema and schema changes are complex.

NoSQL – Dynamic (Flexible) Schema

In NoSQL databases like:

• MongoDB
• Cassandra
• DynamoDB

You dont' need a fixed structure for all records.

Each document (row) can have different fields.

Example: Users Collection in MongoDB

Insert first user:

{
  "id": 1,
  "name": "Alice",
  "email": "alice@email.com"
}

Insert second user:

{
  "id": 2,
  "name": "Bob",
  "email": "bob@email.com",
  "instagram_handle": "@bob_insta"
}

Notice:

• No schema modification needed
• Documents can have different fields
• No migration required

This is schema flexibility.

Real-World Scenario Example:

E-commerce Product Example

SQL Approach:

CREATE TABLE Products (
    id INT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    size VARCHAR(10),
    color VARCHAR(20)
);

Problem:

What if:

• Some products have size
• Some don't
• Some have warranty
• Some have battery life
• Some have RAM
• Some have material type

You end up with:

• Many nullable columns
• Complex schema changes
• Multiple related tables

NoSQL Approach:

{
  "id": 101,
  "name": "T-Shirt",
  "price": 19.99,
  "size": "M",
  "color": "Blue"
}

{
  "id": 102,
  "name": "Laptop",
  "price": 1200,
  "RAM": "16GB",
  "battery_life": "8 hours"
}

No schema changes needed.

Each product stores only relevant fields.

Why SQL Schema Changes are Complex

In large systems:

• Tables may have millions or billions of rows
• Schema change may lock table
• Requires migration scripts
• May need downtime
• Must maintain backward compatibility

In enterprise environments, schema changes can take weeks.

Why NoSQL Is Flexibile

Because:

• Data stored as JSON-like documents
• Fields are not fixed
• No ALTER TABLE required
• Easier iteration during early development

But Flexibility Has Tradeoffs

SQL Advantages

• Strong structure
• Data consistency
• Clear relationships
• ACID guarantees
• Better for complex jons

NoSQL Risks

• No enforced structure
• Inconsistent documents
• Harder data validation
• Can become messy at scale

Flexibility ≠ always better

2 Storage Model

SQL:

SQL stores data in tables.

Think of it like an Excel sheet:

Users Table

Each row:

• Represents one record
• Must follow the same schema

Each column:

• Has a defined data type
• Enforce constraints

Relationships (Core Strength)

SQL databases shine at relationships:

Example:

Orders Table

Here:

• user_id references Users.id
• Enforces via foreign key

This enables:

SELECT Users.name, Orders.product
FROM Users
JOIN Orders ON Users.id = Orders.user_id;

SQL is optimized for:

• Joins
• Structured relationships
• Strong consistency

NoSQL – Multiple Storage Models

Unlike SQL, NoSQL is not noe single model.

It includes several data storage models:

1. Key-Value
2. Document
3. Wide-Column
4. Graph

Each solves different problems.

3 Scalability

SQL:

It traditionally vertical scales (Scale-Up)

Relational databases were originally designed to scale vertically, which is increasing power of a single server:

• More CPU
• More RAM
• Faster SSD
• Better hardware

Example:

Server with:
8 GB RAM → upgrade → 64 GB RAM

Why SQL Traditionally Scales Vertically

Because:

• Strong ACID guarantess
• Complex joins
• Foreign key constraints
• Transactions across tables

These are easier to maintain one a single powerful machine.

Limitations:

• Hardware has limits
• Expensive
• Single point of failure (if not replicated)
• Harder to scale to millions of writes/sec

Although modern SQL systems support sharding and replication, scaling horizontally is more complex.

NoSQL:

Designed for Horizontal Scaling (Scale-Out)

NoSQL databases like:

• MongoDB
• Apache Cassandra
• Amazon DynamoDB

were built to scale horizontally from the beginning.

Horizontal Scaling is adding more servers:

Server 1
Server 2
Server 3
Server 4

Data is distributed across machines.

This is done by Sharding and Partitioning.

Why NoSQL Scales Horizontally Easily:

Because:

• Schema is flexible
• Fewer joins
• Often no complex cross-node transactions

4 Transaction Support

SQL: Strong ACID Transactions (Built-In)

Relational databases are built around ACID transactions.

What is ACID?

ACID stands for:

1. Atomicity: All operations succeed or none do
2. Consistency: Database remains in a valid state
3. Isolation: Concurrent transactions don't interfere
4. Durability: Once committed, data is permanent

Example: Bank Transfer (SQL)

You transfer $100 from Account A to Account B.

Steps:

1. Deduct $100 from A
2. Add $100 to B

If system crashes after step1:

• SQL automatically rolls back
• Money is not lost

Example transaction:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If any step fails -> ROLLBACK

This is extremely reliable.

Why SQL is Strong Here:

Because relational databases:

• Use write-ahead logs
• Lock rows/tables
• Enforce constraints
• Support isolation levels
• Guarantee consistency across multiple tables

Ideal for:

• Banking
• Payments
• Inventory systems
• Financial records

NoSQL: Historicall Limited Transactions

No SQL databases like:

• MongoDB
• Apache Cassandra
• Amazon DynamoDB

were originally designed prioritizing:

• Scalability
• Availability
• Performance

Over strict ACID guarantees.

Early NoSQL Model:

Most NoSQL systems initially supported:

• Atomicity only at single-document level
• No multi-document transactions
• Eventual consistency

Example:

If updating two separate documents:

• One may succeed
• Other may fail
• No automatic rollback

Why:

Because:

• Data is distributed across nodes
• Cross-node transactions are expensive
• Two-phase commit reduces scalability
• CAP theorem trade-offs

Distributed transactions slow down performance.

Modern Reality: NoSQL No