SQL vs NoSQL

When You Need SQL vs NoSQL

Choosing the right database is one of the most impactful decisions in system design. The wrong choice leads to painful migrations. In interviews, you must justify your database choice based on data model, access patterns, and scale requirements.

What It Is

SQL databases (PostgreSQL, MySQL) store data in structured tables with schemas and support ACID transactions with SQL queries. NoSQL databases (MongoDB, Cassandra, Redis, DynamoDB) use flexible data models (document, key-value, column-family, graph) and prioritize scalability over strict consistency.

How It Works

SQL databases organize data into tables with fixed schemas. You define tables, columns, types, and relationships (foreign keys). Data is queried using SQL, which supports filtering, joining, aggregating, and sorting.

NoSQL databases have no fixed schema — each record can have different fields. This flexibility speeds up development but shifts data validation to the application layer. NoSQL databases scale by distributing data across many nodes (sharding by key).

The Decision Framework

• SQL strengths: Structured data, complex queries (JOIN), ACID transactions, mature ecosystem, well-understood scaling patterns (read replicas, sharding).
• NoSQL strengths: Flexible schema, horizontal scaling, high write throughput, various data models for specific use cases.
• Document stores (MongoDB): JSON-like documents. Great for: user profiles, product catalogs, content management.
• Key-value stores (Redis, DynamoDB): Simple get/set by key. Great for: caching, session storage, leaderboards.
• Column-family (Cassandra): Wide columns, distributed. Great for: time-series data, event logs, IoT data.
• Graph databases (Neo4j): Nodes and edges. Great for: social networks, recommendation engines, fraud detection.

What the Industry Uses

Instagram uses PostgreSQL for user data and photos metadata — ACID matters for user accounts.

Netflix uses Cassandra for viewing history (billions of records, high write throughput) and PostgreSQL for billing (ACID required).

Uber uses a mix: PostgreSQL for trips/payments, Redis for caching/geofencing, Cassandra for analytics.

Performance and Tradeoffs

• Schema rigidity: SQL enforces schema at the database; NoSQL enforces at the application.
• Scalability: NoSQL scales horizontally more easily; SQL requires more effort (sharding).
• Query flexibility: SQL supports complex ad-hoc queries; NoSQL often requires denormalization and specific access patterns.
• Consistency: SQL provides strong consistency; most NoSQL offers eventual consistency.

Mistakes Engineers Make

1. Choosing NoSQL because it is trendy — SQL is often the better default
2. Not considering access patterns before choosing a database
3. Using a single database for all use cases — polyglot persistence is often the right approach

Practice These Interview Questions

1. When would you use SQL vs NoSQL?
2. What are the different types of NoSQL databases?
3. How does a document database differ from a relational database?
4. Can you use both SQL and NoSQL in the same system?

Original DosiBridge notes for SQL vs NoSQL

This section is written specifically for this page, not copied from a general system-design checklist. The useful way to study SQL vs NoSQL is to connect the concept to a data path where correctness, access pattern, and future migrations matter more than benchmark numbers. That framing changes the design discussion from definitions to operating choices.

What makes this topic distinct

• Query Shape: For SQL vs NoSQL, decide the query shape before choosing tools; otherwise the design looks clean but fails when sql changes.
• Write Amplification: For SQL vs NoSQL, decide the write amplification before choosing tools; otherwise the design looks clean but fails when vs changes.
• Replication Lag: For SQL vs NoSQL, decide the replication lag before choosing tools; otherwise the design looks clean but fails when nosql changes.
• Migration Safety: For SQL vs NoSQL, decide the migration safety before choosing tools; otherwise the design looks clean but fails when sql changes.
• Data Ownership: For SQL vs NoSQL, decide the data ownership before choosing tools; otherwise the design looks clean but fails when vs changes.

Distinct angle: decision record for SQL vs NoSQL

For SQL vs NoSQL, the original decision should be written as a data ownership record: which aggregate owns the truth, which queries must be transactional, which reads can be projected, and what migration would be needed if the first database choice becomes wrong. This is different from listing database products. The strongest design answer explains the shape of the workload and the cost of changing storage later.

Production review question

If a team adopts SQL vs NoSQL, what observable signal proves it helped instead of only making the architecture diagram more fashionable?

Practical metric to track

Track sql-nosql health as a named dashboard or review item: combine one user-facing metric, one saturation metric, and one correctness metric. For SQL vs NoSQL, this keeps the discussion grounded in behavior that can be measured after deployment instead of generic advice that sounds good in an interview but never gets verified.

Further Reading

• ACID Transactions
• Database Sharding
• Database Types
• Database Scaling
• CAP Theorem