🖼️ See the drawing + lab 🏠 Course home 🌿 Branch on GitHub ✏️ View source

🖼️ आकृती आणि labThe drawing + lab पूर्ण पानावर उघडा ↗Open full page ↗

📚 धडा 10 — Read replicas + connection pools: नोंदवहीच्या प्रती

📍 तुम्ही इथे आहात: 13 पैकी धडा 10 · मागील: lesson-09-caching-the-database · पुढील: lesson-11-partitioning

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

धडे 01–09, आणि database ची दोन साधने: read replicas (reads देणाऱ्या आणि थोड्या मागे असणाऱ्या प्रती) आणि एक connection pool (RDS Proxy), ज्यामुळे शेकडो callers थोडे database connections वाटून घेऊ शकतात. scale/demo.py मधले replicas() replica lag आणि connection storm दाखवते.

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

कार्यालयात एकच नोंदवही 📒 आहे. शिक्षिका त्यात grades लिहितात. हजारो पालकांना ती वाचायची आहे. एका वेळी एकच व्यक्ती एखादे पान पाहू शकते.

म्हणून दीपिका नोंदवहीच्या प्रती 📚 बनवते आणि त्या इतर टेबलांवर ठेवते. पालक प्रती वाचतात. शिक्षिका अजूनही फक्त एकाच खऱ्या नोंदवहीत लिहितात, आणि एक निरोप्या प्रत्येक नवीन grade प्रतींपर्यंत पोहोचवतो.

निरोप्याला एक क्षण लागतो. कतरिनाची शिक्षिका 10:00:00 ला "A+" लिहिते. कतरिना लगेच एका प्रतीकडे धावते आणि पाहते — प्रतीत अजून काहीच नाही! एका क्षणाने निरोप्या पोहोचतो आणि A+ दिसू लागतो. तो क्षण म्हणजे replica lag.

दुसरी अडचण: नोंदवहीच्या खोलीत 500 खुर्च्या आहेत. 800 मदतनीसांना प्रत्येकी स्वतःची खुर्ची हवी आहे — 300 जणांना परत पाठवले जाते. म्हणून दीपिका दारावर एक स्वागतिका (proxy) बसवते, जिच्याकडे 100 खुर्च्या आहेत. मदतनीस त्या खुर्च्यांवर आळीपाळीने बसतात. कोणालाही परत पाठवले जात नाही.

🗺️ आकृती

flowchart LR
    app["🍳 800 Lambda environments"] --> px["🛎️ RDS Proxy<br/>pool of 100 connections"]
    px -->|"writes + read-your-own-writes"| pri["📒 primary<br/>max_connections 500"]
    px -->|"other reads"| rep1["📚 read replica 1"]
    px -->|"other reads"| rep2["📚 read replica 2"]
    pri -.->|"asynchronous copy<br/>lag 200 ms"| rep1
    pri -.->|"asynchronous copy"| rep2

🗺️ रेखाटलेली आवृत्ती + एक lab: https://school-edh.pages.dev/scaling/lesson-diagrams.html#l10

❓ काय

