📈 धडा 10 — Scaling: अधिक कारकून, अधिक खोल्या

📍 तुम्ही इथे आहात: 18 पैकी धडा 10 · मागील: lesson-09-backups-recovery · पुढील: lesson-11-nosql-other-rooms

📦 या ब्रँचमध्ये काय आहे

धडे 01–09, आणि खोली गर्दीने भरली की काय करायचे: connection pools, caches, read replicas (आणि त्यांचा lag), partitions, sharding — आणि teams प्रत्यक्षात कोणत्या क्रमाने हे वापरतात.

🧒 5 वर्षांच्या मुलाला समजावल्यासारखे

शाळा मोठी झाली. रेकॉर्ड रूमच्या दारापर्यंत रांग लागली आहे. आता काय? दर वेळी तीच यादी, त्याच क्रमाने:

आधी प्रश्न सुधारा — बहुतेक रांगा म्हणजे एकच हळू प्रश्न हजार वेळा विचारलेला (हरवलेला card catalogue, धडा 06; एक N+1, धडा 12).
कारकुनांचा pool 🚰 — प्रत्येक request साठी नवे connection उघडणे म्हणजे प्रत्येक पाहुण्यासाठी नवा कारकून नेमणे. एक pool ठेवा; तेच पुन्हा वापरा.
गरम उत्तरांचा सूचना फलक 🧊 (एक cache, उदा. Redis) — "3A मध्ये किती विद्यार्थी?" हा प्रश्न दर सेकंदाला विचारला जातो आणि दिवसातून दोनदाच बदलतो. उत्तर counter वर एका मिनिटासाठी लावून ठेवा.
मोठी खोली — जास्त memory आणि जलद disks खूप फरक पाडतात.
खोलीच्या फक्त-वाचण्याच्या प्रती 📖 (replicas) — वाचणारे प्रतींकडे जातात, लिहिणारे एकाच मूळ खोलीकडे. प्रती थोड्या मागे असतात (replication lag): स्वतः लिहिलेले मूळ खोलीतूनच वाचा.
Partitions — हजेरीची नोंदवही वर्षानुसार विभागलेली; जुनी वर्षे हळू कपाटावर.
Shards 🏢 — प्रदेश किंवा शाळेनुसार वेगळ्या खोल्या. शेवटचा उपाय: खोल्यांपलीकडचे प्रश्न आणि खोल्यांपलीकडचे transactions कठीण होतात.

बहुतेक शाळांना पायऱ्या 6–7 कधीच लागत नाहीत. ज्यांना लागल्या, त्या आधी 1–5 करूनच तिथे पोहोचल्या.

🗺️ आकृती

flowchart LR
    q["🐌 the queue"] --> f["1 fix queries + indexes"] --> p["2 connection pool"] --> c["3 cache hot answers"] --> b["4 bigger machine"] --> r["5 read replicas (lag!)"] --> pt["6 partitions"] --> s["7 shards — last"]
    w["✍️ writes: one primary; Multi-AZ standby is for surviving a fire, not for speed"]
    r -.-> w

❓ काय

Connection pool (pgbouncer, HikariCP, driver चा pool): connections महाग असतात; त्यांना मर्यादा घाला (हजारो connections झाले की databases कोसळतात) आणि तेच पुन्हा वापरा.
Cache: key → उत्तर, TTL सह; write झाल्यावर invalidate करा किंवा थोडा वेळ जुना data चालेल हे स्वीकारा. सगळे नाही, तर गरम आणि हळू तेच cache करा.
Read replicas: streaming प्रती; जवळजवळ लगेच, पण अगदी लगेच नाही. lag चालणारे reads तिकडे पाठवा; "स्वतः लिहिलेले वाचणे" primary कडे पाठवा. failover साठी तयार standby म्हणूनही उपयोगी.
Vertical scaling: मोठे instance — engineering च्या प्रत्येक तासामागे सर्वात स्वस्त पायरी, एका मर्यादेपर्यंत.
Partitioning: एक logical table, अनेक physical तुकडे (तारखेनुसार, प्रदेशानुसार); एकाच partition ला लागणाऱ्या queries जलद होतात; देखभाल सोपी होते.
Sharding: key नुसार वेगळे databases; application routing करते. Cross-shard JOINs आणि transactions application code बनतात.
Managed (AWS L17, RDS): replicas, Multi-AZ, snapshots फक्त checkboxes; queries, indexes आणि schema मात्र तुमचेच राहतात.

🤔 का

कारण scaling च्या गोष्टी दोनपैकी एका प्रकारे फसतात: एखाद्या blog ने सांगितले म्हणून 50 GB database shard करणे (एक वर्ष वाया), किंवा कधीच index न जोडता database ला "हळू" म्हणणे (एक customer गमावला). ही शिडी दोन्हीपासून बचाव आहे — आणि प्रत्येक पायरी पुढच्यापेक्षा स्वस्त आहे.

