SQL vs NoSQL — With Real Examples

Almost every SQL-vs-NoSQL interview opens with "what's the difference?" and almost every candidate answers "SQL has schemas, NoSQL doesn't" — which is half-true and completely misses the point. The real difference is how the data is shaped on disk, and everything else (scaling, joins, transactions) flows from that.

"Schemaless" is one of the most misleading marketing words in databases. Every NoSQL store has a schema; the question is who validates it and when.

-- SQL (Postgres) — rigid, declared once
CREATE TABLE users (
  id        BIGSERIAL PRIMARY KEY,
  email     VARCHAR(255) UNIQUE NOT NULL,
  age       INT CHECK (age >= 0),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

// MongoDB — flexible, per-document
db.users.insertOne({
  email: "riya@example.com",
  age: 28,
  preferences: { theme: "dark", lang: "en" }   // new field, no migration
});
db.users.insertOne({
  email: "aman@example.com",
  age: "twenty-eight"      // stored happily — bug ships to prod
});

"NoSQL" is a marketing umbrella that hides four genuinely different storage shapes. Treating MongoDB, Redis, Cassandra, and Neo4j as the same kind of thing is like treating a notebook, a sticky-note pad, a spreadsheet, and a mind-map as "all paper".

A document store treats one record as a self-contained JSON tree. The unit of read, write, and atomicity is the document — and that's exactly the design choice that makes it fast for some things and miserable for others.

-- Postgres: 4 tables, joined at query time
SELECT p.*, v.size, v.color, v.stock, i.url, r.rating, r.text
FROM products p
  JOIN variants v ON v.product_id = p.id
  JOIN images   i ON i.product_id = p.id
  LEFT JOIN reviews r ON r.product_id = p.id
WHERE p.id = 42;

// MongoDB: 1 document, 1 read
{
  _id: "prod-42",
  name: "Mechanical Keyboard",
  price: 8999,
  variants: [
    { sku: "k-red-tkl", color: "red",  size: "TKL", stock: 12 },
    { sku: "k-blk-100", color: "black", size: "100%", stock: 7 }
  ],
  images: ["cdn/k1.jpg", "cdn/k2.jpg"],
  reviews: [
    { user: "riya", rating: 5, text: "Crispy switches" },
    { user: "aman", rating: 4, text: "Loud but lovely" }
  ]
}
db.products.findOne({ _id: "prod-42" })  // done.

Key-value is the simplest possible database: get(key), put(key, value), delete(key). No queries, no joins, no scans (or at least, none you should run). What it gives up in expressiveness it returns in microsecond latency and effortless horizontal scale.

# 1. Session cache (with TTL)
SET session:abc123 '{"userId":42,"role":"admin"}' EX 3600

# 2. Rate limiter — atomic increment with expiry
INCR ratelimit:user:42:minute
EXPIRE ratelimit:user:42:minute 60

# 3. Leaderboard — sorted set, O(log N) writes, O(log N + M) range read
ZADD leaderboard:global 9800 "riya"
ZREVRANGE leaderboard:global 0 9 WITHSCORES   # top 10

# 4. Distributed lock — SET with NX (only if absent) + TTL
SET lock:order:42 "worker-7" NX EX 30

# 5. Pub/Sub for real-time fan-out
PUBLISH chat:room:5 '{"from":"aman","text":"hi"}'

Wide-column stores look like SQL tables on the surface but behave nothing like them underneath. Each row is keyed by a partition key, the partition decides which physical machine owns the data, and inside the partition you can have millions of "columns" sorted by a clustering key. They're optimized for one thing: writing massive amounts of time-ordered data and reading slices of it back, fast.

CREATE TABLE sensor_readings (
  device_id  UUID,
  ts         TIMESTAMP,
  temp       DOUBLE,
  humidity   DOUBLE,
  PRIMARY KEY ((device_id), ts)        -- partition key, clustering key
) WITH CLUSTERING ORDER BY (ts DESC);

-- This is BLAZING fast — single partition, sequential scan, sorted
SELECT * FROM sensor_readings
WHERE device_id = ? AND ts > now() - 1d;

-- This will FAIL or scan every node — no partition key in WHERE
SELECT * FROM sensor_readings WHERE temp > 40;

Graph databases are built for the one thing SQL is genuinely bad at: following relationships, deeply, fast. "Friends-of-friends-of-friends-who-bought-this" is one query in Cypher and a screaming fan-out of self-joins in SQL.

// Find all friends-of-friends of Riya, who like the same band
MATCH (riya:Person {name:"Riya"})-[:FRIEND*2]-(fof:Person),
      (fof)-[:LIKES]->(b:Band)<-[:LIKES]-(riya)
RETURN DISTINCT fof.name, b.name;

// Shortest path between two people
MATCH p = shortestPath(
  (a:Person {name:"Riya"})-[:KNOWS*]-(b:Person {name:"Karthik"})
)
RETURN p;

ACID and BASE aren't opposites; they're two ends of a sliding bar. SQL traditionally lives at the ACID end (correctness first, scale later); NoSQL traditionally lives at the BASE end (scale first, eventual correctness). Both are valid. Picking wrong is a career-defining bug.

"NoSQL doesn't do joins" is correct — and that's a feature, not a flaw, once you understand where the work moved.

-- SQL: write once, join on every read
SELECT a.name, p.title, p.body
FROM authors a JOIN posts p ON p.author_id = a.id
WHERE a.id = 7;

// MongoDB pattern A: embed (author owns posts, posts < 100)
{ _id: 7, name: "Riya", posts: [{title:"..."}, {title:"..."}] }

// MongoDB pattern B: reference (posts unbounded)
{ _id: 7, name: "Riya" }
{ _id: "p1", authorId: 7, authorName: "Riya", title: "..." }   // duplicated

// Now: rename Riya → Riyaa
// SQL: 1 UPDATE on authors, done.
// Mongo: 1 UPDATE on author, plus updateMany on posts to fix authorName.

Both SQL and NoSQL use indexes (usually B-trees, sometimes LSM-trees in NoSQL). The mental model is the same — maintain a sorted lookup structure for fast finds — but the cost profile is very different.

If you only remember one thing about why NoSQL exists: SQL was designed for one machine; NoSQL was designed for a thousand. Everything else is consequence.

Two orthogonal techniques, often confused. Sharding splits data across machines so each holds a slice. Replication copies data across machines so each replica holds the same slice. Real systems do both.

# 1. Hash sharding — uniform, but no range scans
shard = murmur3(user_id) % numShards

# 2. Range sharding — fast range scans, but hot spots if keys cluster
shard = (user_id < 1M) ? S0 : (user_id < 2M) ? S1 : S2

# 3. Geographic / tenant sharding — data locality
shard = (region == "IN") ? mumbai : (region == "US") ? virginia : ...

The CAP theorem says: in a distributed system, when a network partition (P) happens, you must choose between Consistency (C) and Availability (A). Since partitions will happen at some point, real systems are either CP or AP.

Theory's nice; let's design the same feature both ways. A user places an order with multiple items, payment, shipping, and order history. Compare on the same page.

CREATE TABLE orders (
  id BIGSERIAL PK,
  user_id BIGINT,
  status TEXT,
  total NUMERIC,
  created_at TIMESTAMPTZ
);
CREATE TABLE order_items (
  id BIGSERIAL PK,
  order_id BIGINT FK,
  product_id BIGINT,
  qty INT, price NUMERIC
);
CREATE TABLE payments (...);
CREATE TABLE shipments (...);

-- one ACID transaction
BEGIN;
  INSERT INTO orders ...;
  INSERT INTO order_items ...;
  INSERT INTO payments ...;
  UPDATE inventory SET qty=qty-?;
COMMIT;
      

{
  _id: "ord_42",
  userId: 7,
  status: "PAID",
  items: [
    {productId:1, name:"Keyboard",
     qty:1, price:8999},
    {productId:2, name:"Mouse",
     qty:1, price:1499}
  ],
  payment: {method:"UPI", txn:"abc"},
  shipment: {addr:"...", eta:"..."},
  total: 10498,
  createdAt: "2026-05-08"
}
db.orders.insertOne(...)   // 1 op, 1 doc
      

Reach for SQL (Postgres is the safe default in 2026) when any of these are true:

• The data has rich relationships that you'll query in many ways. Foreign keys are friends.
• Strong consistency is non-negotiable — money, inventory, bookings, anything regulated.
• Ad-hoc analytical queries are a thing — finance, BI, internal dashboards.
• The schema is stable — domain is well understood, fields rarely change.
• Your scale fits one machine (this is most apps — modern Postgres comfortably does 50K+ TPS).
• You need referential integrity at the DB level (no orphan rows, cascades on delete).
• The team is small and you want fewer moving parts. One Postgres beats five "right-tool" databases.

Reach for NoSQL when any of these are true — and ideally more than one:

• Massive write throughput — IoT, telemetry, clickstream (>100K writes/sec sustained).
• Single-key access pattern dominates — sessions, user profiles, product detail by ID.
• Schema is genuinely flexible — different records have different fields (CMS, multi-tenant SaaS).
• Data scale exceeds a single machine — petabytes, hundreds of millions of users.
• Geographic distribution — multi-region writes, low-latency edge serving.
• The data shape is the workload — graph for relationships, KV for cache, time-series for metrics.
• You're already paying the operational cost — a team that knows how to run Cassandra is a serious investment.

Real production systems rarely pick "one database". They pick the right database per workload and stitch them together. This is called polyglot persistence, and it's the modern norm — not an anti-pattern.

┌──────────────┐  ┌──────────────┐  ┌──────────────┐
│  Postgres    │  │   MongoDB    │  │    Redis     │
│  (system of  │  │  (catalog +  │  │  (sessions,  │
│  record:     │  │   reviews)   │  │   cart,      │
│  orders,     │  │              │  │   rate-      │
│  payments,   │  │              │  │   limits)    │
│  users)      │  │              │  │              │
└──────┬───────┘  └──────┬───────┘  └──────────────┘
       │ CDC             │ search-sync
       ▼                 ▼
┌──────────────┐  ┌──────────────┐
│ Elasticsearch│  │  ClickHouse  │
│ (full-text   │  │  (analytics  │
│  search)     │  │   + BI)      │
└──────────────┘  └──────────────┘

Learning from how scaled companies actually combine SQL and NoSQL is more useful than any flowchart.

Every team eventually considers "moving from SQL to NoSQL" (or back). The migration is rarely the database's fault — it's the access patterns and the application code.

• Pitfall 1 — Lift-and-shift fails. Copying a normalized SQL schema into MongoDB collections gives you the worst of both worlds: no joins, no schema enforcement, same write amplification. Redesign for the access pattern.
• Pitfall 2 — Ignoring the shard key. The shard/partition key is the most important schema decision. Pick wrong and the second migration follows a year later.
• Pitfall 3 — Lost ACID invariants. Foreign keys, CHECK constraints, and triggers in Postgres encode business rules. If you move off SQL, those rules now live in app code — every service, every consumer.
• Pitfall 4 — Reporting goes dark. "Run a quick SQL query for the CFO" becomes a Spark job. Always plan an analytics path (CDC → warehouse) before you migrate.
• Pitfall 5 — Operational maturity. Running Postgres is well-understood by every DBA on the planet. Running a 30-node Cassandra ring at 99.99% is a specialized skill. Hire or train before you migrate, not after.

Short, sharp answers to the questions that come right before "do you have any questions for us?".

Functionally close, yes. JSONB columns + GIN indexes + pgvector + full-text + partitioning let it do most NoSQL tricks. It's still relational at heart, but "Postgres for everything" is a defensible architecture in 2026.

Multi-document transactions are slower; cross-collection joins ($lookup) are awkward; ad-hoc reporting is harder; and "schemaless" becomes "schema-everywhere-in-app-code". For relational data with strong invariants, Postgres is simpler.

MongoDB is a document store with rich querying (find, aggregation pipeline) and you run the cluster. DynamoDB is a managed KV / wide-column hybrid with rigid access by partition+sort key — less flexible queries, but zero operational overhead and predictable latency at any scale.

Massive, sustained write throughput; multi-datacenter writes with tunable consistency; time-series or event-log workloads; queries always known in advance. Cassandra punishes you for ad-hoc queries; rewards you for write-heavy known-pattern workloads.

Both. With AOF persistence enabled it's a durable store. In practice, most teams use it as a cache + ephemeral data store and back the data with Postgres or DynamoDB for the system of record.

Distributed databases that offer SQL + ACID at horizontal scale. Spanner, CockroachDB, YugabyteDB, TiDB. They use Paxos/Raft + SQL planners to give you the relational model without the single-machine ceiling — at the cost of higher write latency than a single-node Postgres.

Read-after-write surprises. User updates their profile, refreshes the page, sees the old value because the read hit a stale replica. Fix by routing post-write reads to the primary, or by using a "read-your-writes" session token.

Trick question. Partitions happen, so you always pay P. The real choice is C-or-A during a partition. CP refuses requests; AP serves stale ones.

Write down your top 5 queries before you write any DDL. The database that makes those queries cheap is the right one. Everything else — schema, indexes, scaling — flows from that.