Primary — writes घेणारा एकमेव database instance.
Read replica — फक्त वाचण्याची प्रत. Amazon RDS बदल तिच्याकडे asynchronously copy करते: primary replica ची वाट पाहत नाही. Replicas तुम्ही जास्त reads साठी जोडता, जास्त writes साठी नाही.
Replica lag — replica primary च्या किती मागे आहे. CloudWatch ते ReplicaLag metric (सेकंदांत) म्हणून दाखवते. Primary खूप writes करतो किंवा replica खूप लहान असते तेव्हा ते वाढते.
Read-your-own-writes — user ने write केल्यानंतर थोड्या वेळासाठी (किंवा replica मागचे भरून काढेपर्यंत) त्याच user चे reads primary कडे पाठवा, म्हणजे तिला तिचा बदल दिसतो.
Amazon Aurora — replicas writer सोबत एकच storage layer वाटून घेतात; एका cluster मध्ये जास्तीत जास्त 15 Aurora Replicas असू शकतात, आणि reader endpoint connections त्यांच्यात पसरवतो.
Connection — प्रत्येक client connection साठी database ला memory आणि एक process किंवा thread खर्च करावा लागतो. max_connections ही मर्यादा आहे; तिचा default instance च्या आकारावर अवलंबून असतो.
Connection storm — अनेक नवीन callers (Lambdas, pods) एकाच वेळी प्रत्येकी स्वतःचे connections उघडतात. max_connections पलीकडे गेले की database ते नाकारतो.
Connection pool / RDS Proxy — proxy database शी खऱ्या connections चा एक लहान pool ठेवतो आणि ते अनेक client connections मध्ये वाटून देतो. RDS Proxy हा AWS चा managed proxy आहे (PostgreSQL साठी PgBouncer हा स्वतः चालवला जाणारा नेहमीचा पर्याय). Failover च्या वेळीही तो मदत करतो.

🤔 का

कारण निकालाच्या दिवशी database चे बहुतेक काम वाचण्याचे असते, आणि reads प्रतींवर पसरवता येतात. पण प्रती थोड्या मागे असतात — याकडे दुर्लक्ष केले तर शिक्षिकेने नवीन grade save केल्यावर लगेच पालकाला जुना grade दिसतो. आणि कारण serverless आणि pods काही सेकंदांत शेकडो प्रतींपर्यंत scale होतात (धडे 07–08); pool नसेल तर प्रत्येक प्रतीचे connection म्हणजे ठरावीक खुर्च्या असलेल्या खोलीतली आणखी एक खुर्ची.

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

scale/sim.py मधले Replica(lag_ms) प्रत्येक write त्याच्या वेळेसह साठवते; read(key, t_ms) हे t_ms च्या किमान lag_ms आधी लिहिलेली सर्वात नवीन value परत करते, नाहीतर None. connections(functions, per_function, max_connections, proxy_pool=None) हे callers ना किती connections हवे आहेत, database किती उघडतो, आणि किती नाकारले जातात हे परत करते — proxy असेल तर database ला फक्त pool दिसतो.

🧪 करून पाहा

python3 scale/demo.py replicas
python3 - <<'EOF'
import sys; sys.path.insert(0, "scale"); from sim import Replica, connections
for lag in (20, 200, 2000):
    r = Replica(lag_ms=lag); r.write("grade:katrina", "A+", 1000)
    print(f"lag {lag:>4} ms → read at t=1100: {r.read('grade:katrina', 1100)} · at t=3500: {r.read('grade:katrina', 3500)}")
r = Replica(200); r.write("grade:katrina", "B", 1000); r.write("grade:katrina", "A+", 2000)
print("regraded at t=2000; replica at t=2100 says:", r.read("grade:katrina", 2100))
for fns, per, pool in ((300, 1, None), (800, 2, None), (800, 2, 100), (800, 2, 600)):
    print(f"{fns} × {per} connection(s), max 500, pool {pool} →", connections(fns, per, 500, pool))
EOF

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

replicas हे छापते:

   read from the replica at t=1050 ms → None
   read from the replica at t=1150 ms → None
   read from the replica at t=1250 ms → A+

मग ── 800 Lambda environments × 1 connection, max_connections 500, proxy pool — → opened 500, refused 300 आणि ── 800 Lambda environments × 1 connection, max_connections 500, proxy pool 100 → opened 100, refused 0.

तुमचा snippet हे छापतो:

lag   20 ms → read at t=1100: A+ · at t=3500: A+
lag  200 ms → read at t=1100: None · at t=3500: A+
lag 2000 ms → read at t=1100: None · at t=3500: A+
regraded at t=2000; replica at t=2100 says: B
300 × 1 connection(s), max 500, pool None → {'wanted': 300, 'opened': 300, 'refused': 0}
800 × 2 connection(s), max 500, pool None → {'wanted': 1600, 'opened': 500, 'refused': 1100}
800 × 2 connection(s), max 500, pool 100 → {'wanted': 1600, 'opened': 100, 'refused': 0}
800 × 2 connection(s), max 500, pool 600 → {'wanted': 1600, 'opened': 500, 'refused': 100}

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

