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

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

🔁 धडा 07 — I/O, N+1 आणि batching: प्रत्येक हस्तांतराला एक फेरी लागते

📍 तुम्ही इथे आहात: 12 पैकी धडा 07 · मागे: lesson-06-caching · पुढे: lesson-08-concurrency

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

धडे 01–06, अधिक I/O: database, disk किंवा दुसऱ्या service कडे जाणाऱ्या प्रत्येक फेरीला ठराविक toll भरावा लागतो, तुम्ही नेत असलेली गोष्ट कितीही लहान असली तरी. प्रसिद्ध नासाडी म्हणजे N+1 query: यादीसाठी एक query, मग प्रत्येक item साठी आणखी एक. उपाय आहेत batching (IN (...), एक JOIN) आणि buffering. perf/demo.py मधले io() आणि perf/sim.py मधले make_db, counting, n_plus_one, in_batch, joined आणि write_lines.

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

प्रमाणपत्रांसाठी रिले संघाला प्रत्येकाचा सर्वोत्तम lap हवा आहे. 🏅 Laps मैदानाच्या पलीकडच्या office मध्ये लिहिलेले आहेत.

हळू पद्धत: दीपिका office कडे धावत जाते. "कृपया मला धावपटूंची यादी द्या." धावत परत येते. मग 50 पैकी प्रत्येक धावपटूसाठी: office कडे धावत जाते, "या धावपटूचा सर्वोत्तम lap कोणता?", धावत परत येते. म्हणजे मैदान ओलांडून 51 फेऱ्या. 🏃‍♀️🏃‍♀️🏃‍♀️
त्यापेक्षा चांगली: यादीसाठी एक फेरी, आणि नावांची संपूर्ण यादी घेऊन एक फेरी: "या सगळ्यांचे सर्वोत्तम laps द्या, कृपया." 2 फेऱ्या.
सर्वोत्तम: एक फेरी: "प्रत्येक धावपटू त्याच्या सर्वोत्तम lap सह द्या." 1 फेरी.

प्रत्येक फेरीला फक्त तिथे जाऊन परत यायलाच वेळ लागतो, उत्तर एक छोटा आकडा असला तरी. म्हणून प्रत्येक फेरीत अनेक गोष्टी न्या.

लिहिण्याचेही असेच आहे. प्रत्येक result वेगळ्या card वर लिहून post करणे हळू आहे. आधी संपूर्ण पाकीट भरून ते एकदाच post करणे जलद आहे. यालाच buffering म्हणतात. ✉️

🗺️ आकृती

flowchart LR
    app["🏃 the app"] -->|"1 query: the runners"| db[("🗃️ SQLite")]
    app -->|"+50 queries: one per runner"| db
    n1["N+1 → 51 queries<br/>51 ms at 1 ms a round trip"]
    b["IN (...) → 2 queries"]
    j["JOIN + GROUP BY → 1 query"]
    w["10,000 lines · 210,000 bytes:<br/>unbuffered 10,000 writes · buffered 26"]

🗺️ काढलेली आकृती + एक lab: https://school-edh.pages.dev/performance/lesson-diagrams.html#l07

❓ काय

