SQL vs NoSQL — how do you actually decide for a new service?

#Why "it depends" isn't a good enough answer

Every backend candidate has heard this question, and most answer with the same tired script: "SQL is for structured data, NoSQL is for unstructured data." That line doesn't hold up under any pressure — plenty of relational schemas store semi-structured JSON columns, and plenty of document databases enforce a very rigid shape at the application layer. A senior answer needs to talk about access patterns, consistency guarantees, and operational shape, not vocabulary from a marketing deck.

The real question is never "which database is better" — it's "which database matches the problem I actually have." That means understanding what each family optimizes for, and running your specific requirements through a short decision framework instead of a gut feeling.

#What each family is actually built to optimize

A relational database (Postgres?, MySQL, SQL Server) is built around a fixed schema, strong typing, declarative constraints, and multi-table joins resolved by a query planner. Its superpower is enforcing invariants across many entities at once — foreign keys, unique constraints, multi-row transactions — while still letting you ask ad hoc questions of the data with SQL.

NoSQL isn't one thing; it's four different families that solve different problems:

• Document stores (MongoDB?, DynamoDB in some modes) — store semi-structured, often nested documents keyed by ID. Great when each entity is naturally self-contained and read/written as a whole.
• Key-value stores (Redis, DynamoDB) — extreme throughput for simple get/put by key. No query language, no joins, just lookups.
• Wide-column stores (Cassandra, HBase) — built for massive write throughput and horizontal scale, at the cost of flexible querying (you design tables around your query patterns, not the other way around).
• Graph databases (Neo4j) — optimized for traversing relationships many levels deep, something SQL joins get expensive at quickly.

#A decision framework you can actually use in an interview

1. What does the data look like, and how does it change? A fixed relational shape with lots of cross-entity queries (orders that belong to customers that have addresses) points at SQL. Documents that vary shape per record and are read/written whole (a user profile, a product catalog entry) point at a document store.
2. What's the consistency requirement? If you need multi-row, multi-table transactions with strict correctness (money movement, inventory decrements, anything where "read your own write" and cross-row atomicity matter), lean relational/ACID. If eventual consistency is acceptable (a like counter, an activity feed), a NoSQL store buys you availability and horizontal scale in exchange.
3. What's the query pattern? Ad hoc reporting, joins across entities you didn't anticipate at design time → SQL. A small number of known access patterns at very large scale (fetch user by ID, fetch messages by conversation ID) → key-value or wide-column, designed around exactly those patterns.
4. What's the scale and shape of growth? Many NoSQL stores are built to shard horizontally out of the box. Relational databases traditionally scale vertically or need explicit sharding/read-replica strategy — though this line has blurred a lot with distributed SQL engines that give you horizontal scale without giving up SQL or transactions.
5. What does your team already run well? Operational maturity is a real cost. A team that has run Postgres in production for five years will ship a NoSQL migration slower and buggier than staying relational, even if NoSQL is theoretically a better fit.

#The CAP theorem, briefly, because it will come up

The CAP theorem says that under a network partition, a distributed system must choose between consistency and availability — it can't have both. Most NoSQL stores are explicit about which side of that line they sit on (Cassandra defaults to AP, MongoDB defaults to CP-ish with tunable consistency), while a single-node relational database sidesteps the question because there's no partition to worry about until you distribute it. This is a common follow-up question, so know which side your chosen store falls on and why it matters for your use case.

#Comparison table

Most real systems end up polyglot anyway: Postgres for the transactional core, Redis for caching and session state, Elasticsearch for full-text search, and maybe Kafka or a wide-column store for high-volume event ingestion. The interview answer isn't "pick one forever" — it's showing you can reason about which storage engine fits which slice of the problem.

#Quick check

🧠 Check your understanding
0/3 · 0/3 answered
1. A team needs to store user session data with extremely high read/write throughput, no need for joins, and eventual consistency is acceptable. Which storage type fits best?
A graph databaseA key-value storeA wide-column store designed for analyticsA normalized relational schema
2. Under the CAP theorem, what must a distributed database choose between during a network partition?
Consistency and AvailabilityConsistency and DurabilityAtomicity and IsolationAvailability and Durability
3. Why is 'NoSQL means no schema' a misleading interview answer?
NoSQL databases cannot store JSONSchema only matters for graph databasesMost NoSQL stores still have an implicit schema enforced at the application layer, it's just not declared to the databaseNoSQL databases always enforce schemas identically to SQL