SQL vs NoSQL

Beginner

Data Management

SQL databases (relational) provide ACID transactions, structured schemas, and powerful joins. NoSQL databases (document, key-value, wide-column, graph) offer flexible schemas, horizontal scalability, and optimised access patterns at the cost of some consistency guarantees.

Overview

SQL databases (PostgreSQL, MySQL, Oracle) store data in tables with fixed schemas, enforce relationships via foreign keys, and support ACID transactions and complex joins. They excel when data is structured, relationships are complex, and strong consistency is required. NoSQL databases emerged to handle web-scale workloads where relational databases hit scaling limits. They come in four main types: document stores (MongoDB — flexible JSON documents), key-value stores (Redis, DynamoDB — fast lookups by key), wide-column stores (Cassandra, HBase — massive write throughput), and graph databases (Neo4j — relationship-heavy queries). NoSQL databases typically sacrifice some ACID guarantees for horizontal scalability and flexible schemas. The choice depends on your data model, query patterns, consistency requirements, and scale needs. Many modern systems use polyglot persistence — different databases for different services.

SQL — Relational Databases

Relational databases enforce schemas, support joins across tables, and provide ACID transactions. They are the default choice when data integrity and complex queries matter.

SQL — schema, joins, ACID transactions

// SQL strengths: ACID, joins, schema enforcement
// PostgreSQL / MySQL

CREATE TABLE orders (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    total      DECIMAL(10, 2) NOT NULL,
    status     VARCHAR(20) DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Complex join + aggregation
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > NOW() - INTERVAL '1 year'
GROUP BY u.id
HAVING SUM(o.total) > 1000
ORDER BY lifetime_value DESC;

-- ACID transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both or neither

NoSQL — Types & Use Cases

Each NoSQL type is optimised for a specific access pattern. Document stores for flexible objects, key-value for fast lookups, wide-column for massive write throughput, and graph for relationship traversal.

Multi-language — four NoSQL types

// 1. Document Store (MongoDB)
// Flexible schema, nested objects, good for content/catalogs
db.products.insertOne({
  _id: "prod-123",
  name: "Laptop",
  specs: { ram: "16GB", cpu: "M2" },  // nested document
  tags: ["electronics", "sale"],        // arrays
  reviews: [{ user: "u1", rating: 5 }] // embedded 1:N
});

// 2. Key-Value Store (Redis / DynamoDB)
// Ultra-fast lookups, sessions, caching
SET session:abc123 '{"userId":"u1","role":"admin"}' EX 3600

// 3. Wide-Column Store (Cassandra)
// Massive write throughput, time-series, IoT
CREATE TABLE sensor_data (
  sensor_id TEXT,
  timestamp TIMESTAMP,
  value DOUBLE,
  PRIMARY KEY (sensor_id, timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);

// 4. Graph Database (Neo4j)
// Social networks, recommendations, fraud detection
MATCH (u:User)-[:FOLLOWS]->(f:User)-[:PURCHASED]->(p:Product)
WHERE u.id = 'user-123'
RETURN p.name, COUNT(f) AS friend_buyers
ORDER BY friend_buyers DESC;

Decision Framework

Choose SQL when you need ACID, complex queries, and strong consistency. Choose NoSQL when you need horizontal scale, flexible schemas, or your access pattern maps perfectly to a NoSQL data model.

Conceptual — SQL vs NoSQL decision matrix

// Decision matrix
//
// Requirement                | SQL         | NoSQL
// ─────────────────────────────────────────────────
// ACID transactions          | ✅ Native   | ❌ Limited (some: Cosmos, Fauna)
// Complex joins & queries    | ✅ Strong   | ❌ Denormalise instead
// Fixed schema enforcement   | ✅ Strict   | ⚠️ Schema-on-read
// Horizontal write scaling   | ⚠️ Hard     | ✅ Native (Cassandra, DynamoDB)
// Flexible/evolving schema   | ❌ Migrations| ✅ Schema-less
// Low-latency key lookups    | ⚠️ Okay     | ✅ Optimised (Redis: < 1ms)
// Massive time-series writes | ❌ Bottleneck| ✅ Cassandra, InfluxDB
// Relationship traversal     | ⚠️ Slow joins| ✅ Graph DBs (Neo4j)
//
// Polyglot persistence example (e-commerce):
// Orders + Payments → PostgreSQL (ACID)
// Product catalog   → MongoDB (flexible schema)
// Session store     → Redis (fast, TTL)
// Activity feed     → Cassandra (write-heavy, time-series)
// Recommendations   → Neo4j (graph)

Key Points to Remember

1SQL: ACID transactions, strong schema, complex joins — best for structured data with relationships.
2NoSQL: four types — document, key-value, wide-column, graph — each optimised for specific access patterns.
3NoSQL scales horizontally more easily; SQL requires sharding which adds complexity.
4Most large systems use polyglot persistence — the right database for each use case.
5Start with SQL unless you have a specific reason to choose NoSQL (scale, schema flexibility, access pattern).

Interview Questions

When would you choose NoSQL over SQL?

EasyTCS

What are the four types of NoSQL databases?

EasyInfosys

How does MongoDB handle transactions compared to PostgreSQL?

MediumAmazon

Design the database layer for an e-commerce platform — which databases would you use and why?

MediumFlipkart

When would you use Cassandra over DynamoDB?

HardNetflix

Ask Aria about SQL vs NoSQL

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Database Replication

Consistent Hashing