Recap — Choosing Between SQL and NoSQL

# Choosing Between SQL and NoSQL A frequent interview question. The honest answer: most apps work fine with SQL; NoSQL has specific use cases where it shines. ## SQL (Relational Databases) Examples: Postgres, MySQL, SQL Server, Oracle, SQLite. ### What they're good at - **Structured data** with relationships (users → orders → items). - **ACID transactions** (atomicity, consistency, isolation, durability). - **Complex queries** (joins, aggregations, window functions). - **Strong consistency** by default. - **Mature ecosystem** (ORMs, tools, talent). - **Schemas enforce data integrity**. ### Trade-offs - Harder to scale horizontally (sharding is complex). - Schema migrations can be slow on large tables. - JOIN-heavy queries can be expensive at scale. - Schema rigidity (a feature, sometimes a cost). ### Default choice For most apps: SQL. Start with Postgres. Move to NoSQL only when you have a specific need that SQL can't meet at scale. ## NoSQL types ### 1. Key-value stores Examples: Redis, DynamoDB (KV mode), Memcached. Data model: `key → value`. Value is opaque blob (string, JSON, binary). Use cases: - Caching (most common) - Sessions - Real-time leaderboards - Rate limiting counters Strengths: fastest reads/writes; trivially scalable. Weaknesses: no query (key lookup only); no relationships. ### 2. Document stores Examples: MongoDB, Couchbase, Firestore, DynamoDB. Data model: JSON-like documents, often nested. ```json { "_id": "user_123", "name": "Alice", "orders": [ {"id": "ord_1", "amount": 100}, {"id": "ord_2", "amount": 50} ] } ``` Use cases: - Content management (blog posts, product catalogs) - User profiles - Apps with rapidly evolving schemas Strengths: flexible schema; aggregates fit one document (no joins needed). Weaknesses: joins are awkward or absent; transactions limited; consistency varies by tool. ### 3. Column-family / wide-column stores Examples: Cassandra, ScyllaDB, HBase. Data model: rows with arbitrary columns, partitioned by key. Use cases: - Time-series data - High-volume writes (logging, metrics, IoT) - Multi-region replication with eventual consistency Strengths: massive write throughput; horizontal scaling. Weaknesses: complex data model; no joins; eventual consistency requires careful design. ### 4. Graph databases Examples: Neo4j, Amazon Neptune, ArangoDB. Data model: nodes and edges with properties. Use cases: - Social networks (friend graphs) - Knowledge graphs - Recommendation engines - Fraud detection (cycles, suspicious paths) Strengths: queries over multi-hop relationships are fast (compared to many JOINs in SQL). Weaknesses: niche; smaller talent pool; harder to migrate from. ### 5. Time-series databases Examples: InfluxDB, TimescaleDB, Prometheus. Optimized for: timestamped data, range queries, downsampling. Use cases: metrics, IoT, logs, financial ticker data. ## Decision framework ``` What does your data look like? ├── Structured with relationships → SQL (Postgres default) ├── Key-value lookups, no relationships → KV store (Redis/Memcached) ├── Hierarchical / nested documents → Document store (MongoDB) ├── Time-series → Time-series DB ├── Highly connected (graphs of nodes/edges) → Graph DB └── Massive write volume + horizontal scale → Column-family (Cassandra) ``` ## Common interview signals - "Strong consistency, transactions" → SQL. - "Flexible schema, fast iteration" → Document store. - "Cache, session" → KV (Redis). - "High write throughput, distributed" → Cassandra or DynamoDB. - "Social network, recommendation" → Graph DB. - "Search" → Elasticsearch / OpenSearch (search engine, not pure DB). ## Polyglot persistence Modern systems often use multiple DBs: - Postgres for user accounts and transactions. - Redis for cache and sessions. - Cassandra for activity logs. - Elasticsearch for search. Right tool for each job. Comes with operational complexity but performance wins. ## What's NewSQL Modern systems trying to combine SQL semantics with horizontal scale: - CockroachDB - YugabyteDB - Google Spanner (cloud-only) - Vitess (MySQL sharding layer) Use when: you need SQL semantics AND massive horizontal scale, and can pay the complexity tax. ## Common DB choice mistakes 1. **NoSQL because it's hot.** Most apps don't need it. 2. **SQL when document model fits better.** Trying to ORM-shoehorn nested data into relational. 3. **Single DB for everything.** Use polyglot when costs justify. 4. **Ignoring operational cost.** Each DB has its own scaling, backup, monitoring story. ## Takeaway SQL default for structured data with relationships, transactions, complex queries. NoSQL when specific need: KV (cache, sessions); document (flexible schema); column (massive writes, eventual consistency OK); graph (relationship traversal); time-series (metrics). Polyglot is common at scale — different DBs for different workloads. NewSQL bridges SQL semantics and horizontal scale at a complexity cost.