Relational vs NoSQL Selection

The request for proposal opens with 'we need a scalable, modern database'. Three weeks later the schema is in Cassandra, every read is a multi-partition scan, and reporting needs a nightly export to Postgres because nobody can join anything. The failure was procedural: the store was chosen before the queries were written. The discipline that avoids it is blunt — before evaluating any database, write the top five access patterns with their latency budgets, then ask which engine serves them natively.

The classification that falls out of doing this:

Relational strengths are worth stating precisely because interviews ask for them: joins execute set-wise over indexes and statistics; transactions give ACID across rows and tables (the transfer invariant lives here); constraints (foreign keys, unique, check) enforce invariants in the database, so a buggy deploy cannot corrupt relationships; schema evolution is DDL with rollback; and decades of optimizer maturity mean ad-hoc queries you never anticipated still run. The costs are equally real: write-heavy single-table throughput hits lock contention, vertical scaling hits a ceiling, and schema changes on huge tables need careful migrations.

The trap to name in an interview: 'NoSQL because we will outgrow Postgres' is an argument without arithmetic. Postgres with read replicas, connection pooling (PgBouncer), partitioned tables, and Citus-style sharding handles an enormous amount of write traffic — most systems that 'needed to move to Cassandra' actually needed a partitioning strategy, not a different consistency model. When the jump is real, you are buying horizontal partitioning and availability semantics at the price of transactions, joins, and declarative constraints — prices that must be re-paid in application code (idempotency keys instead of unique constraints, application-level referential checks, two-phase reads).

Key-value deserves its own row because it is the simplest contract with the biggest constraint: get(k), put(k,v), maybe scan(prefix). If your access patterns are genuinely that — session stores, shopping carts, rate-limit counters, feature flags — a key-value store is both faster and simpler than a relational table pretending to be one. If the patterns include 'and then join to users and aggregate by region', you have answered your own question: keep it relational, or accept that the join moves into a service that now owns the denormalized copy.

Interview framing: state the method (queries and budgets first), classify the workload in one sentence, and be ready with the feature list you are trading away. With the method in place, the next chapters examine the two most common jumps: embedding data in documents, and reaching for graph storage when traversals dominate.

A product catalog page needs the product, its variants, its reviews summary, and its category path. In Postgres that is four joins (or a denormalized view someone must maintain). In MongoDB the natural move is to nest everything inside one product document and fetch it in a single findOne — the document store's core promise: the aggregate is the unit of storage, so the read that loads the aggregate is one round trip to one shard. That promise holds until the aggregate stops fitting the model.

The rules for deciding, concretely:

EMBED when:
  - children are 1:few and read WITH the parent (variants on a product)
  - children have no independent lifecycle (no separate updates by other docs)
  - the combined size stays well under the 16 MB document limit (MongoDB)
  - you never query children without the parent

REFERENCE when:
  - 1:many with unbounded growth (reviews, orders per user, events per account)
  - children are updated independently or concurrently
  - you must query or index children alone
  - two parents would duplicate the child and diverge

The size limit is the funhouse mirror: 16 MB sounds generous until users attach unlimited images and the document hits the wall mid-update (BSONObj size limit errors, and worse, a document that grows every month until one write path fails in production). Unbounded arrays are the classic mistake — embed 'last 100 orders' as a rolling window (a bounded, documented design), never 'all orders forever'. Two-phase embedding (bucket patterns: orders_2026_03 documents of ~500 orders each) is the standard workaround for time-series growth inside documents.

What document stores give up, in the columns interviews care about:

Two operational consequences follow. Flexible schema means schema drift: product A stores price as a number, product B as {amount, currency} — six months later every reader has an if (typeof ...) branch. Fix with schema validation at write time (JSON Schema validators in MongoDB, or a service-layer contract) — flexibility of storage is not an excuse for vagueness of contract. Denormalization means a write amplification decision: updating a review summary embedded in the product means one write in one document (atomic, nice) — but if reviews live elsewhere, the projection must be kept fresh by an event or a background job, which is CQRS's territory.

Also worth knowing: many 'document' stores are now hybrid (Postgres jsonb gives relational tables with JSON flexibility and GIN indexes; MongoDB added joins, transactions, and relational-ish querying). A pragmatic answer — start relational with a jsonb escape hatch, move an aggregate out only when its read pattern and growth justify the split — is often the correct one, and it keeps Polyglot Persistence a deliberate choice rather than a default. The workload family that needs a genuinely different engine, though, is traversal-heavy — covered next.

The feature is 'show friends of friends who liked this post, excluding people I already follow, ranked by interaction'. In SQL this is four self-joins over follows and likes — at 10M users the planner produces a plan that materializes millions of intermediate rows, and p95 lands at 4 seconds. In a graph database the same query is a walk: start at the user node, follow FOLLOWS edges twice, follow LIKES edges, filter. The difference is index-free adjacency: each node physically stores pointers to its neighbors, so a traversal costs the number of edges visited, not a join across indexed tables that must re-locate every hop.

That is the precise claim, and it is worth being exact about it in an interview: a graph database does not 'do joins better'; it avoids joins by making relationships first-class stored data. When the workload is dominated by multi-hop queries — social networks, fraud rings (shared devices/accounts across layers), recommendation paths, dependency and lineage graphs, permission hierarchies — the cost profile inverts: SQL grows with intermediate result size, graph grows with traversal depth times fan-out.

Know the failure modes before recommending one. Graph databases are poor at bulk analytics — scanning every node to aggregate totals is a full table scan wearing a different name; route that to the warehouse via CDC. Deep traversals with high fan-out still explode: 'friends of friends of friends of friends' on a celebrity node visits millions of edges regardless of engine — add depth limits and precomputed aggregates. Updating edge-heavy structures (rewriting a million relationship rows) is as expensive as anywhere else. And you do not need a graph store to run one traversal: a small adjacency list cached in Redis or precomputed 'friends-of' tables answer many 'social' features at far lower operational cost — reach for the graph engine when traversals are a core, frequent, and variable-depth part of the product, not when a one-hop query needs a JOIN.

A worked example pins it down: a delivery platform keeps orders and addresses in Postgres (multi-row invariants: inventory, payments, foreign keys), sessions in Redis (key-value with TTL), the catalog's nested variants in MongoDB (one aggregate per product page), the courier-to-route traversal graph in Neo4j (variable-depth path queries per dispatch), and clickstream events in ClickHouse (columnar scans that would wreck the OLTP cluster). Five shapes, five engines, five cost models — and one pipeline keeping them fed, which is what Polyglot Persistence has to manage.

Selection summary to deliver in a design interview: write the queries, then map shapes — multi-entity invariants and ad-hoc SQL to relational; key-point lookups to key-value; aggregate-shaped reads with bounded nesting to documents; variable-depth path queries to graph; append-only metrics to time-series. Each choice also names what you are declining: constraints and joins (document/key-value), scale-out writes (relational), and transactional breadth (graph/analytics). Different shapes coexisting in one product is normal — that is Polyglot Persistence, next.