Databases: SQL vs NoSQL

SQL and NoSQL aren't competing philosophies — they're different tools for different access patterns. Most systems need both. The question is what belongs where.

When to use SQL (relational)

Strong fit for structured, relational data with ACID requirements.

• ›Data has clear relationships and you need JOINs
• ›Transactions matter — money, inventory, bookings
• ›Schema is well-defined and unlikely to change often
• ›You need strong consistency guarantees
• ›Examples: PostgreSQL, MySQL — user accounts, orders, payments

When to use NoSQL

Strong fit for scale, flexibility, or specific access patterns.

• ›Document DB (MongoDB) — flexible schema, hierarchical data, rapid iteration
• ›Key-Value (Redis, DynamoDB) — simple lookup by key, ultra-low latency
• ›Wide-Column (Cassandra) — write-heavy, time-series, massive scale
• ›Graph DB (Neo4j) — relationship-heavy queries like social networks
• ›Search (Elasticsearch) — full-text search, log aggregation

Indexing basics

Indexes make reads fast. They make writes slightly slower.

• ›Index on columns you filter or sort by frequently
• ›B-tree index — default for most DBs, great for range queries
• ›Hash index — perfect for exact lookups, useless for ranges
• ›Composite index — covers multiple columns, order matters
• ›Too many indexes = slow writes and large storage footprint

ACID vs BASE

Two consistency models with real tradeoffs.

• ›ACID: Atomicity, Consistency, Isolation, Durability — SQL default
• ›BASE: Basically Available, Soft state, Eventually consistent — NoSQL default
• ›ACID for financial transactions. BASE acceptable for social feeds, analytics.

Interview tips

• ✓Name specific databases: PostgreSQL, DynamoDB, Cassandra — not just 'a database'
• ✓Justify your choice with access patterns, not familiarity
• ✓Address data modeling: what does the schema look like?
• ✓Mention sharding if scale requires it — and the key strategy

Follow-up questions to expect

• ?How would you shard this database? What's the partition key?
• ?What happens when a database node fails?
• ?When would you switch from SQL to NoSQL for this system?