SQL vs NoSQL

Introduction

SQL vs NoSQL is not a moral choice — it's a data model fit question. Relational ACID vs document/wide-column/graph for access patterns and scale shape.

The story

A team picked Mongo for financial transactions needing multi-row ACID. They spent 6 months building app-level transactions. Postgres would have been week one. Match the database to access pattern and consistency needs.

The business problem

Teams that skip disciplined SQL vs NoSQL thinking pay in outages, cost overruns, and failed interviews:

• Outages: components chosen without scale math fail at peak.
• Cost: over-engineered microservices for 100-user products.
• Latency: missing cache/CDN/replica on read-heavy paths.
• Interviews: boxes without numbers and trade-offs don't hire.

The problem teams faced

This lesson addresses:

• When and why SQL vs NoSQL matters in real architectures.
• How to sketch components and data flows under interview time pressure.
• Trade-offs vs alternatives — what senior engineers articulate aloud.
• Production patterns and failure modes you've seen or will see.

Understanding the topic

Core idea: SQL vs NoSQL in production system design.

• Problem — what force this topic addresses.
• Building blocks — components typically involved.
• Trade-offs — what you gain and what you pay.
• Interview — how this appears in design loops.

Internal architecture

SQL vs NoSQL — system view:

Access pattern analysis
   ↓
Structured + joins + ACID → SQL (Postgres)
Flexible schema + horizontal scale → Document (Mongo)
High write throughput + time series → Wide-column (Cassandra)
Relationships as first-class → Graph (Neo4j)

Visual explanation

Three diagrams: architecture flow, design process, and scaling lens:

Informative example

Example — SQL vs NoSQL:

E-commerce orders + line items + inventory → SQL
User activity feed blob per user → Document
Sensor metrics at 1M writes/sec → Wide-column
Social graph "friends of friends" → Graph or SQL with care

Execution workflow

Real-world use

Used in production at major tech companies and every FAANG system design interview loop. Patterns align with AWS/GCP well-architected frameworks and Google SRE practice.

Production case study

A team picked Mongo for financial transactions needing multi-row ACID. They spent 6 months building app-level transactions. Postgres would have been week one. Match the database to access pattern and …

• Context: production or interview scenario from this lesson.
• Decision: component and trade-off choices explained.
• Outcome: measurable latency, availability, or cost impact.

Trade-offs

• Pro: structured approach reduces outages and interview failures.
• Con: upfront thinking takes time — faster than wrong rebuild.
• Con: every product has unique constraints — adapt templates.

Decision framework

• Always estimate before drawing microservices.
• Match consistency model to business domain (money vs likes).
• Document rejected alternatives — ADR or interview closing.

Best practices

• State assumptions explicitly (DAU, read:write ratio).
• Draw async vs sync paths with different line styles.
• Close with monitoring and on-call failure modes.

Anti-patterns to avoid

• Jumping to Kafka and microservices without scale justification.
• Single DB box with no read replica or cache on read-heavy design.
• Ignoring idempotency on write APIs with retries.

Common mistakes

• Underestimating peak QPS (forgetting peak factor).
• Hot shard from poor partition key choice.

Debugging tips

• Trace one request ID through diagram — find missing component.
• Ask "what fails first at 10×?" for every design.

Optimization strategies

• Cache + CDN first for read-heavy; shard when single DB saturates.
• Async queue for slow side effects (email, analytics).

Common misconceptions

• More components ≠ better design — simplest meeting NFRs wins.
• Interviews test process — not memorizing Netflix architecture.

Advanced interview questions

Summary

You can explain SQL vs NoSQL in a 45-minute system design interview with numbers, diagrams, and trade-offs. Teach it back without notes — you own this piece.