DBMS deep dive
SQL vs NoSQL — When to Use What: deep dive
SQL vs NoSQL is really a question of access patterns and scaling limits, not old versus new. A strong answer starts from the queries the system has to serve and the consistency it needs, and only then picks a data model.
Start from the queries, not the data
Relational databases model the data and let you ask almost any question later through joins and ad-hoc SQL. Most NoSQL stores make you model the queries up front. In DynamoDB or Cassandra you design the partition key around the exact lookups the app will make, and a query you did not plan for may need a new table or index. That is the real trade: query flexibility in exchange for predictable performance at very large scale.
Worked example: one feature, two designs
Take a feature that shows the latest 50 messages in a chat room. In PostgreSQL you would write a messages table with an index on (room_id, sent_at DESC). In Cassandra you would design the table around that one query:
-- PostgreSQL: normalized, flexible, joins available
CREATE TABLE messages (
id BIGSERIAL PRIMARY KEY,
room_id BIGINT NOT NULL REFERENCES rooms(id),
author_id BIGINT NOT NULL REFERENCES users(id),
body TEXT NOT NULL,
sent_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON messages (room_id, sent_at DESC);
-- Cassandra (CQL): table shaped for one query, no joins
CREATE TABLE messages_by_room (
room_id uuid,
bucket text, -- e.g. '2026-10' keeps partitions bounded
sent_at timeuuid,
author_name text, -- denormalized copy, no join to users
body text,
PRIMARY KEY ((room_id, bucket), sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);The Cassandra version spreads writes across nodes and serves the query from one partition. But if a user changes their name, every old message keeps the stale author_name until you rewrite it. 'Show all messages by user X' would need a second table. The relational version answers both with one index, and it is the right choice until one primary can no longer keep up.
A decision table
| Requirement | Leans toward | Why |
|---|---|---|
| Multi-row invariants (money, inventory) | Relational | Cross-row ACID transactions and constraints |
| Ad-hoc analytics and reporting | Relational or a warehouse | Joins, aggregates, a mature optimizer |
| Huge write throughput, simple key access | Wide-column (Cassandra, ScyllaDB) | Log-structured storage, leaderless replication |
| Self-contained documents with varied shapes | Document (MongoDB) or Postgres JSONB | Read or write the whole aggregate at once |
| Sub-millisecond lookups, ephemeral data | Key-value (Redis) | In-memory data structures |
| Multi-hop relationship queries | Graph (Neo4j) | Follows edges directly instead of making repeated joins |
| Predictable latency at any scale, serverless | DynamoDB | Managed partitioning, single-digit-ms key lookups |
Consistency is a spectrum, not a label
'NoSQL means eventual consistency' is outdated. Cassandra lets you choose per query: with replication factor 3, writing and reading at QUORUM gives R + W > N, so every read overlaps the latest write. DynamoDB offers strongly consistent reads on request. MongoDB has multi-document transactions. Meanwhile a Postgres read replica is eventually consistent too. PACELC is a useful frame here: during a partition you choose availability or consistency, and the rest of the time you still trade latency against consistency.
Scaling also is not a binary choice. Postgres scales reads with replicas and writes with partitioning, or with sharding through tools like Citus. Distributed SQL systems such as CockroachDB, Spanner and YugabyteDB offer SQL with horizontal writes, at the cost of higher commit latency.
Common mistakes
- Picking NoSQL for 'scale' at a data size one Postgres instance handles comfortably.
- Using a document store for highly relational data and then rebuilding joins in application code.
- Calling NoSQL 'schemaless'. The schema still exists, but it lives in application code (schema-on-read), and every reader has to handle old shapes.
- Unbounded partitions in Cassandra or DynamoDB, such as all events for one user forever, which create hot partitions.
- Ignoring operations: backups, migrations and the tooling your team already knows.
Interview questions
›What is the difference between schema-on-write and schema-on-read?
Schema-on-write, as in SQL, rejects bad data at insert time through types and constraints. Schema-on-read, as in most document stores, accepts any shape and leaves the interpretation to readers. Evolution is easier, but versioning moves into application code.
›What is a hot partition, and how do you avoid it?
It is one partition key that receives a large share of the traffic, so one node becomes the bottleneck. Avoid it with high-cardinality keys, write sharding (adding a suffix such as key#0..N), and time bucketing for time-series data.
›Why do NoSQL designs denormalize, and what does it cost?
Without joins, one query should hit one partition, so data is copied into each table that needs it. The cost is write amplification and keeping the copies consistent, often through async fan-out or change streams.
›When is Postgres JSONB a better choice than MongoDB?
When most of the data is relational but some attributes vary, such as product specs. JSONB with a GIN index gives flexible fields alongside joins, foreign keys and transactions in one system.
›How would you migrate a growing Postgres app that is hitting write limits?
First rule out the cheaper fixes: indexes, batching, connection pooling and table partitioning. Then shard by a tenant or user key, using Citus or application-level routing, or move the hot write path, such as events, to a store built for append-heavy load.