Fix list_persons N+1 (the ~4s person-page load) #246

2026-06-11T08:00:46-04:00

justin commented

2026-06-11 08:00:46 -04:00

Symptom: opening a person page took 4–5s on an idle server, single user.

Root cause: list_persons looped over every person calling privacy.person_visibility (which runs two get_membership_role queries per call) and _attach_primary_name (one name query per person). On the 2,324-person tree in question that is ~7,000 serialized DB round-trips per page load — and the person page fetches the whole person list to build its name-lookup map. Each query is ~1ms, but thousands serialized = seconds.

Fix:

Resolve the viewer's membership role once; members see the whole tree (full), so skip the per-person privacy engine entirely.
_attach_primary_names: one batched names query (person_id IN (...), ordered identically to the single-person query so it selects the same name) instead of one per person.
Same batching applied to the non-member path, search_persons, the deleted-persons list, and public_view_service.list_public_persons.

Member-path list_persons drops from ~3·N queries to ~3 total. The other tree-wide lists (events/relationships/media/citations) were already flat selects.

Regression test asserts list_persons issues a constant number of queries regardless of person count. Suite 103 passing. Backend-only, no migration.

🤖 Generated with Claude Code

**Symptom:** opening a person page took 4–5s on an idle server, single user. **Root cause:** `list_persons` looped over every person calling `privacy.person_visibility` (which runs **two** `get_membership_role` queries per call) **and** `_attach_primary_name` (one name query per person). On the 2,324-person tree in question that is **~7,000 serialized DB round-trips** per page load — and the person page fetches the whole person list to build its name-lookup map. Each query is ~1ms, but thousands serialized = seconds. **Fix:** - Resolve the viewer's membership role **once**; members see the whole tree (full), so skip the per-person privacy engine entirely. - `_attach_primary_names`: **one** batched names query (`person_id IN (...)`, ordered identically to the single-person query so it selects the same name) instead of one per person. - Same batching applied to the non-member path, `search_persons`, the deleted-persons list, and `public_view_service.list_public_persons`. Member-path `list_persons` drops from ~3·N queries to **~3 total**. The other tree-wide lists (events/relationships/media/citations) were already flat selects. **Regression test** asserts `list_persons` issues a *constant* number of queries regardless of person count. Suite **103 passing**. Backend-only, no migration. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

justin added 1 commit 2026-06-11 08:00:47 -04:00

Fix list_persons N+1 (the ~4s person-page load) 1562febdcf

Opening any person page on a large tree took 4-5s on an idle server. Root cause:
list_persons looped over every person calling privacy.person_visibility (which
issues TWO get_membership_role queries per call) AND _attach_primary_name (one
name query per person). On the reporter's 2,324-person tree that's ~7,000
serialized DB round-trips per page load — the person page fetches the full
person list to build its name-lookup map.

Fix:
- Resolve the viewer's membership role ONCE. Members see the whole tree (full),
  so skip the per-person privacy engine entirely.
- Add _attach_primary_names: one batched names query (person_id IN (...),
  ordered the same as the single-person query so it picks the same name) instead
  of one per person.
- Apply the same batching to the non-member path, search_persons, the deleted-
  persons list, and public_view_service.list_public_persons.

Member-path list_persons goes from ~3·N queries to ~3 total. Other tree-wide
list endpoints (events/relationships/media/citations) were already flat selects.

Adds a regression test that asserts list_persons issues a constant number of
queries (not proportional to person count). Suite: 103 passing.

Signed-off-by: Justin Paul <justin@jpaul.me>

justin merged commit 629bfa1367 into main

2026-06-11 08:00:47 -04:00

justin deleted branch fix-person-list-n-plus-one

2026-06-11 08:00:47 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: justin/provenance#246