EXAMPLE 1 — URL Shortener

(You can explain this in 2 mins in interviews)

Given:

 100M users

 1 URL per user per day

 Each URL shortener entry stores:

o original URL (200 bytes avg)

o short code (8 bytes)

o metadata (50 bytes)

Let’s es mate everything.

QPS (Queries Per Second)

Every day:

URLs created/day = 100M

URL crea on request = 1 request

Daily seconds = 86400

QPS = 100,000,000 / 86,400 ≈ 1157 req/sec

Peak mul plier (x3):

Peak QPS = ~3500 req/sec

✔ Very small — even 1 server can handle.

STORAGE

One record size:

200 bytes (URL) +

8 bytes (short code) +

50 bytes (metadata)

≈ 258 bytes ≈ 0.25 KB

Records per year:

100M × 365 = 36.5 billion URLs

Yearly storage:

36.5B × 0.25 KB = 9.1 TB

~18 TB / year

✔ This tells us:

“Why can’t we use Oracle / SQL for URL shortener? How big is too big?”

Let me give you a clear numeric answer, with simple logic.

SQL Storage Limits (Prac cal, Not Theore cal)

SQL databases can store terabytes — but they fall apart when the write volume, index size, and
table size get too big.

Let’s break it down with REAL enterprise numbers.

1. INSERT OPS LIMIT (SQL vs NoSQL)

Oracle / MySQL:

 Prac cal write throughput = 2,000 – 10,000 inserts/sec

 Beyond this → locks, conten on, slow writes

Cassandra / DynamoDB:

 Prac cal write throughput = 100,000+ writes/sec

 Horizontally scalable

 No locks, no constraints

2. SINGLE TABLE SIZE LIMIT (Real World)

Oracle:

 Can store many TB, but performance dies when:

o Table > 1 billion rows

o Index > 50–100 GB

o Inserts involve heavy indexing

o Reten on is long

URL Shortener Yearly Data:

We calculated:
36.5 billion URLs per year

≈ 9 TB / year raw

≈ 18 TB with replica on

Pu ng 36.5 billion rows into Oracle in a single table is not realis c.

3. Index Size Problem (SQL Killer)

For URL shortener, your main index is:

short_code → original_url

Index entry size ≈ 30–50 bytes

Index size per year:

36.5B × 40 bytes = 1.46 TB index

Oracle does NOT like 1.46 TB indexes

NoSQL is designed EXACTLY for this type of workload.

4. Write IOPS Requirement

Earlier we calculated peak QPS:

3500 URL shortens/sec (writes)

35,000 fetches/sec (reads)

SQL can handle 35k reads/sec with caching.

But 3500 writes/sec con nuously into a giant table with a growing index?
→ Near impossible in Oracle.

NoSQL (Cassandra/DynamoDB) handles:

 Horizontal par oning

 Zero locking

 Write-op mized LSM trees

 Independent scaling

5. Cost Considera on

SQL:
  Scaling storage + compute = ver cal scaling (super expensive)

 18 TB replicated Oracle instance = ₹1–2 crore/year licensing+

 Hard to maintain

 Hard to backup

NoSQL:

 Horizontal scaling

 Scale-out for pennies

 Built for billions of rows

Final Answer (Interview-Perfect)

“URL shortener writes ~36 billion records per year, ~18 TB raw data including replica on.
Oracle can store terabytes but indexing billions of records kills insert performance.
Oracle/SQL can handle maybe 500–2000 writes/sec reliably, but URL shortener needs ~3500
sustained writes/sec with fast lookups.
Also, index size grows to 1.4 TB/year, which is not prac cal for SQL.
That is why URL shortener always uses NoSQL like Cassandra or DynamoDB — built for massive write
throughput and horizontal par oning.”

READ QPS (Users opening short URLs)

Assume each user opens 10 short links/day:

Total reads/day = 100M × 10 = 1B reads/day

Reads per second:

QPS = 1,000,000,000 / 86,400 ≈ 11,500 QPS

Peak (3×):

35,000 QPS

✔ This requires caching (Redis/Memory).

CACHE Requirement

Assume 20M most-used URLs stay in hot cache.

20M × 0.25 KB = 5 MB
Wait — that seems small?
Let’s re-check:

20M × 256 bytes = 5.12 GB → YES

So:

✔ Hot cache storage = ~5 GB Redis

NETWORK Bandwidth

URL redirec on response is ny (~200 bytes).

35,000 QPS × 200 bytes ≈ 7 MB/sec

≈ 56 Mbps

✔ Very small
✔ CDNs can handle easily

What this teaches you

 Write QPS is low

 Read QPS is high → needs caching

 Storage grows fast → need NoSQL

 Network usage is small

 Redis solves 95% performance problems

---------------------------------------------------------

EXAMPLE 2 — CHAT APP (1-to-1 messaging)

Given:

 50M daily ac ve users

 Average 40 messages/day/user

Let’s es mate everything.

QPS

Total messages per day:

50M × 40 = 2B messages/day
Requests per second:

2,000,000,000 / 86,400 = 23,148 QPS

Peak 3×:

~70,000 QPS

✔ Medium load
✔ Need async processing
✔ Ka a or MQ suitable

Storage

Message size (text-only):

200 bytes/message

Yearly storage:

2B/day × 365 × 200 bytes

= 146 TB/year

✔ SQL is impossible
✔ Need NoSQL (Cassandra, DynamoDB)

Throughput

Write throughput:

70,000 QPS × 200 bytes = 14 MB/sec

Read throughput (deliver messages):

Each message is read 2–3 mes (sender, receiver, sync).

70,000 × 3 × 200 bytes = 42 MB/sec

✔ Need Ka a
✔ Need par oning
✔ Need shard-based messaging

CACHE

Assume unread messages = 100M per day

Each 200 bytes:

100M × 200 bytes = 20 GB

NETWORK

Chats are small → network easy.

42 MB/sec ≈ 336 Mbps

✔ Very manageable

What you learned:

 Chat apps need Ka a

 Redis cannot hold full history

 Unread messages need caching

 Fan-out mul plies QPS

 Storage explodes → NoSQL mandatory