SQL vs NoSQL

Relational rigour versus document, wide-column and key-value flexibility — and when each wins.

IntermediateTheory13 min read

Read these first

CAP Theorem and PACELC

Relational (SQL) strengths

When SQL wins

Complex joins — reports spanning users, orders, and products.
ACID transactions — multi-row updates must succeed or fail together.
Ad hoc queries — analysts run SQL without schema migrations.
Mature tooling — Postgres, MySQL, backups, ORMs, decades of ops knowledge.

Java

-- Natural fit for SQL: relational integrity
CREATE TABLE follows (
  follower_id  UUID NOT NULL REFERENCES users(id),
  following_id UUID NOT NULL REFERENCES users(id),
  created_at   TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (follower_id, following_id)
);

SELECT u.username, COUNT(f.following_id) AS following_count
FROM users u
LEFT JOIN follows f ON f.follower_id = u.id
WHERE u.id = $1
GROUP BY u.username;

StreamHub stores accounts, billing, and follow relationships in Postgres — relationships and constraints matter.

Database selection on AWS

DATABASERDS / AuroraACID · joins

DATABASEDynamoDBkey-value scale

ANALYTICSOpenSearchfull-text · autocomplete

DATABASEElastiCachesessions · rate limits

Pick the store that matches access pattern — not the other way around.

NoSQL categories

Type	Model	Examples	Sweet spot
Document	JSON documents	MongoDB, DynamoDB	Flexible schema, nested objects
Key-value	Opaque key → blob	Redis, DynamoDB	Sessions, cache, simple lookups
Wide-column	Row key + column families	Cassandra, HBase	High write throughput, time-series
Graph	Nodes and edges	Neo4j	Social graphs, recommendations

Document
ModelJSON documents
ExamplesMongoDB, DynamoDB
Sweet spotFlexible schema, nested objects
Key-value
ModelOpaque key → blob
ExamplesRedis, DynamoDB
Sweet spotSessions, cache, simple lookups
Wide-column
ModelRow key + column families
ExamplesCassandra, HBase
Sweet spotHigh write throughput, time-series
Graph
ModelNodes and edges
ExamplesNeo4j
Sweet spotSocial graphs, recommendations

Java

// Document store: video metadata as one document
{
  "video_id": "v_9182",
  "creator_id": "u_42",
  "title": "Sunset timelapse",
  "tags": ["nature", "4k"],
  "stats": { "views": 128400, "likes": 9201 }
}

Side-by-side comparison

Aspect	SQL	NoSQL
Schema	Fixed, enforced	Flexible or schemaless
Scaling	Vertical + read replicas; sharding harder	Built for horizontal partition
Transactions	Full ACID (single node)	Often limited or per-partition
Queries	Rich SQL, joins	Key/path access; limited ad hoc
Consistency	Strong by default	Often tunable eventual

Schema
SQLFixed, enforced
NoSQLFlexible or schemaless
Scaling
SQLVertical + read replicas; sharding harder
NoSQLBuilt for horizontal partition
Transactions
SQLFull ACID (single node)
NoSQLOften limited or per-partition
Queries
SQLRich SQL, joins
NoSQLKey/path access; limited ad hoc
Consistency
SQLStrong by default
NoSQLOften tunable eventual

Polyglot persistence

Production systems use multiple stores. StreamHub's data layer:

StreamHub store map

Postgres — users, subscriptions, follows, payments.
Redis — sessions, rate limits, hot video metadata cache.
S3 — video files and thumbnails (object storage).
Elasticsearch — full-text video search and autocomplete.
Cassandra (at scale) — high-volume view events and analytics writes.

Each store serves one access pattern well. The cost is operational complexity and eventual consistency across boundaries.

Migration and schema evolution

SQL migrations are explicit:

Java

ALTER TABLE videos ADD COLUMN duration_sec INTEGER;
CREATE INDEX CONCURRENTLY idx_videos_creator ON videos(creator_id);

NoSQL still needs schema discipline — "schemaless" often means "schema in application code" with painful drift.

Decision checklist

Java

choose_sql_when:
  - need_multi_row_transactions
  - complex_joins_and_reporting
  - team_strong_in_sql
  - write_qps_under_10k_on_one_primary

choose_nosql_when:
  - massive_write_throughput_per_partition_key
  - flexible_nested_documents
  - global_multi_region_with_tunable_consistency
  - simple_key_lookup_at_billions_of_rows

Common mistakes

Mistake	Better approach
NoSQL because it sounds modern	Match store to access pattern
One MongoDB for everything	Polyglot with clear boundaries
Ignoring transactions on orders	SQL or distributed transactions for money
SQL for billion-row time-series writes	Wide-column or time-series DB

NoSQL because it sounds modern
Better approachMatch store to access pattern
One MongoDB for everything
Better approachPolyglot with clear boundaries
Ignoring transactions on orders
Better approachSQL or distributed transactions for money
SQL for billion-row time-series writes
Better approachWide-column or time-series DB

Quick recall

Everything you need if you only revisit this box.

SQL excels at relations, joins, and ACID; NoSQL at scale and flexible shapes.
NoSQL types: document, key-value, wide-column, graph — each has a niche.
Real systems use polyglot persistence: SQL core + Redis + object storage + search.
StreamHub uses Postgres for relational data, Redis for cache, S3 for media.
"Schemaless" still requires schema governance in application code.
Pick the store per access pattern, not per hype cycle.

Test yourself

Answer these before moving on — recall is what makes it stick.