Export: the archive runs queries per application, per contact and per sent document while holding the write lock #557

New issue

Closed

opened 2026-10-01 10:47:37 +00:00 by tiagoagueda · 1 comment

tiagoagueda commented

2026-10-01 10:47:37 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by reading core/export.py:build_document and counting its queries as the data grows.

Related to #247.

Checked again on 2026-10-01 against main at 0a5cad587: still present.

build_document prefetches most of what it walks but not all of it, so an export makes several queries per application, four per contact and one per sent document. write_archive runs it inside transaction.atomic(), which on SQLite is BEGIN IMMEDIATE, and on the default install the export errand runs inline, so the whole build holds the instance's write lock.

Where: core/export.py:638-650 (the prefetch list), core/export.py:703 (application.tags.all()), core/export.py:722 (application.offers.all()), core/export.py:479-487 _contact (contact.department, phone_numbers, postal_addresses, web_links through :397-419), core/export.py:659 (company.parent), core/export.py:624-631 (the contacts at no company, with no prefetch), core/export.py:777 (RenderedDocument without prefetch_related("copies"), read by _copies at :916-933), core/export.py:958-975 write_archive.
What happens: measured on the snapshot with the harness's rich fixture: 155 queries for 10 companies and 262 for 20, the growth being core_phonenumber, core_postaladdress, core_weblink and jobs_department once per contact, core_tag and applications_offer once per application, and documents_documentcopy once per sent document. The other reviewer measured a large account (800 companies, 1,500 applications, 266 contacts) at about 3,830 queries and 6 to 12 s, against 39 queries and 2.9 s with five prefetches added, with an identical manifest. Other requests to the instance wait for that time.
Why it matters: an export is rare, but for its duration every other page of the instance waits; on a slow host a large account can pass the 20-second busy timeout and make other people's requests fail.
Fix: add postings__applications__tags, postings__applications__offers, contacts__phone_numbers, contacts__postal_addresses, contacts__web_links and a Prefetch("contacts", queryset=Contact.objects.select_related("department")) to the companies query; select_related("parent") on it; the same channel prefetches and select_related("department") on the company-less contacts; and prefetch_related("copies") on the rendered documents.

Done when

A test in tests/test_export.py builds the document for an account and for one with ten times as many companies, applications and contacts, and asserts the query counts are equal; it fails today.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, by reading `core/export.py:build_document` and counting its queries as the data grows. Related to #247. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. `build_document` prefetches most of what it walks but not all of it, so an export makes several queries per application, four per contact and one per sent document. `write_archive` runs it inside `transaction.atomic()`, which on SQLite is `BEGIN IMMEDIATE`, and on the default install the export errand runs inline, so the whole build holds the instance's write lock. - **Where:** `core/export.py:638-650` (the prefetch list), `core/export.py:703` (`application.tags.all()`), `core/export.py:722` (`application.offers.all()`), `core/export.py:479-487` `_contact` (`contact.department`, `phone_numbers`, `postal_addresses`, `web_links` through `:397-419`), `core/export.py:659` (`company.parent`), `core/export.py:624-631` (the contacts at no company, with no prefetch), `core/export.py:777` (`RenderedDocument` without `prefetch_related("copies")`, read by `_copies` at `:916-933`), `core/export.py:958-975` `write_archive`. - **What happens:** measured on the snapshot with the harness's rich fixture: 155 queries for 10 companies and 262 for 20, the growth being `core_phonenumber`, `core_postaladdress`, `core_weblink` and `jobs_department` once per contact, `core_tag` and `applications_offer` once per application, and `documents_documentcopy` once per sent document. The other reviewer measured a large account (800 companies, 1,500 applications, 266 contacts) at about 3,830 queries and 6 to 12 s, against 39 queries and 2.9 s with five prefetches added, with an identical manifest. Other requests to the instance wait for that time. - **Why it matters:** an export is rare, but for its duration every other page of the instance waits; on a slow host a large account can pass the 20-second busy timeout and make other people's requests fail. - **Fix:** add `postings__applications__tags`, `postings__applications__offers`, `contacts__phone_numbers`, `contacts__postal_addresses`, `contacts__web_links` and a `Prefetch("contacts", queryset=Contact.objects.select_related("department"))` to the companies query; `select_related("parent")` on it; the same channel prefetches and `select_related("department")` on the company-less contacts; and `prefetch_related("copies")` on the rendered documents. ## Done when - A test in `tests/test_export.py` builds the document for an account and for one with ten times as many companies, applications and contacts, and asserts the query counts are equal; it fails today.

tiagoagueda added this to the 0.6.0 milestone

2026-10-01 10:47:37 +00:00

tiagoagueda added the

tier

enhancement

labels

2026-10-01 10:47:37 +00:00

tiagoagueda referenced this issue from a commit

2026-10-05 23:54:30 +00:00

Read an export's channels, tags, offers and copies in a fixed number of queries

tiagoagueda commented

2026-10-06 09:32:05 +00:00