Scheduler: the gone-quiet check runs two queries per account every five minutes and reloads applications it already announced #1120

New issue

Closed

opened 2026-10-31 03:04:09 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

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

Owner

What exists today

src/postulo/applications/quiet.py:63-116 (announce_quiet_applications, called from send_due_reminders.py:203 on every pass):

one query for the distinct owners of any application (lines 73-78), not limited to owners who have an open, sent one;
per owner, owner_of(owner_id) (notifications/base.py:205-209, one query) just to read the threshold, then one query for the quiet rows (lines 82-87). quiet() goes through with_activity (applications/models.py:256-291), which annotates every application with a latest-event subquery, two EXISTS subqueries and the next-interview annotation;
the "already announced for this silence" test (quiet_announced_at against last_activity_at, lines 88-92) is applied in Python after the rows are loaded, so an application that went quiet weeks ago is loaded with select_related of owner, profile, posting and company on every pass.

So on a pass where nothing is new, the cost is 1 + 2N queries and every quiet application's row, not "about 3N"; the claimed updates happen only when something is fresh. The scout's count is corrected; the substance holds.

What would change

Push the freshness test into SQL: .filter(Q(quiet_announced_at__isnull=True) | Q(quiet_announced_at__lt=F("last_activity_at"))) after quiet() (filtering on the annotation is valid on SQLite and PostgreSQL). Rows already announced are then never loaded.
Read the owners once with their profiles: User.objects.filter(is_active=True, applications_application_set__status__in=QUIET_STATUSES).select_related("profile").distinct() and pass the user to threshold_for, dropping the per-owner owner_of query. The pass is then N+1 queries and returns nothing for owners with nothing fresh.
Optional: run this announcement once an hour rather than every five minutes, as thresholds are in days. Only if the user is happy with a quiet notice arriving up to an hour later than today.

Existing tests of the notifier (tests/ for quiet announcements, including the double-scheduler claim of #221) must stay green; the claim-by-compare UPDATE (lines 99-105) is unchanged.

What it saves or protects

small: about 2N+1 queries per pass (N accounts with applications), each quiet query with four correlated subqueries per application, 288 times a day, for a state that changes on the scale of days

Accessibility

none

Decisions

Hourly instead of every pass? Recommended: not needed once 1 and 2 land; keep the pass as it is.

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

## What exists today `src/postulo/applications/quiet.py:63-116` (`announce_quiet_applications`, called from `send_due_reminders.py:203` on every pass): - one query for the distinct owners of any application (lines 73-78), not limited to owners who have an open, sent one; - per owner, `owner_of(owner_id)` (`notifications/base.py:205-209`, one query) just to read the threshold, then one query for the quiet rows (lines 82-87). `quiet()` goes through `with_activity` (`applications/models.py:256-291`), which annotates every application with a latest-event subquery, two `EXISTS` subqueries and the next-interview annotation; - the "already announced for this silence" test (`quiet_announced_at` against `last_activity_at`, lines 88-92) is applied in Python after the rows are loaded, so an application that went quiet weeks ago is loaded with `select_related` of owner, profile, posting and company on every pass. So on a pass where nothing is new, the cost is 1 + 2N queries and every quiet application's row, not "about 3N"; the claimed updates happen only when something is fresh. The scout's count is corrected; the substance holds. ## What would change 1. Push the freshness test into SQL: `.filter(Q(quiet_announced_at__isnull=True) | Q(quiet_announced_at__lt=F("last_activity_at")))` after `quiet()` (filtering on the annotation is valid on SQLite and PostgreSQL). Rows already announced are then never loaded. 2. Read the owners once with their profiles: `User.objects.filter(is_active=True, applications_application_set__status__in=QUIET_STATUSES).select_related("profile").distinct()` and pass the user to `threshold_for`, dropping the per-owner `owner_of` query. The pass is then N+1 queries and returns nothing for owners with nothing fresh. 3. Optional: run this announcement once an hour rather than every five minutes, as thresholds are in days. Only if the user is happy with a quiet notice arriving up to an hour later than today. Existing tests of the notifier (`tests/` for quiet announcements, including the double-scheduler claim of #221) must stay green; the claim-by-compare `UPDATE` (lines 99-105) is unchanged. ## What it saves or protects small: about 2N+1 queries per pass (N accounts with applications), each quiet query with four correlated subqueries per application, 288 times a day, for a state that changes on the scale of days ## Accessibility none ## Decisions - **Hourly instead of every pass?** Recommended: not needed once 1 and 2 land; keep the pass as it is. --- Found by the efficiency and reliability audit of 2026-10-30 (finding B4; 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:09 +00:00

tiagoagueda added the

performance

enhancement

labels

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

tiagoagueda referenced this issue

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

Efficiency and reliability audit of 2026-10-30: index #1108

tiagoagueda referenced this issue from a pull request that will close it,

2026-11-11 10:38:54 +00:00