Question 1

When would you choose SQL vs NoSQL?

Accepted Answer

**Target Audience:** Beginners to system design — you have used a spreadsheet or a database table; no database internals assumed.
**Difficulty:** Medium
**Verified with:** Node 22.22 and Python 3.12 (both print identical output).

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Behaviour shown comes from running the example; the **Verified** section quotes the full output.

Question 2

What is database connection pooling?

Accepted Answer

**Target Audience:** Backend beginners — you have written code that queries a database; no knowledge of database internals assumed.
**Difficulty:** Medium
**Verified with:** Node 22.22 and Python 3.12 (both print identical output).

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Behaviour shown comes from running the example; the **Verified** section quotes the full outpu

Question 3

How do you choose between SQL and NoSQL and scale each (replication, sharding, federation)?

Accepted Answer

## Step 1: Use Cases, Constraints, Assumptions, Back-of-Envelope

### Use cases
We'll scope to handle only the core flows that expose the bottlenecks; everything else is deferred.

- **User** needs strong transactions — SQL (ACID, joins) for orders, payments, friendships.
- **Service** needs massive write scale — NoSQL (Cassandra/Dynamo wide-column/KV) for timelines, metrics, crawl frontier.
- **Service** scales reads — add read replicas (async), measure lag.
- **Service** scales writes — shard

Question 4

What is a write-ahead log (WAL)?

Accepted Answer

**Target Audience:** Beginners to database internals — you know a database stores data on disk and that a server can crash; everything else is defined as we go.
**Difficulty:** Medium
**Verified with:** Node 22.22 and Python 3.12 (both print identical output). PostgreSQL settings were read with `SHOW` on a local PostgreSQL 16.15 server; SQLite behaviour was checked with SQLite 3.45.1 from Python.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 I

Question 5

What is the difference between optimistic and pessimistic locking?

Accepted Answer

**Target Audience:** Beginners to databases and concurrency — you know what a row, a transaction and an `UPDATE` are; locking is built up from there.
**Difficulty:** Medium
**Verified with:** Node 22.22 and Python 3.12 (both print identical output). PostgreSQL defaults quoted below were read with `SHOW` on a local PostgreSQL 16.15 server; the SQL patterns are shown for illustration and were not executed in this run.

> **How to read this doc:** Every idea is explained in plain language first, th

Question 6

What indexing strategies improve database performance?

Accepted Answer

**Target Audience:** Developers who know what an index is and want to choose good ones. If "B-tree", "page" and "leaf" are new, read [Explain database indexing and B-trees](/interview-question/database-indexing-btrees) first; this page does not re-explain the tree.
**Difficulty:** Medium
**Verified with:** Node 22.22 and Python 3.12 (both print identical output), plus real query plans from SQLite 3.45.1 via Python.

> **How to read this doc:** Every idea is explained in plain language first, the

Question 7

How would you design a distributed email service?

Accepted Answer

**Target Audience:** Backend engineers preparing for a "design Gmail" round, and anyone curious how a message actually gets from one mailbox to another.
**Difficulty:** Hard
**Verified with:** Node 22.22 and Python 3.12 (identical output for both examples). SMTP reply classes are from RFC 5321; the Postfix default `maximal_queue_lifetime` of 5 days is from the Postfix queue manager documentation; the bulk-sender rules (more than 5,000 messages a day to Gmail: SPF, DKIM and DMARC, one-click unsub

SYSTEM DESIGNDECODED.

Medium