🔧 कसे (या repo मध्ये)

SQLite ही एकच file आहे, म्हणून इथे replicas आणि shards कागदावरचे सराव आहेत — पण pool, cache आणि "आधी query सुधारा" ही पायरी खरी आहे: धडा 06 चा index आणि धडा 12 चा N+1 म्हणजे पायरी 1; खालचा code म्हणजे पायऱ्या 2–3 लहान रूपात.

🧪 करून पाहा

python3 - <<'EOF'
import sqlite3, time
# rung 2: a pool in miniature — one connection reused vs opened per request
t=time.perf_counter()
for _ in range(2000): sqlite3.connect("db/school.db").execute("SELECT 1").fetchone()
per_request=(time.perf_counter()-t)*1000
c=sqlite3.connect("db/school.db"); t=time.perf_counter()
for _ in range(2000): c.execute("SELECT 1").fetchone()
pooled=(time.perf_counter()-t)*1000
print(f"2000 queries — new connection each: {per_request:.0f} ms · one reused connection: {pooled:.0f} ms")
# rung 3: a cache with a TTL for a hot, slow-ish question
cache={}; TTL=60
def class_size(cls):
    hit=cache.get(cls)
    if hit and time.time()-hit[1] < TTL: return hit[0], "cache"
    n=c.execute("SELECT COUNT(*) FROM students s JOIN classes k ON k.id=s.class_id WHERE k.name=?", (cls,)).fetchone()[0]
    cache[cls]=(n, time.time()); return n, "db"
print(class_size("3A"), class_size("3A"))
EOF
# paper exercise (5 min): your app shows "your enrolment was saved" then lists students from a replica — what can the user see, and what do you change?

✅ तपासा — तुम्हाला काय दिसायला हवे

प्रत्येक query साठी नवे connection उघडण्यापेक्षा पुन्हा वापरलेले connection कित्येक पट जलद आहे (pool ची पायरी, मोजून पाहिलेली); दुसरा class_size call cache मधून उत्तर देतो. तुमचे कागदावरचे उत्तर: नवा विद्यार्थी क्षणभर दिसणार नाही (lag) — स्वतः लिहिलेले वाचणे primary कडे जाते, किंवा UI साठवलेली row स्थानिकपणे दाखवते.

🏁 तुम्ही आत्ताच काय सिद्ध केले

दोन सर्वात स्वस्त पायऱ्या — connections पुन्हा वापरा, गरम उत्तरे cache करा — खऱ्या database वर मोजल्या, आणि replica-lag चा सापळा तो भेटण्याआधीच ओळखला.

⚠️ नेहमीच्या चुका

index करण्याआधी sharding
अमर्याद connections (प्रत्येक app instance × प्रत्येक worker) — database queries मुळे नाही, तर connections मुळे कोसळतो
सगळे cache करणे (किंवा TTL शिवाय) — जुना data, आणि ताजा करायचा मार्ग नाही
स्वतः लिहिलेले replica वरून वाचणे
Multi-AZ ला performance feature समजणे — ते टिकण्यासाठीचे feature आहे

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: "DB हळू आहे, shard करायचे का?" असे विचारल्यावर senior engineer हीच शिडी सांगतो: आधी slow query log दाखवा. खऱ्या companies मधले बहुतेक scaling चे विजय पायऱ्या 1–3 मधूनच आले.

⏭️ पुढे

प्रत्येक प्रश्न नोंदवहीत बसत नाही. NoSQL आणि इतर खोल्या — document, key-value, columnar, graph, vector — आणि निवड कशी करायची.

git checkout lesson-11-nosql-other-rooms

📈 Lesson 10 — Scaling: more clerks, more rooms

📍 You are here: Lesson 10 of 18 · Previous: lesson-09-backups-recovery · Next: lesson-11-nosql-other-rooms

📦 What's in this branch

Lessons 01–09, plus what to do when the room is busy: connection pools, caches, read replicas (and their lag), partitions, sharding — and the order in which teams actually reach for them.

🧒 Explain like I'm 5

The school grew. The record room has a queue out of the door. What now? The same list, in the same order, every time:

Fix the questions first — most queues are one slow question asked a thousand times (a missing card catalogue, lesson 06; an N+1, lesson 12).
A pool of clerks 🚰 — opening a new connection per request is like hiring a clerk per visitor. Keep a pool; reuse them.
A notice board of hot answers 🧊 (a cache, e.g. Redis) — "how many students in 3A?" is asked every second and changes twice a day. Pin the answer at the counter for a minute.
A bigger room — more memory and faster disks go a long way.
Read-only copies of the room 📖 (replicas) — readers go to the copies, writers to the one original. The copies are a beat behind (replication lag): read your own write from the original.
Partitions — the attendance register split by year; old years on a slower shelf.
Shards 🏢 — separate rooms by region or school. Last resort: questions across rooms, and transactions across rooms, get hard.

Most schools never need steps 6–7. The ones that do got there by doing 1–5 first.

🗺️ Diagram

flowchart LR
    q["🐌 the queue"] --> f["1 fix queries + indexes"] --> p["2 connection pool"] --> c["3 cache hot answers"] --> b["4 bigger machine"] --> r["5 read replicas (lag!)"] --> pt["6 partitions"] --> s["7 shards — last"]
    w["✍️ writes: one primary; Multi-AZ standby is for surviving a fire, not for speed"]
    r -.-> w

❓ What

Connection pool (pgbouncer, HikariCP, the driver's pool): connections are expensive; limit them (databases fall over at thousands) and reuse them.
Cache: key → answer with a TTL; invalidate on write or accept brief staleness. Cache the hot and slow, not everything.
Read replicas: streaming copies; near-instant but not instant. Route reads that tolerate lag; route "read your own write" to the primary. Also a warm standby for failover.
Vertical scaling: bigger instance — the cheapest step per hour of engineering, up to a point.
Partitioning: one logical table, many physical pieces (by date, region); queries that hit one partition get faster; maintenance gets easier.
Sharding: separate databases by key; the application routes. Cross-shard JOINs and transactions become application code.
Managed (AWS L17, RDS): replicas, Multi-AZ, snapshots as checkboxes; the queries, indexes and schema remain yours.

🤔 Why

Because scaling stories fail in one of two ways: sharding a 50 GB database because a blog said so (a year lost), or never adding an index and calling the database "slow" (a customer lost). The ladder is the defence against both — and every rung is cheaper than the next.

🔧 How (in this repo)

SQLite is one file, so replicas and shards are paper exercises here — but the pool, the cache and the "fix the query first" rung are real: lesson 06's index and lesson 12's N+1 are rungs 1; the code below is rungs 2–3 in miniature.

🧪 Try it

python3 - <<'EOF'
import sqlite3, time
# rung 2: a pool in miniature — one connection reused vs opened per request
t=time.perf_counter()
for _ in range(2000): sqlite3.connect("db/school.db").execute("SELECT 1").fetchone()
per_request=(time.perf_counter()-t)*1000
c=sqlite3.connect("db/school.db"); t=time.perf_counter()
for _ in range(2000): c.execute("SELECT 1").fetchone()
pooled=(time.perf_counter()-t)*1000
print(f"2000 queries — new connection each: {per_request:.0f} ms · one reused connection: {pooled:.0f} ms")
# rung 3: a cache with a TTL for a hot, slow-ish question
cache={}; TTL=60
def class_size(cls):
    hit=cache.get(cls)
    if hit and time.time()-hit[1] < TTL: return hit[0], "cache"
    n=c.execute("SELECT COUNT(*) FROM students s JOIN classes k ON k.id=s.class_id WHERE k.name=?", (cls,)).fetchone()[0]
    cache[cls]=(n, time.time()); return n, "db"
print(class_size("3A"), class_size("3A"))
EOF
# paper exercise (5 min): your app shows "your enrolment was saved" then lists students from a replica — what can the user see, and what do you change?

✅ Verify — what you should see

The reused connection is many times faster than a connection per query (the pool rung, measured); the second class_size call answers from cache. Your paper answer: the new student may be missing for a moment (lag) — read-your-own-write goes to the primary, or the UI shows the saved row locally.

🏁 What you just proved

Two of the cheapest rungs — reuse connections, cache hot answers — measured on a real database, and the replica-lag trap named before you meet it.

⚠️ Common mistakes

sharding before indexing
unbounded connections (every app instance × every worker) — the database collapses under connections, not queries
caching everything (or with no TTL) — stale data with no way to refresh
reading your own write from a replica
treating Multi-AZ as a performance feature — it is a survival feature

🏭 Why this matters in production: the ladder is what a senior engineer says when asked "the DB is slow, should we shard?": show me the slow query log first. Most scaling wins in real companies came from rungs 1–3.

⏭️ Next

Not every question fits a register. NoSQL and other rooms — document, key-value, columnar, graph, vector — and how to choose.

git checkout lesson-11-nosql-other-rooms

← Previousbackups recovery Next →nosql other rooms

This page is the lesson's README from the lesson-10-scaling branch, shown here so the whole School stays on one site. Code files open on GitHub at the same branch.