Sync: every contact costs two queries per run for its primary number and link, and every old interview one more #47

New issue

Closed

opened 2026-10-31 03:04:32 +00:00 by tiagoagueda · 1 comment

tiagoagueda commented

2026-10-31 03:04:32 +00:00

Owner

What exists today

postulo-dav on the forge's main (83710e9):

src/postulo_dav/sync.py:197-221 card_for calls primary_phone_number(contact) and primary_web_link(contact, LinkKind.SOCIAL) from the plugin surface. It runs for every visible contact on every run, changed or not, because the digest it feeds (sync.py:790) is how a change is noticed.
In the core those are core/phone_numbers.py:61-63 (holder.phone_numbers.filter(is_primary=True).first()) and core/web_links.py:241-243 (holder.web_links.filter(kind=kind, is_primary=True).first()): one query each, and .filter() ignores any prefetch_related the caller did, so the plugin cannot batch them today.
The contacts queryset (sync.py:684-688) has select_related("company") and with_notes(owner), but no prefetch of numbers or links.
Run.each (sync.py:181-187) wraps every contact, unchanged ones included, in transaction.atomic(), a SAVEPOINT and RELEASE pair each.
Links whose record is not in the run: contacts, sync.py:917-920, one Contact.objects.filter(pk=…).exists() per link (rare: a contact deleted or no longer shared); interviews, sync.py:1171-1175, one Interview.objects.filter(pk=…).exists() per link whose interview started more than 90 days ago (HISTORY, sync.py:96). Those links are kept on purpose ("merely too old for the window"), so this second loop grows with the person's whole interview history and runs every interval.

The scout's line numbers (129-144, 661) were from the stale local checkout.

What would change

Core (postulo): phone_numbers.primary_for and web_links.primary_for read the holder's prefetched rows when phone_numbers or web_links is in its _prefetched_objects_cache, and query as today otherwise. Same answer, no new surface name; a test asserts zero queries for a prefetched holder.

postulo-dav:

Add prefetch_related("phone_numbers", "web_links") to the contacts queryset (two queries per run in all), once the core above is pinned.
Replace the per-link .exists() in both loops with one values_list("pk", flat=True) over the missing ids before the loop.
Optional: skip the savepoint for a contact whose digest and ETag are both unchanged (check before run.each).
A test with django_assert_max_num_queries around a second, idle run.

What it saves or protects

small: an idle run over 300 contacts and 200 past interviews makes about 800 single-row queries (2 per contact, 1 per old interview link), plus a savepoint pair per contact; with a prefetch and one batched lookup, a handful (counted from the code, not measured)

Accessibility

none

Decisions

Split into: one core issue (primary_for honours a prefetch) and one postulo-dav issue (prefetch, batched existence checks), the second repinning past the first.

Found by the efficiency and reliability audit of 2026-10-30 (finding PR7; index postulo/postulo#1108). Checked against main at daee30b15 (2026-10-30).

## What exists today postulo-dav on the forge's `main` (`83710e9`): - `src/postulo_dav/sync.py:197-221` `card_for` calls `primary_phone_number(contact)` and `primary_web_link(contact, LinkKind.SOCIAL)` from the plugin surface. It runs for every visible contact on every run, changed or not, because the digest it feeds (`sync.py:790`) is how a change is noticed. - In the core those are `core/phone_numbers.py:61-63` (`holder.phone_numbers.filter(is_primary=True).first()`) and `core/web_links.py:241-243` (`holder.web_links.filter(kind=kind, is_primary=True).first()`): one query each, and `.filter()` ignores any `prefetch_related` the caller did, so the plugin cannot batch them today. - The contacts queryset (`sync.py:684-688`) has `select_related("company")` and `with_notes(owner)`, but no prefetch of numbers or links. - `Run.each` (`sync.py:181-187`) wraps every contact, unchanged ones included, in `transaction.atomic()`, a `SAVEPOINT` and `RELEASE` pair each. - Links whose record is not in the run: contacts, `sync.py:917-920`, one `Contact.objects.filter(pk=…).exists()` per link (rare: a contact deleted or no longer shared); interviews, `sync.py:1171-1175`, one `Interview.objects.filter(pk=…).exists()` per link whose interview started more than 90 days ago (`HISTORY`, `sync.py:96`). Those links are kept on purpose ("merely too old for the window"), so this second loop grows with the person's whole interview history and runs every interval. The scout's line numbers (129-144, 661) were from the stale local checkout. ## What would change Core (postulo): `phone_numbers.primary_for` and `web_links.primary_for` read the holder's prefetched rows when `phone_numbers` or `web_links` is in its `_prefetched_objects_cache`, and query as today otherwise. Same answer, no new surface name; a test asserts zero queries for a prefetched holder. postulo-dav: - Add `prefetch_related("phone_numbers", "web_links")` to the contacts queryset (two queries per run in all), once the core above is pinned. - Replace the per-link `.exists()` in both loops with one `values_list("pk", flat=True)` over the missing ids before the loop. - Optional: skip the savepoint for a contact whose digest and ETag are both unchanged (check before `run.each`). - A test with `django_assert_max_num_queries` around a second, idle run. ## What it saves or protects small: an idle run over 300 contacts and 200 past interviews makes about 800 single-row queries (2 per contact, 1 per old interview link), plus a savepoint pair per contact; with a prefetch and one batched lookup, a handful (counted from the code, not measured) ## Accessibility none ## Decisions - **Split into:** one core issue (`primary_for` honours a prefetch) and one postulo-dav issue (prefetch, batched existence checks), the second repinning past the first. --- Found by the efficiency and reliability audit of 2026-10-30 (finding PR7; index postulo/postulo#1108). Checked against `main` at `daee30b15` (2026-10-30).

tiagoagueda added this to the 0.6.0 milestone

2026-10-31 03:04:32 +00:00

tiagoagueda added the

performance

enhancement

labels

2026-10-31 03:04:32 +00:00

tiagoagueda referenced this issue from Postulo/postulo

2026-10-31 03:04:37 +00:00

Phone numbers and web links: primary_for ignores rows already prefetched, so a sync pays two queries per contact #1171

tiagoagueda commented

2026-10-31 03:05:03 +00:00