SQL vs NoSQL (High-Level Primary Store)

What it is

This note compares Postgres (OLTP SQL), DynamoDB (managed wide-column/key-value), Cassandra (wide-column, peer-to-peer), and Redis as if they were a primary store—at a high level only; exact choice depends on access patterns and ops constraints.

When to use what (conceptual)

Store	Strengths as primary (when it fits)
Postgres	Relational model, transactions, joins, constraints, rich indexing; default for many products needing ACID and ad hoc queries.
DynamoDB	Known partition/sort key access, predictable scale with AWS, serverless ops; single-table modeling discipline.
Cassandra	Very high write throughput, multi-region active-active style patterns, time-ordered wide rows; tunable consistency.
Redis	Sub-millisecond reads when entire working set fits RAM; often better as cache or ephemeral primary (sessions, leaderboards) than general system of record—persistence options exist but operational profile differs from Postgres.

Mental model

SQL (Postgres): normalize or denormalize inside one engine with transactions across rows.
Dynamo / Cassandra: partition key drives locality; avoid cross-partition transactions unless product explicitly supports them.
Redis: key-value + data structures; primary only when durability and query patterns are fully understood.

  Postgres:     flexible queries + FK + TX
  Dynamo:       get/put/query by designed keys + GSIs
  Cassandra:    partition-wide ordered rows + TTL
  Redis:        in-memory speed + simple structures

Alternatives

NewSQL / distributed SQL (CockroachDB, Spanner): SQL + horizontal scale—trade ops complexity and latency.
Document DB (MongoDB): flexible schema, document-shaped workloads—compare to Postgres JSONB tradeoffs.

Failure modes

Wrong access pattern for the database: hot partitions on Dynamo/Cassandra; missing index on Postgres.
Treating Redis as durable Oracle without backup/HA story.
Joins across services when you split bounded contexts—sometimes move logic to app or use CQRS.

Interview talking points

Start from data model and queries, not buzzwords; mention RPO/RTO and back-of-envelope for size and QPS.
If you mention NoSQL, say how you enforce invariants (compensating transactions, idempotency, design for concurrency).
Connect read scaling to replication and sharding; cache with caching.

On this page