Part 4: SQL vs NoSQL, Distributed & Performance

1. SQL vs. NoSQL

	SQL (Relational)	NoSQL
Model	Tables (rows/columns)	Document, Key-Value, Column, Graph
Schema	Fixed, rigid	Flexible / schema-less
Scaling	Vertical (bigger server)	Horizontal (add servers)
Consistency	Strong (ACID)	Often eventual (BASE)
Use when	Structured data, transactions, joins	Unstructured data, high scale, fast writes

The 4 NoSQL categories:

• Document (MongoDB): JSON-like documents; flexible fields.
• Key-Value (Redis, DynamoDB): simple key → value; fastest lookups.
• Column-family (Cassandra, HBase): wide columns; great for analytics over huge data.
• Graph (Neo4j): nodes + edges; ideal for relationships (social, fraud).

BASE (the NoSQL counterpart to ACID): Basically Available, Soft state, Eventually consistent.

2. The CAP Theorem (The “Pick Two” Rule)

In any distributed data store, you cannot simultaneously achieve all three of these properties during a network failure:

• Consistency (C): Every read receives the most recent write or an error.
• Availability (A): Every request receives a response (even if it’s stale).
• Partition Tolerance (P): The system continues to operate despite network failures (partitions) between nodes.

The Reality: In a distributed system, Network Partitions (P) are inevitable. Therefore, you must choose between CP (Consistency over Availability) or AP (Availability over Consistency).

• CP (e.g., MongoDB, HBase): If the network breaks, the system shuts down or returns errors to ensure you never read old/wrong data.
• AP (e.g., Cassandra, DynamoDB): If the network breaks, the system stays “up,” but different users might see different versions of the data.

3. Strong vs. Eventual Consistency

• Strong Consistency: Once a write is acknowledged, all subsequent reads reflect that value. This requires expensive synchronization between all nodes before a write is confirmed.
• Eventual Consistency: A write is acknowledged when it hits one node. The system promises that eventually all nodes will have the data.
• Trade-off: Eventual consistency provides extreme performance and high availability, but applications must be designed to handle “stale” data.

Replication — sync vs async: synchronous replication confirms a write only after replicas acknowledge it (stronger consistency, higher latency); asynchronous replication confirms immediately and propagates later (faster, weaker consistency).

4. Partitioning vs. Sharding

If your database is too large, you must split it.

• Partitioning (Vertical or Horizontal):
• Splitting a table into smaller segments within the same server.
• Example: A Sales table partitioned by year (2024, 2025). The database engine knows which partition to look in, ignoring the rest (Partition Pruning).
• Sharding (Horizontal Scaling):
• Splitting a database across multiple physical servers.
• Example: Users with IDs 1–1M go to Server A; Users 1M+ go to Server B.
• Complexity: Managing cross-shard queries and data rebalancing is very difficult.

Sharding strategies:

• Range-based: rows 1–1000 → Server 1. Simple, but leads to hot spots (all new writes hit the last server).
• Hash-based: Hash(user_id) % number_of_servers. Spreads traffic evenly, but resharding (adding servers) is expensive — nearly all data re-hashes.
• Consistent Hashing: minimizes data movement when adding/removing servers — the industry standard.

5. Distributed Transaction Protocols

How do you update data on two different servers so that they both succeed or both fail?

• Two-Phase Commit (2PC):
• Phase 1 (Prepare): The “Coordinator” asks all participants: “Can you commit?”
• Phase 2 (Commit): If all respond “Yes,” the coordinator sends a “Commit” signal. If anyone says “No” or fails to respond, it sends an “Abort” signal.
• Drawback: It is a blocking protocol. If the coordinator dies while waiting, all participant nodes remain locked and frozen.
• Sagas (The Modern Alternative): Used in microservices. Instead of one big transaction, you break it into a series of local transactions. If one fails, you execute Compensating Transactions (e.g., if “Payment” fails, you run a “Cancel Order” command to undo the previous steps).

6. Performance Tuning Essentials

When asked “My query is slow, what do I do?”, follow this diagnostic path:

1. Explain Plan: Use the EXPLAIN command to see if the database is doing a Full Table Scan (Bad) or an Index Seek/Scan (Good).
2. Filter/Join Optimization: Are you filtering on unindexed columns? Are you joining on non-indexed Foreign Keys?
3. Covering Index: Can you create an index that includes all the columns requested in your SELECT statement? This allows the database to return the result without ever touching the actual table (Index-Only Scan).
4. Hardware: Do you have enough RAM for the Buffer Pool? (The more data that lives in memory, the fewer disk I/O operations required).
5. Avoid SELECT *: fetch only the columns you need to reduce I/O and use indexes better.
6. Watch for SARGable vs non-SARGable: wrapping an indexed column in a function (WHERE YEAR(date) = 2024) defeats the index; write WHERE date >= '2024-01-01' AND date < '2025-01-01'.