Lag म्हणजे फक्त "अजून काहीच नाही" असे नाही — ती जुनी value सुद्धा असू शकते: A+ मध्ये regrade झाल्यानंतर 100 ms नी replica ने B सांगितले, जे जास्त वाईट आहे. Proxy storm सुधारतो, पण फक्त त्याचा pool max_connections पेक्षा लहान असेल तरच: 600 च्या pool मध्ये अजूनही 100 नाकारले जातात.

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

user ने write केल्यानंतर लगेच तिचा read replica कडे पाठवणे
हळू writes सुधारण्यासाठी replicas जोडणे — प्रत्येक replica ला अजूनही प्रत्येक write लागू करावा लागतो
प्रत्येक request साठी नवीन connection उघडणे, किंवा प्रत्येक pod ला मोठा pool × 100 pods
database च्या max_connections पेक्षा मोठा proxy pool
ReplicaLag वर alarm नाही, त्यामुळे तासन्तास मागे असलेली replica अजूनही reads घेते
proxy connections धरून ठेवणारे लांब transactions, त्यामुळे proxy ते वाटून देऊ शकत नाही

🏭 प्रत्यक्ष वापरात

खऱ्या account वर — एक read replica जोडा आणि तिचा lag पाहा:

aws rds create-db-instance-read-replica --db-instance-identifier school-db-replica-1 \
    --source-db-instance-identifier school-db --db-instance-class db.r7g.large
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name ReplicaLag \
    --dimensions Name=DBInstanceIdentifier,Value=school-db-replica-1 \
    --statistics Maximum --period 60 \
    --start-time 2026-05-20T03:00:00Z --end-time 2026-05-20T05:00:00Z

Primary समोर RDS Proxy तयार करा (secret मध्ये database user आणि password असतो; role मुळे proxy तो वाचू शकतो):

aws rds create-db-proxy --db-proxy-name school-proxy --engine-family POSTGRESQL \
    --auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:ap-south-1:111122223333:secret:school-db-app-AbCdEf","IAMAuth":"REQUIRED"}]' \
    --role-arn arn:aws:iam::111122223333:role/school-proxy-secrets \
    --vpc-subnet-ids subnet-0aaa1111bbbb2222c subnet-0ddd3333eeee4444f \
    --vpc-security-group-ids sg-0123456789abcdef0 --require-tls
aws rds register-db-proxy-targets --db-proxy-name school-proxy --db-instance-identifiers school-db
aws rds modify-db-proxy-target-group --db-proxy-name school-proxy --target-group-name default \
    --connection-pool-config MaxConnectionsPercent=50

PostgreSQL वर खरी मर्यादा तपासा:

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

🏭 प्रत्यक्ष वापरात हे का महत्त्वाचे: code मध्ये हेतूनुसार route करा — db.write, db.read (replica) आणि db.read_fresh (primary) — म्हणजे कोणीही चुकून read-after-write replica कडे पाठवत नाही. पहिला Lambda database शी बोलण्याआधीच त्याच्यासमोर proxy ठेवा.

⏭️ पुढे

प्रती reads ला मदत करतात. पण writes एका primary ला पुरून उरतात, किंवा data एका machine ला पुरून उरतो, तेव्हा नोंदवहीची विभागणी करावीच लागते. पुढे: partitioning आणि DynamoDB.

git checkout lesson-11-partitioning

📚 Lesson 10 — Read replicas + connection pools: copies of the register

📍 You are here: Lesson 10 of 13 · Previous: lesson-09-caching-the-database · Next: lesson-11-partitioning

📦 What's in this branch

