System Design Module 6: Database Design (SQL vs NoSQL)

The Core Conflict: SQL vs NoSQL

Mental Model

Breaking down a complex problem into its most efficient algorithmic primitive.

In a system design interview, choosing the right database is half the battle. This choice determines your consistency guarantees, scaling limits, and development speed.

1. SQL (Relational Databases)

Examples: PostgreSQL, MySQL, Oracle.

• Data Model: Rigid tables with rows and columns. Uses schemas.
• ACID: Atomic, Consistent, Isolated, Durable. High focus on data integrity.
• Scaling: Optimized for Vertical Scaling. Horizontal scaling (sharding) is complex.
• Best For: Financial systems, complex joins, structured data.

2. NoSQL (Non-Relational)

Examples: Cassandra (Columnar), MongoDB (Document), Redis (Key-Value), Neo4j (Graph).

• Data Model: Flexible. JSON-like documents, key-value pairs, or wide-column rows.
• BASE: Basically Available, Soft-state, Eventually consistent.
• Scaling: Built for Horizontal Scaling from day one.
• Best For: Real-time analytics, social media feeds, massive unstructured data.

3. Decision Matrix: Which one to pick?

4. Real-World Analogy: The Library

• SQL is a strictly organized library where every book is in a specific category, shelf, and position. It's easy to find relationships but hard to move the library.
• NoSQL is a pile of boxes where you just throw books in. It's incredibly fast to add more boxes, but finding specific relationships between books in different boxes is harder.

Final Takeaway

Don't pick NoSQL just because it's "cool." Pick it because you need massive scale or a flexible schema. Pick SQL because you need consistency and complex queries.

6. Staff-Level Verbal Masterclass (Communication)

Interviewer: "How would you defend this specific implementation in a production review?"

You: "In a mission-critical environment, I prioritize the Big-O efficiency of the primary data path, but I also focus on the Predictability of the system. In this implementation, I chose a state-based dynamic programming approach. While a recursive solution is more readable, I would strictly monitor the stack depth. If this were to handle skewed inputs, I would immediately transition to an explicit stack on the heap to avoid a StackOverflowError. From a memory perspective, I leverage localized objects to ensure that we minimize the garbage collection pauses (Stop-the-world) that typically plague high-throughput Java applications."

7. Global Scale & Distributed Pivot

When a problem like this is moved from a single machine to a global distributed architecture, the constraints change fundamentally.

1. Data Partitioning: We would shard the input space using Consistent Hashing. This ensures that even if our dataset grows to petabytes, any single query only hits a small subset of our cluster, maintaining logarithmic lookup times.
2. State Consistency: For problems involving state updates (like DP or Caching), we would use a Distributed Consensus protocol like Raft or Paxos to ensure that all replicas agree on the final state, even in the event of a network partition (The P in CAP theorem).

8. Performance Nuances (The Staff Perspective)

1. Cache Locality: Accessing a 2D matrix in row-major order (reading [i][j] then [i][j+1]) is significantly faster than column-major order in modern CPUs due to L1/L2 cache pre-fetching. I always structure my loops to align with how the memory is physically laid out.
2. Autoboxing and Generics: In Java, using List<Integer> instead of int[] can be 3x slower due to the overhead of object headers and constant wrapping. For the most performance-sensitive sections of this algorithm, I advocate for primitive specialized structures.

Engineering Standard: The "Staff" Perspective

In high-throughput distributed systems, the code we write is often the easiest part. The difficulty lies in how that code interacts with other components in the stack.

1. Data Integrity and The "P" in CAP

Whenever you are dealing with state (Databases, Caches, or In-memory stores), you must account for Network Partitions. In a standard Java microservice, we often choose Availability (AP) by using Eventual Consistency patterns. However, for financial ledgers, we must enforce Strong Consistency (CP), which usually involves distributed locks (Redis Redlock or Zookeeper) or a strictly linearizable sequence.

2. The Observability Pillar

Writing logic without observability is like flying a plane without a dashboard. Every production service must implement:

• Tracing (OpenTelemetry): Track a single request across 50 microservices.
• Metrics (Prometheus): Monitor Heap usage, Thread saturation, and P99 latencies.
• Structured Logging (ELK/Splunk): Never log raw strings; use JSON so you can query logs like a database.

3. Production Incident Prevention

To survive a 3:00 AM incident, we use:

• Circuit Breakers: Stop the bleeding if a downstream service is down.
• Bulkheads: Isolate thread pools so one failing endpoint doesn't crash the entire app.
• Retries with Exponential Backoff: Avoid the "Thundering Herd" problem when a service comes back online.

Critical Interview Nuance

When an interviewer asks you about this topic, don't just explain the code. Explain the Trade-offs. A Staff Engineer is someone who knows that every architectural decision is a choice between two "bad" outcomes. You are picking the one that aligns with the business goal.

Performance Checklist for High-Load Systems:

1. Minimize Object Creation: Use primitive arrays and reusable buffers.
2. Batching: Group 1,000 small writes into 1 large batch to save I/O cycles.
3. Async Processing: If the user doesn't need the result immediately, move it to a Message Queue (Kafka/SQS).

Key Takeaways

• Data Model: Rigid tables with rows and columns. Uses schemas.
• ACID: Atomic, Consistent, Isolated, Durable. High focus on data integrity.
• Scaling: Optimized for Vertical Scaling. Horizontal scaling (sharding) is complex.

Read Next

• Module 7: Sharding & Partitioning
• Case Study: Design Instagram