SQL vs NoSQL

When to prefer SQL (relational)

Strong schema and constraints, mature ACID transactions across rows/tables.
Complex relationships and joins; referential integrity matters.
Rich ad‑hoc queries, window functions, sophisticated optimizers.

Common picks: PostgreSQL, MySQL, SQL Server.

When to prefer NoSQL

Flexible schemas and fast iteration on data models.
Natural horizontal scale with partitioning built in.
Simple key/document access at very low latency; high write throughput.

Common picks: MongoDB (document), DynamoDB (key‑value), Cassandra (wide‑column), Redis (in‑memory structures).

Decision axes

Transactions: need multi‑row/table atomicity? SQL shines. Some NoSQL support transactions but with caveats.
Query shape: ad‑hoc and joins → SQL. Stable access by key/range/aggregation → NoSQL.
Scale: RDBMS can scale up and shard with effort; NoSQL often bakes in partitioning and replication.
Consistency: strong by default (SQL) vs tunable/eventual in many NoSQL systems.

Data modeling examples

Orders and payments: normalized schema with foreign keys; use transactions to update order + ledger atomically.
User profile with flexible attributes: document model (JSON) with partial indexes on hot fields.
Activity feed: append‑only event store (NoSQL or relational with partitioned table) + materialized views for per‑user timelines.

-- language-sql
-- Relational: strong constraints for orders and payments
BEGIN;
  INSERT INTO orders(id, user_id, total_cents, status)
  VALUES ($1, $2, $3, 'PENDING');
  INSERT INTO payments(id, order_id, amount_cents, provider_ref)
  VALUES ($4, $1, $3, $5);
  UPDATE orders SET status = 'PAID' WHERE id = $1;
COMMIT;

// language-javascript
// NoSQL: document with evolving schema and partial indexes (MongoDB)
db.users.createIndex({ "email": 1 }, { unique: true })
db.users.createIndex({ "attributes.tier": 1 })
db.users.insertOne({
  _id: ObjectId(),
  email: "user@example.com",
  schemaVersion: 3,
  attributes: { tier: "pro", timezone: "UTC" }
})

Patterns that work well

Polyglot persistence: use SQL for the transactional core; use NoSQL for cache, session, events, full‑text, time‑series.
Change Data Capture (CDC) and streams to maintain derived materialized views in another store.

Schema evolution

SQL: use forward‑compatible migrations (add nullable columns, backfill, then enforce NOT NULL).
NoSQL: version documents (schemaVersion) and write adapters; backfill in the background.

Common mistakes

Picking NoSQL to dodge data modeling. You will re‑implement constraints in app code.
Forcing SQL to behave like a time‑series or key‑value store at huge scale. Use a specialized engine.

Bottom line: start from workload requirements (transactions, queries, scale, consistency). The right answer is often “both” with a clean boundary.

Tooling checklist

SQL: EXPLAIN/ANALYZE, query plans, pg_stat_statements, proper indexes, partitioning.
NoSQL: partition key selection reviews, hot‑partition dashboards, TTLs, read/write capacity alarms, backup/restore drills.

-- language-sql
-- PostgreSQL: detect slow queries and missing indexes
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Relational databases are like spreadsheets with strict rules (columns, types, relationships). NoSQL stores are like labeled folders of documents—flexible, but you must agree on how to file and find things.

Example scenario

Checkout system: SQL for orders/payments (atomic, auditable). DynamoDB/Redis for carts and sessions (fast, flexible). Kafka for events; Elastic for search. Each tool fits a job.

FAQ

“Can I do joins in NoSQL?” You denormalize or join in application code; some engines support limited server‑side joins.
“Will SQL scale?” Yes, with partitioning and read replicas; many workloads never need to shard.
“Migrations scary?” Use forward‑compatible migrations and feature flags for safe rollout.

Try it (query plan)

-- language-sql
EXPLAIN ANALYZE SELECT *
FROM orders o
JOIN payments p ON p.order_id = o.id
WHERE o.user_id = $1 AND o.created_at >= now() - interval '30 days';

*SQL vs NoSQL