Lessons 01–09, plus two database tools: read replicas (copies that serve reads and lag a little behind) and a connection pool (RDS Proxy) that lets hundreds of callers share a small number of database connections. replicas() in scale/demo.py shows replica lag and a connection storm.

🧒 Explain like I'm 5

The office has one register 📒. Teachers write grades in it. Thousands of parents want to read it. Only one person can look at a page at a time.

So Dipika makes copies of the register 📚 and puts them on other tables. Parents read the copies. Teachers still write only in the one real register, and a runner carries each new grade to the copies.

The runner needs a moment. Katrina's teacher writes "A+" at 10:00:00. Katrina runs to a copy at once and looks — the copy still shows nothing! A moment later, the runner arrives and the A+ appears. That moment is replica lag.

A second problem: the register room has 500 chairs. 800 helpers each want their own chair — 300 are sent away. So Dipika puts a receptionist at the door (the proxy) with 100 chairs. Helpers take turns on those chairs. Nobody is sent away.

🗺️ Diagram

flowchart LR
    app["🍳 800 Lambda environments"] --> px["🛎️ RDS Proxy<br/>pool of 100 connections"]
    px -->|"writes + read-your-own-writes"| pri["📒 primary<br/>max_connections 500"]
    px -->|"other reads"| rep1["📚 read replica 1"]
    px -->|"other reads"| rep2["📚 read replica 2"]
    pri -.->|"asynchronous copy<br/>lag 200 ms"| rep1
    pri -.->|"asynchronous copy"| rep2

🗺️ Drawn version + a lab: https://school-edh.pages.dev/scaling/lesson-diagrams.html#l10

❓ What

Primary — the one database instance that takes writes.
Read replica — a read-only copy. Amazon RDS copies changes to it asynchronously: the primary does not wait for the replica. You add replicas for more reads, not more writes.
Replica lag — how far a replica is behind the primary. CloudWatch shows it as the ReplicaLag metric (seconds). It grows when the primary writes a lot or the replica is too small.
Read-your-own-writes — after a user writes, send that user's reads to the primary for a short time (or until the replica has caught up), so she sees her change.
Amazon Aurora — replicas share one storage layer with the writer; a cluster can have up to 15 Aurora Replicas, and the reader endpoint spreads connections across them.
Connection — each client connection costs the database memory and a process or thread. max_connections is the limit; its default depends on the instance size.
Connection storm — many new callers (Lambdas, pods) each open their own connections at once. Past max_connections, the database refuses them.
Connection pool / RDS Proxy — a proxy keeps a small pool of real connections to the database and shares them among many client connections. RDS Proxy is AWS's managed one (PgBouncer is a common self-run one for PostgreSQL). It also helps during a failover.

🤔 Why

Because on results day most database work is reading, and reads can be spread over copies. But copies are a little behind — ignore that and a parent sees an old grade right after a teacher saved the new one. And because serverless and pods scale to hundreds of copies in seconds (lessons 07–08); without a pool, each copy's connection is one more chair in a room with a fixed number of chairs.

🔧 How (in this repo)

Replica(lag_ms) in scale/sim.py stores each write with its time; read(key, t_ms) returns the latest value written at least lag_ms before t_ms, or None. connections(functions, per_function, max_connections, proxy_pool=None) returns how many connections the callers want, how many the database opens, and how many are refused — with a proxy, the database only sees the pool.

🧪 Try it

python3 scale/demo.py replicas
python3 - <<'EOF'
import sys; sys.path.insert(0, "scale"); from sim import Replica, connections
for lag in (20, 200, 2000):
    r = Replica(lag_ms=lag); r.write("grade:katrina", "A+", 1000)
    print(f"lag {lag:>4} ms → read at t=1100: {r.read('grade:katrina', 1100)} · at t=3500: {r.read('grade:katrina', 3500)}")
