caduh

SQL vs NoSQL Databases — The 2‑Minute Guide

3 min read

A high-level comparison of relational and non-relational databases with common use cases for each.

TL;DR

  • SQL (relational): strong consistency, structured schema, rich joins/ACID transactions, great for reporting and integrity (think: money, orders, users).
  • NoSQL (non-relational): flexible schemas and horizontal scaling; great for high‑throughput, variable shapes, and large aggregates (events, sessions, content feeds, IoT).
  • Start with SQL unless you have clear scale/shape needs; add NoSQL per workload (polyglot persistence).

Mental model (speed-run)

  • Relational (SQL): tables ↔ rows; normalized data; relations via foreign keys; query with SQL; ACID transactions.
  • NoSQL umbrellas:
    • Document (MongoDB, Couchbase): JSON-like docs; denormalize and read in one go.
    • Key‑Value (Redis, DynamoDB as KV): superfast lookups, caching, sessions.
    • Wide‑Column (Cassandra, Bigtable): petabyte scale, time‑series/event logs, predictable access patterns.
    • Graph (Neo4j, JanusGraph): nodes/edges; path queries, recommendations, network analysis.

Typical fits

Choose SQL when…

  • You need strong consistency and multi‑row transactions (payments, inventory).
  • Your data model is well‑structured and you rely on ad hoc analytics and joins.
  • You want mature tooling (ORMs, BI, migrations) and portable SQL.

Choose NoSQL when…

  • You have massive write throughput, hot partitions, or global scale (logs, telemetry, feeds).
  • Entities are document‑shaped and best read without joins.
  • Access patterns are narrow and known ahead (KV lookups, time‑series appends).
  • You need schema flexibility and rapid iteration.

Data shaping & queries

SQL example (order with join):

SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '7 days';

Document example (MongoDB, one doc per order with embedded items):

db.orders.find(
  { createdAt: { $gte: new Date(Date.now() - 7*24*60*60*1000) } },
  { _id: 1, total: 1, customerEmail: 1, items: 1 }
);
  • SQL optimizes for relations and set operations.
  • Document databases optimize for aggregate boundaries (what you fetch together lives together).

Scaling & transactions (quick compare)

| Topic | SQL (relational) | NoSQL (non-relational) | |---|---|---| | Transactions | ACID, multi‑row/statement | Often single‑item atomicity; some offer multi‑document with caveats | | Scaling | Vertical + read replicas; sharding supported by many modern engines | Horizontal/partitioned by default (KV/wide‑column); auto‑scale on managed offerings | | Consistency | Strong by default | Often tunable or eventual (per read/write) | | Joins | Native, optimized | Usually application‑side; design to avoid them | | Schema | Explicit with constraints | Flexible; constraints at app level |

CAP & consistency (30 seconds)

  • CAP trade‑off under partitions: you choose Availability vs Consistency for distributed writes.
  • SQL engines can scale with strong consistency (e.g., leader/follower); many NoSQL systems favor availability + partition tolerance with eventual consistency.
  • Many platforms provide tunable consistency (read‑your‑writes, quorum reads) to pick per operation.

Cost & operations

  • SQL: predictable performance, powerful indexes, but joins over huge shards can be costly; migrations require care.
  • NoSQL: cheaper linear scale for simple access patterns; denormalization saves read cost but duplicates data; careful with hot keys and unbounded partitions.

Common use cases

SQL: OLTP for commerce, user/accounts, financial ledgers, booking systems, back‑office reporting, CMS metadata.
Document: user profiles, product catalogs, content pages, events with variable shape.
KV / Cache: sessions, feature flags, rate limits, leaderboards.
Wide‑column / TSDB: metrics, logs, IoT telemetry, time‑series dashboards.
Graph: social networks, dependency maps, fraud/risk link analysis.

Cloud quick map (non‑exhaustive)

  • SQL (managed): Amazon RDS/Aurora, Cloud SQL (Postgres/MySQL), Azure SQL, Neon, PlanetScale.
  • Document: MongoDB Atlas, Firebase/Firestore, Couchbase Capella.
  • KV / Wide‑column: DynamoDB, Bigtable, Cassandra (Astra), Redis Enterprise.
  • Graph: Neo4j Aura, Amazon Neptune, Azure Cosmos DB (Gremlin).

Pragmatic playbook

  1. Start with Postgres/MySQL for core business data.
  2. Add Redis for caching/queues/sessions.
  3. Introduce NoSQL per hotspot (e.g., events → Cassandra/Bigtable; denormalized views → Mongo/Firestore).
  4. Keep one source of truth; use streams (CDC/Kafka) to sync read models.
  5. Monitor p95/p99 latency, hot partitions, and index health before re‑architecting.

One‑minute decision guide

  • Need transactions + joins + ad hoc SQL? → SQL.
  • Need massive scale with simple access patterns or flexible docs? → NoSQL.
  • Need both? → Polyglot: SQL for system of record, NoSQL for specialized reads/writes.

Footnote: “NewSQL” & HTAP

  • Systems like CockroachDB, YugabyteDB, and TiDB aim for SQL and horizontal scale.
  • HTAP (Hybrid Transaction/Analytical Processing) engines blur OLTP/OLAP lines—useful, but still plan for workload isolation.
← Back to all posts