I/O (input/output) — CPU च्या बाहेर जाणारी कोणतीही गोष्ट: disk, network, database, दुसरी service. सहसा गणनेपेक्षा खूप हळू, आणि program बहुतेक वेळ वाट पाहत असतो.
Round trip — request पाठवा, उत्तराची वाट पाहा. त्याचा ठराविक भाग (network latency, parsing, system call) प्रत्येक फेरीला भरावा लागतो, उत्तर कितीही लहान असले तरी. एकाच data center मध्ये database पर्यंतचा round trip बऱ्याचदा millisecond च्या अंशापासून काही milliseconds पर्यंत असतो; regions ओलांडून, दहापट milliseconds.
N+1 queries — यादीसाठी 1 query अधिक तपशीलांसाठी N queries. ते ORM मागे सहज लपते: for runner in Runner.objects.all(): runner.laps... objects वरच्या loop सारखे दिसते आणि प्रत्यक्षात queries वरचा loop असतो.
Batching — अनेक items नेणारी एक request: WHERE id IN (?, ?, ...), एक JOIN, bulk insert (executemany, COPY), batch API call. Batches ना मर्यादा असतात: SQLite default नुसार एका statement मध्ये जास्तीत जास्त 32,766 ? parameters ठेवू देते (version 3.32 आधी 999).
Buffering — अनेक छोटे writes memory मध्ये गोळा करा आणि एका मोठ्या write म्हणून पाठवा. File वरचा प्रत्येक raw write हा एक system call असतो, ज्याचा खर्च memory मध्ये bytes copy करण्यापेक्षा खूप जास्त असतो. Python चे open() default नुसार buffer करते; flush() buffer पाठवते, आणि os.fsync() operating system ला तो disk वर ठेवायला सांगते.
Connection reuse — TCP (आणि TLS) connection उघडणे हेच स्वतः अनेक round trips असते. Connection pools आणि HTTP keep-alive तो खर्च प्रत्येक request ला नव्हे, एकदाच भरतात.

🤔 का

कारण रोजच्या बहुतेक services आपला वेळ I/O मध्येच घालवतात. प्रत्येकी 1 ms च्या 51 queries करणारा endpoint 51 ms वाट पाहण्यात घालवतो, Python कितीही जलद असला तरी, आणि load खाली त्या queries database मध्ये रांगेतही उभ्या राहतात (धडा 09). N+1 local database आणि 3 rows असलेल्या laptop वर दिसतही नाही. ते production मध्ये दिसते, network आणि 200 rows सह.

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

perf/sim.py मधले make_db() 50 धावपटू आणि प्रत्येकी 4 laps असलेला in-memory SQLite database बनवते. counting(conn) sqlite3 चा trace callback सुरू करते, जो connection चालवत असलेल्या प्रत्येक SQL statement सह एक function call करतो — म्हणून query counts खरे आहेत. n_plus_one, in_batch आणि joined तेच उत्तर तीन प्रकारे मिळवतात. SQLite याच process च्या आत चालतो, म्हणून इथे त्याच्या queries स्वस्त आहेत; "प्रत्येक round trip ला 1 ms" हा network वरच्या database server चा model आहे. write_lines(lines, buffered) CountingRaw मधून लिहिते, जी प्रत्येक raw write मोजणारी बनावट file आहे, Python च्या खऱ्या io.BufferedWriter सह किंवा त्याशिवाय.

🧪 करून पाहा

python3 perf/demo.py io
python3 - <<'EOF'
import sys; sys.path.insert(0, "perf"); import sim
for n in (10, 50, 200):
    db = sim.make_db(n_runners=n); log = sim.counting(db)
    sim.n_plus_one(db); a = len(log); log.clear(); sim.joined(db); b = len(log)
    print(f"{n:>3} runners → N+1 {a:>3} queries · JOIN {b} · at 2 ms a round trip: {a * 2:>3} ms vs {b * 2} ms")
db = sim.make_db(n_runners=3); log = sim.counting(db); sim.n_plus_one(db)
print("\n".join(log))
lines = [b"x" * 20 + b"\n"] * 10_000
for size in (1024, 8192, 65536):
    print(f"buffer {size:>5} bytes → {sim.write_lines(lines, True, size)[0]:>4} raw writes")
EOF

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

io हे छापते:

