“It depends on the use case.”
This phrase ends more system design answers than any other. It sounds thoughtful. It signals nuance. And it is the single most effective way to tell an interviewer you don’t know how to make a decision.
Every technology choice depends on the use case. That’s not an answer — it’s a preamble. The answer is what it depends on, and how you decide.
Here’s the decision framework for choosing between relational and non-relational storage that works in an interview setting.
Step 1: Write down your two hottest queries
Before naming any storage technology, write down the two or three queries the system will run most frequently. Specifically: what are the inputs, what are the outputs, and what constraints do they have?
“Give me all messages for user X, ordered by time” — this is a range query on a partition key. Wide-column store handles this well.
“Give me all orders for account X with status=pending, joined to the product table” — this has a join. Relational database handles this better.
“Give me the 10 nearest restaurants within 5km of this coordinate” — this is a spatial query. PostGIS or a geohash index.
The storage decision follows from the query. The query never follows from the storage decision.
Step 2: Apply the decision rules
Use a relational database when:
Your data is structured and schema is stable
You need ACID transactions across multiple rows or tables
Your queries involve joins, aggregations, or complex filtering
Your write volume is under roughly 5–10K writes per second per node
Your total data per table is under roughly 100M rows before sharding gets painful
Use a wide-column store (Cassandra, HBase) when:
You need high write throughput (100K+ writes per second)
Your queries are predictable and partition-key-based — no joins, no ad-hoc filtering
Your data is naturally time-ordered (events, messages, metrics)
You need built-in TTL on rows (messages that expire, session data)
Use a key-value store (Redis, DynamoDB) when:
Your reads are pure point lookups by a single key
You need sub-millisecond latency
You’re storing simple values or small objects
You need atomic counters or sorted sets
Use object storage (S3) when:
You’re storing large binary objects (files, images, video, backups)
You need very high read throughput via CDN
Latency of 50–200ms per request is acceptable
Step 3: Name the anti-pattern you’re avoiding
The most powerful version of a storage decision isn’t “I’d use Cassandra.” It’s “I’d use Cassandra instead of Postgres here because the write volume — 50,000 events per second — will exceed what a single Postgres instance can handle, and the queries are all partition-key-based so I don’t need the join capability I’d be giving up.”
The comparison forces you to show you considered the alternative. The reason forces you to show you understand the trade-off.
The one rule that overrides everything
If you need a multi-row ACID transaction — an operation where either all rows change or none do — you need a relational database or a relational-compatible distributed database (CockroachDB, Spanner). No amount of Cassandra tuning gives you true ACID across multiple partition keys.
Payment systems almost always need ACID. Account balances must be updated atomically. Inventory reservation must be atomic. Order creation and payment capture must be atomic.
“I’d use Cassandra for the payment ledger” is a common answer that fails almost every payment system design question. The interviewer will ask “what happens if the debit succeeds but the credit fails?” and there is no good answer with Cassandra.
Know what you’re trading away before you trade it.
CTA: The Stripe Payments walkthrough goes deep on exactly this decision — why the ledger needs ACID, how double-entry accounting makes the atomicity requirement explicit, and what happens at the infrastructure level when you enforce it. Paid post #2.
Interested in mastering this topic?
Unlock premium content with deeper explanations and real implementation patterns.
Subscription link
https://systemdr.systemdrd.com/subscribe
—Sumedh
The Question Vault has all 52 walkthroughs organized by archetype — so you can see the pattern across questions, not just the surface answer.
Access all 52 Questions here