NoSQL Decision Guide — Which Store, and When

The NoSQL folder has depth on MongoDB, Elasticsearch, Cassandra individually (and DynamoDB lives under AWS). This file is the layer above: the four NoSQL families, when each fits, and — the answer interviewers actually want — when the right NoSQL choice is not to use NoSQL.

The four families

(Search engines like Elasticsearch and vector DBs are sometimes lumped in as “NoSQL” too — they’re really a separate “specialized index” category. See nosql/elasticsearch/.)

The decision — start by not assuming NoSQL

The single most important framing: a relational database (Postgres) is the right default for most backend workloads. It gives you ACID transactions, joins, ad-hoc queries, mature tooling, strong consistency, and constraints. You should be able to justify choosing NoSQL — not default to it.

Reach for NoSQL when relational hits a specific wall:

Do you need ad-hoc queries, joins, multi-row transactions, strong consistency?
  → Yes → Postgres. (Most apps. Don't overthink it.)
  → No, or only for part of the data → consider NoSQL, by access pattern:

  - Need a sub-ms cache / sessions / counters / rate limits?
        → Key-value (Redis)
  - Storing semi-structured entities, schema evolves often, queried by id / nested fields?
        → Document (MongoDB) — but note Postgres JSONB does a lot of this too
  - Write throughput beyond one relational primary, known query patterns, time-series?
        → Wide-column (Cassandra / DynamoDB)
  - The queries are fundamentally about *relationships and traversal*?
        → Graph (Neo4j)

Family-by-family — when each genuinely wins

Key-value

Wins when the access is purely “get/set by key” at high QPS and low latency. Caching in front of any database, session storage, rate-limit counters, feature flags, leaderboards (Redis sorted sets). DynamoDB extends this to a durable, massively-scalable KV/document store with no connection management — the natural pick for “huge scale, simple key access, on AWS.” Loses when you need to query by anything other than the key.

Document

Wins when entities are naturally nested/hierarchical, the schema varies or evolves, and you query mostly by id or by indexed fields within the document. Content management, product catalogs, user profiles with varying shapes.

The honest caveat: Postgres JSONB columns give you document storage inside a relational database — flexible nested data plus joins, transactions, and constraints when you need them. So “I need flexible schema” is often not a reason to leave Postgres. The real reason to choose MongoDB is usually horizontal scale + a genuinely document-shaped workload, not flexibility alone.

Wide-column

Wins at write throughput and data volume that a single relational primary can’t sustain, when the query patterns are known up front. Time-series, event logs, sensor data, messaging history. The cost: you design the schema around the queries (no ad-hoc querying, no joins), and you trade strong consistency for tunable/eventual consistency. Cassandra for self-managed/multi-region; DynamoDB for the managed AWS version of the same model.

Graph

Wins when the value is in the relationships and queries are traversals: “friends of friends,” “what else did people who bought this buy,” “find the fraud ring connecting these accounts,” “what’s the parent-of-parent-of in this org tree.” These are queries that become painful multi-join recursive SQL — a graph DB makes them natural. Loses for everything that isn’t relationship-traversal — don’t use a graph DB as a general-purpose store.

Consistency, scale, and the trade-offs

NoSQL stores generally trade something for scale:

• Consistency — many default to eventual consistency; you opt into stronger (DynamoDB strong reads, Cassandra QUORUM, Mongo write concerns) at a latency cost. Relational gives you strong consistency by default.
• Transactions — limited or constrained. Mongo has multi-document transactions (4.0+) but they’re a fallback, not the default; DynamoDB transactions cap at a number of items and cost 2× capacity; Cassandra has lightweight transactions but they’re expensive. None match Postgres’ general ACID transactions.
• Joins — generally none. You denormalize, embed, or do application-side joins. This is a modeling cost you pay up front.
• Ad-hoc queries — relational lets you query any which way after the fact; NoSQL stores reward queries you designed the schema for and punish the ones you didn’t.

The senior framing: NoSQL buys you scale and flexibility by spending consistency, transactions, joins, and query flexibility. That’s a good trade for specific workloads and a bad trade for a general-purpose application database.

Polyglot persistence — the realistic answer

Mature systems rarely pick one store. They use the right tool per workload:

• Postgres as the system of record (orders, users, money — needs ACID).
• Redis in front of it for caching, sessions, rate limits.
• Elasticsearch for full-text search over the catalog.
• DynamoDB for a specific massive-scale, simple-access-pattern table (an activity feed, a device-state table).
• Maybe a graph DB for the recommendation subsystem.

The interview answer to “SQL or NoSQL?” is usually “both — per workload. Postgres is the default and the system of record; specific access patterns get a specialized store when they outgrow what relational does well.”

Common gotchas

• Defaulting to NoSQL “because it scales” — most apps never hit relational’s scaling wall; you give up transactions, joins, and ad-hoc queries for a problem you don’t have.
• “Flexible schema” as the reason for MongoDB — Postgres JSONB gives flexible nested data with transactions and joins. The real reason for Mongo is scale + document-shaped workload.
• Wide-column without knowing the queries — Cassandra/DynamoDB make you design the schema around the access patterns; an unknown future query is expensive or impossible to serve.
• Graph DB as a general store — it’s brilliant for traversal queries and mediocre for everything else.
• Forgetting the consistency trade — picking an eventually-consistent store for data that needs strong consistency (balances, inventory) is a correctness bug.
• One store for everything — both “Postgres for literally everything” at extreme scale and “NoSQL for everything” are usually wrong; polyglot per workload is the mature answer.

Interview angle

• “SQL or NoSQL — how do you decide?” — start by justifying leaving relational, don’t default away from it. Postgres is the right default for most workloads (ACID, joins, ad-hoc queries, constraints). Reach for NoSQL when a specific access pattern hits a relational wall — then pick the family by that pattern: key-value for high-QPS key lookups, document for nested evolving entities, wide-column for write-throughput at scale, graph for relationship traversal.
• “What are the four NoSQL families?” — key-value (Redis, DynamoDB — caching, sessions, simple lookups), document (MongoDB — nested semi-structured entities), wide-column (Cassandra — massive writes, known query patterns, time-series), graph (Neo4j — relationship-traversal queries). Search/vector engines are a separate specialized-index category.
• “What does NoSQL trade away for scale?” — generally consistency (eventual by default, strong is opt-in with a latency cost), transactions (limited or constrained), joins (none — you denormalize), and ad-hoc query flexibility (you design the schema around known queries). It buys scale and flexibility by spending exactly what relational is good at.
• “‘We need a flexible schema’ — does that mean MongoDB?” — not necessarily. Postgres JSONB gives flexible nested documents inside a relational database, keeping transactions, joins, and constraints. The genuine reason to choose MongoDB is horizontal scale plus a truly document-shaped workload — flexibility alone isn’t it.
• “When is a graph database the right call?” — when the queries are fundamentally about relationships and traversal — friends-of-friends, recommendation paths, fraud rings, deep hierarchy traversal — the kind of query that becomes painful recursive multi-join SQL. For anything that isn’t traversal-shaped, a graph DB is the wrong general-purpose store.
• “How do real systems answer SQL-vs-NoSQL?” — polyglot persistence: Postgres as the system of record for anything needing ACID, Redis for caching/sessions, Elasticsearch for search, DynamoDB for a specific massive-scale simple-access table — the right tool per workload, not one store for everything.