r = Replica(200); r.write("grade:katrina", "B", 1000); r.write("grade:katrina", "A+", 2000)
print("regraded at t=2000; replica at t=2100 says:", r.read("grade:katrina", 2100))
for fns, per, pool in ((300, 1, None), (800, 2, None), (800, 2, 100), (800, 2, 600)):
    print(f"{fns} × {per} connection(s), max 500, pool {pool} →", connections(fns, per, 500, pool))
EOF

✅ Verify — what you should see

replicas prints:

   read from the replica at t=1050 ms → None
   read from the replica at t=1150 ms → None
   read from the replica at t=1250 ms → A+

then ── 800 Lambda environments × 1 connection, max_connections 500, proxy pool — → opened 500, refused 300 and ── 800 Lambda environments × 1 connection, max_connections 500, proxy pool 100 → opened 100, refused 0.

Your snippet prints:

lag   20 ms → read at t=1100: A+ · at t=3500: A+
lag  200 ms → read at t=1100: None · at t=3500: A+
lag 2000 ms → read at t=1100: None · at t=3500: A+
regraded at t=2000; replica at t=2100 says: B
300 × 1 connection(s), max 500, pool None → {'wanted': 300, 'opened': 300, 'refused': 0}
800 × 2 connection(s), max 500, pool None → {'wanted': 1600, 'opened': 500, 'refused': 1100}
800 × 2 connection(s), max 500, pool 100 → {'wanted': 1600, 'opened': 100, 'refused': 0}
800 × 2 connection(s), max 500, pool 600 → {'wanted': 1600, 'opened': 500, 'refused': 100}

🏁 What you just proved

Lag is not only "nothing yet" — it can be the old value: the replica said B 100 ms after the regrade to A+, which is worse. A proxy fixes the storm, but only if its pool is smaller than max_connections: a pool of 600 still gets 100 refused.

⚠️ Common mistakes

sending a user's read to a replica right after her write
adding replicas to fix slow writes — every replica still applies every write
opening a new connection per request, or a big pool per pod × 100 pods
a proxy pool sized larger than the database's max_connections
no alarm on ReplicaLag, so a replica hours behind still takes reads
long transactions that hold proxy connections, so the proxy cannot share them

🏭 In production

On a real account — add a read replica and watch its lag:

aws rds create-db-instance-read-replica --db-instance-identifier school-db-replica-1 \
    --source-db-instance-identifier school-db --db-instance-class db.r7g.large
aws cloudwatch get-metric-statistics --namespace AWS/RDS --metric-name ReplicaLag \
    --dimensions Name=DBInstanceIdentifier,Value=school-db-replica-1 \
    --statistics Maximum --period 60 \
    --start-time 2026-05-20T03:00:00Z --end-time 2026-05-20T05:00:00Z

Create RDS Proxy in front of the primary (the secret holds the database user and password; the role lets the proxy read it):

aws rds create-db-proxy --db-proxy-name school-proxy --engine-family POSTGRESQL \
    --auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:ap-south-1:111122223333:secret:school-db-app-AbCdEf","IAMAuth":"REQUIRED"}]' \
    --role-arn arn:aws:iam::111122223333:role/school-proxy-secrets \
    --vpc-subnet-ids subnet-0aaa1111bbbb2222c subnet-0ddd3333eeee4444f \
    --vpc-security-group-ids sg-0123456789abcdef0 --require-tls
aws rds register-db-proxy-targets --db-proxy-name school-proxy --db-instance-identifiers school-db
aws rds modify-db-proxy-target-group --db-proxy-name school-proxy --target-group-name default \
    --connection-pool-config MaxConnectionsPercent=50

Check the real limit on PostgreSQL:

SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

🏭 Why this matters in production: route by intent in code — db.write, db.read (replica) and db.read_fresh (primary) — so nobody sends a read-after-write to a replica by accident. Put a proxy in front of the database before the first Lambda talks to it.

⏭️ Next

Copies help reads. But when writes outgrow one primary, or the data outgrows one machine, you must split the register. Next: partitioning and DynamoDB.

git checkout lesson-11-partitioning

← Previouscaching the database Next →partitioning

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