Removes the last measured /api/compare bottleneck: the per-school supplementary DB round-trips.
Problem (measured on staging)
/api/compare scaled linearly with school count — ~37ms per school:
schools
time
baseline (data + marts)
~60ms
1
~155ms
3
~220ms
6
~340ms
get_supplementary_data(db, urn) ran ~5 sequential queries per school (Ofsted, census, admissions history, deprivation, finance). At N schools that's 5×N round-trips on a network-separated Postgres.
Fix
get_supplementary_data_batch(db, urns) fetches each table once with WHERE urn IN (…) and groups per-URN in Python — a constant 5 round-trips regardless of school count. Latest-per-URN selection (Ofsted, census, finance) is done by ordering urn, <date> DESC and keeping the first row per URN; admissions keeps the full per-URN history. Each table is queried independently so a missing/failing mart degrades that block to null without affecting the others.
get_supplementary_data(db, urn) becomes a thin wrapper over the batch, so /api/schools/{urn} (the detail page) is behaviourally unchanged. The compare endpoint makes one batched call.
Expected result
3-school compare from ~220ms toward ~90ms; the endpoint stops scaling with school count (6 schools should land near 3, not ~340ms).
Tests
48 backend (2 new): the batch runs exactly one query per table for many URNs and picks the latest row per URN; the single-URN wrapper still works; the compare endpoint's degradation + enrichment tests updated to the batched call.
Removes the last measured `/api/compare` bottleneck: the per-school supplementary DB round-trips.
## Problem (measured on staging)
`/api/compare` scaled linearly with school count — ~37ms per school:
| schools | time |
|---|---|
| baseline (data + marts) | ~60ms |
| 1 | ~155ms |
| 3 | ~220ms |
| 6 | ~340ms |
`get_supplementary_data(db, urn)` ran ~5 sequential queries per school (Ofsted, census, admissions history, deprivation, finance). At N schools that's 5×N round-trips on a network-separated Postgres.
## Fix
`get_supplementary_data_batch(db, urns)` fetches each table **once** with `WHERE urn IN (…)` and groups per-URN in Python — a constant 5 round-trips regardless of school count. Latest-per-URN selection (Ofsted, census, finance) is done by ordering `urn, <date> DESC` and keeping the first row per URN; admissions keeps the full per-URN history. Each table is queried independently so a missing/failing mart degrades that block to null without affecting the others.
`get_supplementary_data(db, urn)` becomes a thin wrapper over the batch, so `/api/schools/{urn}` (the detail page) is behaviourally unchanged. The compare endpoint makes one batched call.
## Expected result
3-school compare from ~220ms toward ~90ms; the endpoint stops scaling with school count (6 schools should land near 3, not ~340ms).
## Tests
48 backend (2 new): the batch runs exactly one query per table for many URNs and picks the latest row per URN; the single-URN wrapper still works; the compare endpoint's degradation + enrichment tests updated to the batched call.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB
get_supplementary_data ran ~5 sequential DB round-trips per URN, so
/api/compare scaled at ~37ms/school (measured on staging: 1 school 155ms,
3 schools 220ms, 6 schools 340ms). get_supplementary_data_batch fetches
each table once with WHERE urn IN (...) and groups in Python, collapsing
5*N round-trips to a constant 5. get_supplementary_data is now a thin
wrapper so the detail endpoint is unchanged; the compare endpoint makes
one batched call. Each table degrades independently on failure.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB
tudor
merged commit a9611e21c3 into main2026-07-14 21:57:27 +00:00
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Removes the last measured
/api/comparebottleneck: the per-school supplementary DB round-trips.Problem (measured on staging)
/api/comparescaled linearly with school count — ~37ms per school:get_supplementary_data(db, urn)ran ~5 sequential queries per school (Ofsted, census, admissions history, deprivation, finance). At N schools that's 5×N round-trips on a network-separated Postgres.Fix
get_supplementary_data_batch(db, urns)fetches each table once withWHERE urn IN (…)and groups per-URN in Python — a constant 5 round-trips regardless of school count. Latest-per-URN selection (Ofsted, census, finance) is done by orderingurn, <date> DESCand keeping the first row per URN; admissions keeps the full per-URN history. Each table is queried independently so a missing/failing mart degrades that block to null without affecting the others.get_supplementary_data(db, urn)becomes a thin wrapper over the batch, so/api/schools/{urn}(the detail page) is behaviourally unchanged. The compare endpoint makes one batched call.Expected result
3-school compare from ~220ms toward ~90ms; the endpoint stops scaling with school count (6 schools should land near 3, not ~340ms).
Tests
48 backend (2 new): the batch runs exactly one query per table for many URNs and picks the latest row per URN; the single-URN wrapper still works; the compare endpoint's degradation + enrichment tests updated to the batched call.
🤖 Generated with Claude Code
https://claude.ai/code/session_0146VHeLAWjDVE2B5uU67jCB