Database Selection Guide

There is no perfect database — you choose based on access patterns + consistency needs + scale.

Interviewers want to see your thought process, not just tech names.

---

Decision Tree#

Workload	Choose
Read-heavy	Cache + Read replicas + SQL/NoSQL
Write-heavy	NoSQL (Cassandra, DynamoDB), sharded SQL
Analytics	OLAP DB (BigQuery, Snowflake)
Complex queries/joins	SQL (PostgreSQL, MySQL)
Key-value lookups	Redis, DynamoDB
Document-based data	MongoDB, Couchbase
Time-series	TimescaleDB, InfluxDB
Graph relations	Neo4j

---

Start With Access Patterns#

Ask yourself:

• Do I need joins?
• Do I need transactions?
• Is consistency or availability more important?
• Do I have high write throughput?
• Will the data grow horizontally?

This alone eliminates 70% of the options.

---

SQL vs NoSQL#

Choose SQL when:#

• Strong consistency needed
• Joins and relational queries
• ACID transactions required
• Moderate scale but correctness is paramount
• Example: payments, orders, banking

Choose NoSQL when:#

• Need horizontal scaling
• High write throughput
• Flexible schema required
• Eventual consistency is acceptable
• Example: feeds, logs, messaging, analytics, IoT

---

NoSQL Types#

1. Key-Value (Redis, DynamoDB)#

• Ultra-fast lookups
• Sessions, caching, tokens, hot data

2. Document Store (MongoDB)#

• Semi-structured data
• User profiles, products, JSON blobs

3. Columnar Store (Cassandra, BigTable)#

• High write scale
• Time-series, logging, metrics

4. Graph DB (Neo4j)#

• Friends-of-friends, recommendations, fraud graphs

---

Consistency vs Availability (CAP Thinking)#

Need	Choose
CA (Consistency + Availability)	Single-region SQL, financial transactions
CP (Consistency + Partition tolerance)	MongoDB (majority writes), HBase, Zookeeper
AP (Availability + Partition tolerance)	Cassandra, DynamoDB, systems prioritizing uptime

---

Interview Answer Template#

Use this structure in every database selection question:

Step 1: Start With Access Patterns#

"First, let's look at the access pattern. This system primarily needs [read/write-heavy? joins? transactions? time-series? key-value?]."

Step 2: State the Consistency Need#

"The system needs [strong / eventual / tunable] consistency, especially for [critical part]."

Step 3: Justify SQL vs NoSQL#

• SQL: "The data is structured, relationships matter, and transactions are important."
• NoSQL: "We need horizontal scaling, flexible schema, and high write throughput."

Step 4: Tie It to the Use Case#

"For [user profiles / orders / payments] — integrity matters → SQL (PostgreSQL)." "For [feeds / logs / analytics] — scale matters → NoSQL."

Step 5: Name the DB + Two Reasons#

"I'd choose [database] because: [Reason 1 — high write throughput, ACID, etc.] [Reason 2 — horizontal scaling, low-latency lookups, etc.]"

Step 6: Mention Polyglot Persistence#

"We don't need a single DB. For best performance:

• [DB A] for [component]
• [DB B] for [component]"

Bonus#

"We can add Redis in front for hot reads and reduce DB load. We'll monitor with slow query logs + read replicas + auto-scaling."

---

Example: Instagram-like Feed#

Q: Which DB for an Instagram-like feed?

A: The system is write-heavy and needs horizontal scaling. Posts are append-only and strong consistency is not required for the feed. A column-family NoSQL store like Cassandra fits because it offers high write throughput, tunable consistency, and time-series storage.

For user profiles and relational data, I'd still use PostgreSQL — it gives ACID transactions, powerful indexing, and clean relational modeling without needing massive horizontal scaling.