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 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.

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 do you choose between SQL and NoSQL databases for a system? — System Design Interview Answer & Trade-offs

Q: 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.

Architectural Breakdown & AnalysisFAANG Aligned

The choice between SQL (Relational) and NoSQL (Non-Relational) databases is not about which is "better," but rather which matches your system's data structure, query patterns, and scale constraints.

1. SQL Databases (e.g., PostgreSQL, MySQL)

Data Structure: Strict tabular schema with defined relationships (Foreign Keys). Ideal for normalized data.
Consistency: Strict ACID compliance by default. Best for financial ledgers and transactions where inconsistency causes immediate failure.
Scaling: Primarily vertical (scaling CPU/RAM). Horizontal scaling requires master-replica reads or complex application-layer sharding.
Queries: Powerful SQL engine supporting multi-table JOINs and complex analytical aggregations.

2. NoSQL Databases (e.g., MongoDB, DynamoDB, Cassandra)

Data Structure: Schemaless or flexible models (Key-Value, Document, Wide-Column, Graph).
Consistency: Eventual consistency models (BASE) optimized for ultra-high write throughput and partition tolerance.
Scaling: Native horizontal scaling out-of-the-box via partition keys and auto-sharding.
Queries: Key-value lookups or simple index searches. Multi-document JOINs are either unsupported or inefficient.

NOTE — Decision Rule of Thumb

Choose SQL if you require multi-record ACID transactions, complex relational queries, or your data model is highly structured. Choose NoSQL if you need to scale horizontally to support massive read/write volumes, require flexible document representations, or are storing time-series or unstructured data.

Key Architectural Comparison

Database Type	Schema Model	ACID Guarantees	Horizontal Scalability	Best Fit Workload
SQL (Relational)	Strict tabular schema (Foreign Keys).	Full ACID out of the box.	Challenging (Requires manual sharding/replicas).	Financial ledgers, ERP, core order checkouts.
NoSQL (Document)	Flexible JSON/BSON documents.	Single-document ACID (BASE).	Native auto-sharding out of the box.	E-commerce catalogs, user profile preferences.
NoSQL (Wide-Column)	Partition & Clustering key table.	Tunable eventual consistency.	Massive linear write scale across petabytes.	Time-series telemetry, chat messaging history.
NoSQL (Key-Value)	Opaque string or byte values.	Atomic single-key mutations.	Ultra-fast memory-speed cluster scaling.	Session storage, caching, shopping carts.

How do you choose between SQL and NoSQL databases for a system?

1. SQL Databases (e.g., PostgreSQL, MySQL)

2. NoSQL Databases (e.g., MongoDB, DynamoDB, Cassandra)

Key Architectural Comparison

Continue Learning on Being SDE