── best lap for each of 50 runners, from SQLite (sqlite3's trace callback counts the queries)
   N+1: one query for the runners, then one per runner → 51 queries
   batched with IN (...)                            → 2 queries
   one JOIN + GROUP BY                              → 1 query · same answer all three ways: True
   at 1 ms round trip to a database server: 51 ms vs 2 ms vs 1 ms of waiting (here SQLite is in-process)
── writing 10,000 result lines (210,000 bytes) unbuffered     → 10,000 raw writes
── writing 10,000 result lines (210,000 bytes) buffered (8 KB) → 26 raw writes

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

 10 runners → N+1  11 queries · JOIN 1 · at 2 ms a round trip:  22 ms vs 2 ms
 50 runners → N+1  51 queries · JOIN 1 · at 2 ms a round trip: 102 ms vs 2 ms
200 runners → N+1 201 queries · JOIN 1 · at 2 ms a round trip: 402 ms vs 2 ms
SELECT id, name FROM runners ORDER BY id
SELECT MIN(lap_ms) FROM laps WHERE runner_id = 1
SELECT MIN(lap_ms) FROM laps WHERE runner_id = 2
SELECT MIN(lap_ms) FROM laps WHERE runner_id = 3
buffer  1024 bytes →  209 raw writes
buffer  8192 bytes →   26 raw writes
buffer 65536 bytes →    4 raw writes

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

N+1 data सोबत वाढते — 11, 51, 201 queries — तर JOIN 1 वरच राहतो. Trace log नेमके तेच दाखवतो जे ORM लपवेल: त्याच आकाराची query, प्रत्येक धावपटूसाठी एकदा. Buffering ने 10,000 raw writes 26 वर आणले (210,000 bytes ÷ 8192 ≈ 25.6); मोठा buffer म्हणजे कमी writes, आणि program flush करण्याआधी crash झाल्यास जास्त data हरवणे.

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

rows वरच्या loop च्या आत query (किंवा HTTP call)
ORM template मध्ये relations lazy-load करणे, प्रत्येक row साठी एक query, लक्षात न येता
100,000 values असलेली एक प्रचंड IN यादी — काहीशे किंवा काही हजारांच्या तुकड्यांमध्ये batch करा
"सुरक्षिततेसाठी" प्रत्येक ओळीनंतर flush(), ज्यामुळे buffering बंद होते
pool वापरण्याऐवजी प्रत्येक request साठी नवे database connection उघडणे
फक्त तुमच्या code मधला वेळ मोजणे आणि round trips ची संख्या न मोजणे

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

खऱ्या account वर — N+1 टाळण्याची ORM पद्धत. Django:

runners = Runner.objects.prefetch_related("laps")          # 2 queries, not N+1
laps = Lap.objects.select_related("runner")                # 1 query with a JOIN
from django.test.utils import CaptureQueriesContext        # count queries in a test

SQLAlchemy:

from sqlalchemy.orm import selectinload
stmt = select(Runner).options(selectinload(Runner.laps))   # 1 + 1 queries, IN (...)

PostgreSQL pg_stat_statements extension वापरून प्रत्येक query आकारासाठी calls मोजतो — प्रचंड calls आणि अगदी लहान mean_exec_time असलेली query बऱ्याचदा N+1 असते:

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, query
FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;

Bulk inserts: अनेक rows साठी एक statement, किंवा PostgreSQL मध्ये COPY:

cur.executemany("INSERT INTO laps (runner_id, lap_ms) VALUES (%s, %s)", rows)

🏭 Production मध्ये हे का महत्त्वाचे आहे: तुमच्या tests मध्ये query counter ठेवा (Django चे assertNumQueries, किंवा SQLAlchemy event listener) आणि एखाद्या page चा count data सोबत वाढला तर build fail करा. धडा 12 त्याचे budget मध्ये रूपांतर करतो.

⏭️ पुढे

दीपिका office कडे चालत गेली तेव्हा संघातले बाकीचे नुसते उभे राहिले. वाट पाहताना ते दुसरे काही करू शकले असते का? पुढे: concurrency — threads, asyncio, processes, आणि GIL.

git checkout lesson-08-concurrency

🔁 Lesson 07 — I/O, N+1 & batching: every hand-off costs a trip

📍 You are here: Lesson 07 of 12 · Previous: lesson-06-caching · Next: lesson-08-concurrency

📦 What's in this branch

Lessons 01–06, plus I/O: every trip to a database, a disk or another service pays a fixed toll, however small the thing you carry. The famous waste is the N+1 query: one query for a list, then one more per item. The fixes are batching (IN (...), a JOIN) and buffering. io() in perf/demo.py and make_db, counting, n_plus_one, in_batch, joined and write_lines in perf/sim.py.

🧒 Explain like I'm 5

The relay team needs everyone's best lap for the certificates. 🏅 The laps are written in the office across the field.

The slow way: Dipika runs to the office. "Please give me the list of runners." Runs back. Then for each of the 50 runners: runs to the office, "what is this runner's best lap?", runs back. That is 51 trips across the field. 🏃‍♀️🏃‍♀️🏃‍♀️
Better: one trip for the list, and one trip with the whole list of names: "best laps for all of these, please." 2 trips.
Best: one trip: "give me every runner with their best lap." 1 trip.

Every trip takes time just to walk there and back, even if the answer is one small number. So carry many things per trip.

It is the same with writing. Writing each result on a separate card and posting it is slow. Filling a whole envelope first and posting it once is fast. That is buffering. ✉️

🗺️ Diagram

flowchart LR
    app["🏃 the app"] -->|"1 query: the runners"| db[("🗃️ SQLite")]
    app -->|"+50 queries: one per runner"| db
    n1["N+1 → 51 queries<br/>51 ms at 1 ms a round trip"]
    b["IN (...) → 2 queries"]
    j["JOIN + GROUP BY → 1 query"]
    w["10,000 lines · 210,000 bytes:<br/>unbuffered 10,000 writes · buffered 26"]

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

❓ What

I/O (input/output) — anything that leaves the CPU: disk, network, a database, another service. Usually far slower than computing, and the program mostly waits.
Round trip — send a request, wait for the answer. Its fixed part (network latency, parsing, a system call) is paid per trip, however small the answer is. Inside one data center a round trip to a database is often a fraction of a millisecond to a few milliseconds; across regions, tens of milliseconds.
N+1 queries — 1 query for the list plus N queries for the details. It hides easily behind an ORM: for runner in Runner.objects.all(): runner.laps... looks like a loop over objects and is a loop over queries.
Batching — one request that carries many items: WHERE id IN (?, ?, ...), a JOIN, a bulk insert (executemany, COPY), a batch API call. Batches have limits: SQLite allows up to 32,766 ? parameters per statement by default (999 before version 3.32).
Buffering — collect many small writes in memory and send them as one big write. Each raw write to a file is a system call, which costs far more than copying bytes in memory. Python's open() buffers by default; flush() sends the buffer, and os.fsync() asks the operating system to put it on the disk.
Connection reuse — opening a TCP (and TLS) connection is itself several round trips. Connection pools and HTTP keep-alive pay that once, not per request.

🤔 Why

Because I/O is where most everyday services spend their time. An endpoint that makes 51 queries at 1 ms each spends 51 ms waiting, however fast the Python is, and under load those queries also queue up in the database (lesson 09). N+1 is also invisible on a laptop with a local database and 3 rows. It shows up in production, with a network and 200 rows.

🔧 How (in this repo)

make_db() in perf/sim.py builds an in-memory SQLite database with 50 runners and 4 laps each. counting(conn) turns on sqlite3's trace callback, which calls a function with every SQL statement the connection runs — so the query counts are real. n_plus_one, in_batch and joined get the same answer three ways. SQLite runs inside this process, so its queries are cheap here; the "1 ms per round trip" is a model of a database server on a network. write_lines(lines, buffered) writes through CountingRaw, a pretend file that counts each raw write, with or without Python's real io.BufferedWriter.

🧪 Try it

python3 perf/demo.py io
python3 - <<'EOF'
import sys; sys.path.insert(0, "perf"); import sim
for n in (10, 50, 200):
    db = sim.make_db(n_runners=n); log = sim.counting(db)
    sim.n_plus_one(db); a = len(log); log.clear(); sim.joined(db); b = len(log)
    print(f"{n:>3} runners → N+1 {a:>3} queries · JOIN {b} · at 2 ms a round trip: {a * 2:>3} ms vs {b * 2} ms")
db = sim.make_db(n_runners=3); log = sim.counting(db); sim.n_plus_one(db)
print("\n".join(log))
lines = [b"x" * 20 + b"\n"] * 10_000
for size in (1024, 8192, 65536):
    print(f"buffer {size:>5} bytes → {sim.write_lines(lines, True, size)[0]:>4} raw writes")
EOF

✅ Verify — what you should see

io prints:

── best lap for each of 50 runners, from SQLite (sqlite3's trace callback counts the queries)
   N+1: one query for the runners, then one per runner → 51 queries
   batched with IN (...)                            → 2 queries
   one JOIN + GROUP BY                              → 1 query · same answer all three ways: True
   at 1 ms round trip to a database server: 51 ms vs 2 ms vs 1 ms of waiting (here SQLite is in-process)
── writing 10,000 result lines (210,000 bytes) unbuffered     → 10,000 raw writes
── writing 10,000 result lines (210,000 bytes) buffered (8 KB) → 26 raw writes

Your snippet prints:

 10 runners → N+1  11 queries · JOIN 1 · at 2 ms a round trip:  22 ms vs 2 ms
 50 runners → N+1  51 queries · JOIN 1 · at 2 ms a round trip: 102 ms vs 2 ms
200 runners → N+1 201 queries · JOIN 1 · at 2 ms a round trip: 402 ms vs 2 ms
SELECT id, name FROM runners ORDER BY id
SELECT MIN(lap_ms) FROM laps WHERE runner_id = 1
SELECT MIN(lap_ms) FROM laps WHERE runner_id = 2
SELECT MIN(lap_ms) FROM laps WHERE runner_id = 3
buffer  1024 bytes →  209 raw writes
buffer  8192 bytes →   26 raw writes
buffer 65536 bytes →    4 raw writes

🏁 What you just proved

N+1 grows with the data — 11, 51, 201 queries — while the JOIN stays at 1. The trace log shows exactly what an ORM would hide: the same query shape, once per runner. Buffering cut 10,000 raw writes to 26 (210,000 bytes ÷ 8192 ≈ 25.6); a bigger buffer means fewer writes, and more data lost if the program crashes before it flushes.

⚠️ Common mistakes

a query (or an HTTP call) inside a loop over rows
lazy-loading relations in an ORM template, one query per row, without noticing
one giant IN list with 100,000 values — batch in chunks of a few hundred or thousand
flush() after every line "to be safe", which turns buffering off
opening a new database connection per request instead of using a pool
counting only the time in your code and not the number of round trips

🏭 In production

On a real account — the ORM way to avoid N+1. Django:

runners = Runner.objects.prefetch_related("laps")          # 2 queries, not N+1
laps = Lap.objects.select_related("runner")                # 1 query with a JOIN
from django.test.utils import CaptureQueriesContext        # count queries in a test

SQLAlchemy:

from sqlalchemy.orm import selectinload
stmt = select(Runner).options(selectinload(Runner.laps))   # 1 + 1 queries, IN (...)

PostgreSQL counts calls per query shape with the pg_stat_statements extension — a query with a huge calls and a tiny mean_exec_time is often an N+1:

SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, query
FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;

Bulk inserts: one statement for many rows, or COPY in PostgreSQL:

cur.executemany("INSERT INTO laps (runner_id, lap_ms) VALUES (%s, %s)", rows)

🏭 Why this matters in production: put a query counter in your tests (Django's assertNumQueries, or a SQLAlchemy event listener) and fail the build when a page's count grows with the data. Lesson 12 turns that into a budget.

⏭️ Next

While Dipika walked to the office, the rest of the team stood still. Could they do something else while waiting? Next: concurrency — threads, asyncio, processes, and the GIL.

git checkout lesson-08-concurrency

← Previouscaching Next →concurrency

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