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.

SQL databases are relational databases that stores data in tables consisting of rows and columns. Relationships between different tables are maintained using keys (primary keys and foreign keys). SQL databases follow the ACID properties, which ensure strong reliability and consistency.

• Atomicity – A transaction is treated as a single unit; it either completes fully or fails completely.
• Consistency – The database always moves from one valid state to another, maintaining all defined rules and constraints.
• Isolation – Transactions are executed independently without interfering with each other.
• Durability – Once a transaction is committed, the data remains permanently stored even in case of system failures.

Because of ACID guarantees, SQL databases provide strong consistency, which makes them suitable for systems like banking, financial systems, and transactional applications.

Why NoSQL Databases Were Introduced

As modern applications grew in scale (e.g., social media, big data systems, real-time applications), traditional relational databases faced some limitations:

• Difficulty handling massive volumes of unstructured or semi-structured data
• Schema rigidity (tables must be predefined)
• Challenges with horizontal scalability across distributed systems
• Performance issues with extremely large datasets

To address these challenges, NoSQL databases were introduced.

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.
   1. Structure of the data (the tables, columns, and their data types) must be defined before inserting data.
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.

NoSQL Database

NoSQL databases are non-relational databases designed to handle large-scale, distributed, and flexible data models. Unlike SQL databases, they do no require a fixed table schema and can store data in multiple formats.

Common types of NoSQL databases include:

1 Key-Value Stores

• Data is stored as key-value pairs
• The key is a unique identifier (usually a string)
• The value can be a string, number, JSON object, list, or any complex structure.
• Example: Redis, AWS DynamoDB

2 Document Databases

• Store data in document formats like JSON or BSON.
• Example: MongoDB.

3 Column-Family Databases

• Store Data in columns rather than rows, optimized for large-scale analytics.
• Example: Apache Cassandra

4 Graph Databases

• Designed to represent relationships using nodes and edges.
• Example: Neo4j

BASE Properties in NoSQL

Unlike SQL databases that prioritize strict consistency (ACID), many NoSQL databases follow the BASE model, which focuses more on scalability and availability in distributed systems.

BASE stands for:

• Basically Available: The system guarantees availability, meaning it will always respond to requests, even if the data is not perfectly consistent at that moment.
• Soft State: The system state may change over time, even without new imputl due to eventual synchronization between nodes.
• Eventually Consistent: Data will become consistent across the system over time.

This approach allows NoSQL databases to achieve high availability and horizontal scalability, which is essential for modernb large-scale applications.

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-consuming 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