The N+1 Tax: Rewriting Python Loops as SQL Views

N+1 query patterns are one of those bugs that never look like a bug. The code reads fine — loop over patients, fetch each patient's related records, build the response. It's the natural way to write it in an ORM, and it works correctly on a laptop with ten rows of test data. The problem only shows up in production, under load, once "loop over patients" means a few hundred round trips to the database for a single API response.

That was the shape of the problem on the AI-specific patient views in AgeCare: a unified patient timeline, a clinical profile, behaviour intelligence, and compliance features, several of which were built as Python loops issuing one query per patient per related entity. The fix — set-based PostgreSQL views instead of per-row Python loops — produced a 2.64× peak speedup, a 44.4% payload reduction, and a 75% cut in downstream LLM cost, benchmarked across 18 runs. The benchmark methodology matters as much as the fix, so I'll cover both.

What "N+1" actually costs

The pattern, simplified:

patients = await get_patients(facility_id)
results = []
for patient in patients:
    vitals = await get_latest_vitals(patient.id)      # 1 query per patient
    meds = await get_active_medications(patient.id)    # 1 query per patient
    incidents = await get_recent_incidents(patient.id)  # 1 query per patient
    results.append(build_summary(patient, vitals, meds, incidents))

For a facility with 150 residents, that's 1 + 150×3 = 451 round trips for one dashboard load. Each round trip pays fixed overhead — network latency, connection scheduling, query planning — on top of the actual work, and none of that overhead does anything useful. The database also loses the ability to optimize across the whole request: it's answering 451 tiny, unrelated questions instead of one well-formed question it could plan efficiently.

Replacing loops with views

The fix moves the join logic into PostgreSQL as a set-based view, so the database answers "give me the latest vitals, active medications, and recent incidents for every resident in this facility" as a single query it can plan holistically — index usage, join order, and parallelism all handled by the query planner instead of by 451 separate round trips:

create view patient_clinical_profile as
select
    p.id as patient_id,
    p.facility_id,
    v.latest_vitals,
    m.active_medications,
    i.recent_incidents
from patients p
left join lateral (
    select jsonb_agg(row_to_json(x)) as latest_vitals
    from (
        select * from vitals
        where patient_id = p.id
        order by recorded_at desc
        limit 5
    ) x
) v on true
left join lateral (
    select jsonb_agg(row_to_json(x)) as active_medications
    from medications x
    where x.patient_id = p.id and x.active = true
) m on true
left join lateral (
    select jsonb_agg(row_to_json(x)) as recent_incidents
    from incidents x
    where x.patient_id = p.id and x.occurred_at > now() - interval '30 days'
) i on true;

LATERAL joins are the key piece — they let each subquery reference p.id from the outer row, which is what makes "top 5 vitals per patient" expressible as a join instead of a loop. The application code collapses to one query against the view:

async def get_facility_profiles(facility_id: int):
    return await db.fetch_all(
        "select * from patient_clinical_profile where facility_id = :fid",
        {"fid": facility_id},
    )

Five AI-specific views of this shape now power the unified patient timeline, clinical profile, behaviour intelligence, and compliance features — each one replacing what used to be a Python-side loop with a single set-based query.

Benchmarking it properly

A single before/after timing run is nearly worthless — cache state, connection pool warmup, and background load can each swing a single measurement by more than the effect you're trying to measure. The benchmark ran both implementations across 18 runs against equivalent data volumes and load conditions, and the payload comparison was measured independently of timing, since a smaller response shape (the view returns exactly the aggregated JSON the frontend needs, not raw rows to be reshaped client-side) is a separate win from query speed.

The three numbers that came out of it measure three different things:

• 2.64× peak speedup — the query/response time improvement under peak load, where round-trip overhead in the N+1 version dominates and set-based execution pays off most.
• 44.4% payload reduction — the view returns pre-aggregated JSON shaped for the consumer, instead of the wider row sets the Python loop used to assemble and reshape.
• 75% LLM cost savings — downstream: several of these views feed data directly into LLM prompts (clinical summarisation, behaviour analysis), so a smaller, cleaner payload directly shrinks token spend on every call that consumes it.

That last number is the one worth underlining. It's easy to treat "backend SQL optimisation" and "LLM cost" as unrelated line items, but in a system where the database is the thing assembling context for an LLM call, they're the same problem. A leaner query layer means a leaner prompt.

When this pattern is and isn't worth it

Set-based views aren't a universal upgrade. They push logic into the database, which means schema changes now touch SQL as well as application code, and a LATERAL join with three subqueries is harder to read at a glance than a Python loop — the loop version is more debuggable in isolation, one entity at a time. The trade is worth it when the same query shape gets hit repeatedly at scale, which is exactly the profile of a facility dashboard loaded by every user, every session. For a one-off admin report run twice a month, I'd leave the loop alone.