SQL vs NoSQL

Introduction

Choosing between SQL (relational) and NoSQL databases is one of the highest-impact HLD decisions. SQL systems (PostgreSQL, MySQL) offer ACID transactions, joins, and mature tooling. NoSQL spans document (MongoDB), wide-column (Cassandra), key-value (Redis, DynamoDB), and graph (Neo4j) — each optimized for specific access patterns.

Interviewers want trade-off reasoning, not tribal loyalty. Start with access patterns and consistency needs; pick the store that matches read/write shape, not the logo you know best.

This lesson provides a decision framework, polyglot persistence patterns, and when to combine SQL + NoSQL in one system.

Understanding the topic

Key concepts

• SQL: structured schema, ACID, complex queries, vertical + read-replica scale, strong consistency default.
• Document NoSQL: flexible schema, horizontal scale, embed related data, eventual consistency options.
• Wide-column: write-heavy, time-series, massive partition scale (Cassandra, HBase).
• Key-value: O(1) lookups, caching, sessions (Redis, DynamoDB).
• Polyglot persistence: orders in PostgreSQL, product catalog in Elasticsearch, sessions in Redis.
• CAP awareness: NoSQL often chooses AP for partition tolerance; SQL leans CP on primary.

flowchart TB
  subgraph SQL
    PG[(PostgreSQL)]
  end
  subgraph NoSQL
    Mongo[(MongoDB)]
    Redis[(Redis)]
  end

Internal architecture

Architecture overview

flowchart TB
  subgraph SQL
    PG[(PostgreSQL)]
  end
  subgraph NoSQL
    Mongo[(MongoDB)]
    Redis[(Redis)]
  end

Step-by-step explanation

1. Map each entity to access pattern: relational reports → SQL; product JSON blob → document store.
2. Use SQL as system of record for money, inventory, accounts requiring ACID.
3. Use Cassandra/DynamoDB for high-write feeds, metrics, click streams.
4. Redis for ephemeral hot data; not primary durable store without persistence config.
5. Sync via CDC (Debezium) or events from SQL to search/analytics indexes.
6. Abstract data access behind repository interfaces to allow store migration.

Informative example

Polyglot persistence in Spring — JPA for orders, Redis for session, Mongo optional for catalog flexibility:

spring:
  datasource:
    url: jdbc:postgresql://db:5432/shop
    hikari:
      maximum-pool-size: 20
  data:
    redis:
      host: redis
      port: 6379
  mongodb:
    uri: mongodb://mongo:27017/catalog

# Repository split by bounded context
# OrderRepository -> JPA (ACID checkout)
# ProductReadRepository -> Mongo (flexible attributes)
# SessionStore -> Redis (TTL sessions)

Interview script: 'Financial transactions in PostgreSQL; search index in Elasticsearch fed by Kafka CDC; hot product cache in Redis.'

Real-world use

Real-world use cases

• E-commerce: PostgreSQL orders + Elasticsearch search + Redis cart.
• Social: Cassandra timeline writes + PostgreSQL user accounts.
• Banking: SQL core ledger; NoSQL fraud feature store.
• Healthcare: SQL EHR records; document store for unstructured clinical notes.

Best practices

• Default to PostgreSQL until proven otherwise — it scales further than myths suggest.
• Denormalize in NoSQL deliberately for read paths; don't accidentally rebuild joins in app.
• Define source of truth per entity — avoid dual-write without reconciliation.
• Use migrations and schema validation even for document stores.
• Benchmark with realistic payload sizes and query patterns.
• Plan backup/restore per store type.

Common mistakes

• NoSQL because 'scale' without measuring write QPS or data size.
• Using MongoDB for heavy multi-document transactions needing ACID.
• Redis as only database without AOF/RDB and HA.
• Cross-store joins in application without caching — N+1 service calls.
• Ignoring operational expertise — Cassandra ops are not PostgreSQL ops.

Advanced interview questions

Summary

Choose database by access pattern, consistency, and scale — not hype. SQL remains default for transactional core data. NoSQL types solve specific shapes: document, column, key-value, graph. Polyglot persistence is normal at scale. CDC/events sync between stores; avoid dual-write chaos. Sharding and replication deepen SQL/NoSQL scale tactics next.