How do you choose between SQL and NoSQL databases?

Choose SQL (PostgreSQL, MySQL) for structured data requiring ACID transactions, complex JOINs, and strict schema enforcement. Choose NoSQL (MongoDB, DynamoDB, Cassandra) for flexible schemas, massive horizontal scale, high write throughput, and eventual consistency workloads.

When should you choose Vertical Scaling vs Horizontal Scaling?

Start with vertical scaling (adding CPU/RAM to one server) for simplicity. Transition to horizontal scaling (adding more servers) when hardware limits are reached, cost curves become prohibitive, or high availability is required. Horizontal scaling introduces distributed system complexity.

What is the difference between Sharding, Partitioning, and Indexing?

Indexing speeds up read queries within a single table using B-Tree or Hash structures. Partitioning splits a single table into logical physical chunks within one database instance. Sharding distributes rows across multiple distinct database servers using a shard key, scaling write throughput and storage.

How do you scale WebSockets to millions of concurrent connections?

Increase Linux file descriptor limits, use Layer 4 load balancing with sticky sessions, and implement a Redis Pub/Sub backplane so any WebSocket server can relay messages to clients connected to other servers. Pattern: Client → HAProxy → WS Servers ↔ Redis Pub/Sub.

What is the difference between LSI and GSI in DynamoDB?

A Local Secondary Index (LSI) shares the same partition key as the base table but uses a different sort key. It is limited to 10GB per item collection and supports strong consistency. A Global Secondary Index (GSI) uses a completely different partition key, spans all partitions, supports only eventual consistency, and has its own dedicated throughput capacity.

Which database should I use for transactions, user profiles, logs, and analytics?

Use PostgreSQL for financial transactions (ACID compliance). Use MongoDB for user profiles (flexible JSON). Use Cassandra for logs and high-throughput writes (LSM tree). Use Apache Druid for real-time analytics (columnar storage). Use Elasticsearch for search and catalog (inverted index). Use Redis for sessions and caching (in-memory).

How do you design a distributed rate limiter?

Use a Token Bucket algorithm for burst-friendly limiting. For distributed enforcement across multiple API gateway instances, use Redis with Lua scripts to atomically evaluate and increment counters, preventing race conditions (dirty reads) where concurrent requests could bypass the limit.

How does Consistent Hashing work and how do Virtual Nodes eliminate Hotspots? — System Design Interview Answer & Trade-offs

Q: How does Consistent Hashing work?

Consistent hashing maps both servers and keys onto a circular ring. A key routes clockwise to the nearest server. When a node is added or removed, only 1/N keys need to migrate (vs nearly 100% in modulo hashing). Virtual nodes (100-200 per server) ensure even distribution and prevent hotspots.

Architectural Breakdown & AnalysisFAANG Aligned

In distributed storage systems (e.g., Redis Cluster, Cassandra, DynamoDB), assigning data keys to server nodes using standard modulo hashing (hash(key) % N) causes catastrophic data movement whenever a node is added or removed ($N \to N+1$), forcing nearly 100% of keys to migrate.\n\n### 1. The Consistent Hash Ring\nConsistent hashing maps both server nodes and data keys onto a circular 360-degree integer space (e.g., a $2^{32}-1$ ring):\n1. Hash the Servers: Server IPs or hostnames are passed through a cryptographic hash function (e.g., MurmurHash3) and positioned on the ring.\n2. Hash the Keys: When a key arrives, its hash value is positioned on the ring.\n3. Clockwise Routing: Traverse clockwise from the key's position until the first physical server is encountered. That server owns the key.\n\n### 2. Node Addition and Removal\n* When a node is added, it only claims keys between itself and its immediate counter-clockwise predecessor. On average, only $K/N$ keys need migration (where $K$ is total keys and $N$ is nodes).\n\n### 3. The Hotspot Problem & Virtual Nodes (Vnodes)\nWith a small number of physical servers (e.g., 5 nodes), random hash placement produces unevenly sized ring segments, causing one server to hold 60% of data while another holds only 5% (Non-uniform data distribution).\n\n* The Solution: Virtual Nodes (Vnodes):\n Each physical server is mapped to multiple distinct points on the ring (e.g., 100 to 256 virtual positions per physical machine: ServerA#1, ServerA#2, ...).\n * Spreading hundreds of virtual nodes across the ring guarantees statistical uniformity.\n * Heterogeneous hardware can be supported naturally: a machine with 2x CPU/RAM is assigned 2x more virtual nodes.\n\n### Key Architectural Comparison\n| Hashing Model | Keys Migrated on Resize | Hotspot Protection | Hardware Heterogeneity Support | Production Example |

:---	:---	:---	:---	:---
Basic Consistent Hashing	$1/N$ of keys only	Poor (Vulnerable to ring clustering)	Difficult	Early distributed caches

How does Consistent Hashing work and how do Virtual Nodes eliminate Hotspots?

Continue Learning on Being SDE