How do you decide between a relational database and a NoSQL database for a new project?
Quick Answer
Start from the data's actual shape and the application's real consistency/query needs, not from technology trends: relationships that need strong consistency and ad-hoc querying favor relational; highly variable or nested data, extreme write scale, or a query pattern that's almost always "fetch one whole object" favor NoSQL. Most real systems end up polyglot — a relational core for transactional data, with NoSQL stores layered in for the specific workloads that genuinely benefit from them.
Detailed Answer
This question is really asking whether you reason from first principles about data and access patterns, or default to whatever's currently fashionable. A strong answer walks through concrete decision criteria rather than a blanket preference.
Questions worth asking before choosing
- How relational is the data, really? Genuine many-to-many relationships queried from multiple angles favor a relational model with real joins; data that's naturally self-contained per record (a user profile with nested preferences) favors a document model.
- What consistency guarantee does the business logic actually need? Financial balances, inventory that must never oversell, anything where "briefly stale" causes real harm — favors relational (or a NoSQL system explicitly configured for strong consistency). Social counters, activity feeds, caches — tolerate eventual consistency fine.
- How variable is the schema across records? A fixed, well-understood structure fits relational tables comfortably; wildly varying attributes per record (a multi-category product catalog) fit documents more naturally.
- What's the expected write scale and growth trajectory? Genuinely massive, horizontally-distributed write volume (far beyond what a scaled-up relational primary plus read replicas can handle) points toward systems built for it from the ground up (Cassandra-style wide-column, or a relational system with mature sharding tooling like Vitess/Citus).
- What does the team already know, and what's the operational maturity of the tooling around each option? A team deeply experienced with PostgreSQL, with mature migration/backup/monitoring tooling already in place, pays a real (and often underestimated) cost adopting an unfamiliar NoSQL system for marginal technical benefit — operational familiarity is a legitimate factor, not just a technical purity question.
Why "it depends" is genuinely the right answer, done well
This isn't hedging — it's recognizing that "relational vs. NoSQL" is really a bundle of several independent decisions (consistency model, schema flexibility, query pattern, scale characteristics), and different parts of the same system can reasonably land on different answers. A candidate who says "always use Postgres" or "NoSQL scales better so use that" without qualification is skipping the actual analysis; a candidate who walks through 2-3 concrete factors and reaches a specific recommendation for the scenario at hand is demonstrating real judgment.
A worked example
"For an e-commerce platform: orders, payments, and inventory go in a relational database — they need strong consistency (an order total must be right, inventory must not oversell) and benefit from real joins for reporting. But the product catalog, with wildly different attributes per category (books have authors/ISBNs, electronics have wattage/warranty terms), fits a document store better. Session data and shopping carts fit a key-value store like Redis — fast, ephemeral, no need for durability guarantees as strong as the order data." This kind of concrete, per-domain reasoning is what distinguishes a strong answer from a generic one.