Stop large captures lists from spilling gigabytes of database temp files

[mihow]

Summary

During operational work on a production deployment, we repeatedly observed a single database query fill Postgres's temp-file space and run for roughly two hours before finishing. The query is a SELECT DISTINCT over every column of the source-image ("captures") table, joined against project ownership/membership. On a deployment with tens of millions of source images, deduplicating the entire wide row forces Postgres to sort the whole result set instead of just checking for duplicate IDs, and that sort spills to disk. Based on code reading (see below), the .distinct() call responsible appears to live in a permission-scoping helper that every list and detail endpoint in the app calls on every request, not just captures — so on a large deployment this query re-fires constantly, competing for disk and I/O with everything else the database is doing.

Observed behavior

• A query matching the shape SELECT DISTINCT main_sourceimage.* FROM main_sourceimage <joins to project ownership/membership> ORDER BY <capture ordering> was seen filling Postgres's temp_file usage and running for roughly two hours before completing, on a production deployment with a captures table in the tens of millions of rows.
• It appeared to re-fire whenever the application was in normal use, consistent with it living in a code path that runs on every (or nearly every) request rather than a one-off admin/export action.
• This was observed opportunistically during unrelated database maintenance, not via a dedicated profiling session — see "What we still need to verify" below.

Suspected root cause (based on code reading, not a captured EXPLAIN plan)

The project's permission-scoping queryset method looks like the source of this query shape:

# ami/base/models.py:42-88 (BaseQuerySet.visible_for_user)
def visible_for_user(self, user: User | AnonymousUser) -> QuerySet:
    ...
    filter_condition = Q(**{f"{project_field}draft": False})
    if not is_anonymous:
        filter_condition |= Q(**{f"{project_field}owner": user}) | Q(**{f"{project_field}members": user})
    return self.filter(filter_condition).distinct()

project__members traverses a many-to-many relationship (Project.members, ami/main/models.py:297-301), and combining it in an OR with the project__owner foreign-key condition is a plausible source of duplicate joined rows — the classic case where Django ORM code reaches for .distinct() defensively. The problem is that this .distinct() has no field list, so Django emits SELECT DISTINCT <every column> rather than deduplicating by primary key. On a table as wide as the captures table, with tens of millions of rows, that turns into a full sort of the entire result set.

This method is not specific to captures — it's called from the shared base viewset that (as far as we can tell from a repo-wide search) essentially every list/detail endpoint in the app inherits from:

# ami/main/api/views.py:134-141 (DefaultViewSet.get_queryset)
def get_queryset(self):
    qs: QuerySet = super().get_queryset()
    assert self.queryset is not None
    if isinstance(qs, BaseQuerySet):
        return qs.visible_for_user(self.request.user)  # type: ignore
    return qs

The captures viewset (SourceImageViewSet, ami/main/api/views.py:572) picks this up through super().get_queryset() at ami/main/api/views.py:625, then layers select_related(...), order_by("timestamp"), and further filters on top — none of which clear the .distinct() flag set earlier in the chain. The source-image model's default ordering (ami/main/models.py:2563-2564, Meta.ordering = ("deployment", "event", "timestamp")) means an ORDER BY is present even on call sites that don't explicitly set one, matching the ORDER BY seen in the observed query shape.

Because the captures table is by far the largest project-scoped table in the schema, and every visible-captures request runs through this shared method, it's the most likely candidate for the query we saw — but we have not yet captured a live EXPLAIN to confirm this is the exact call site versus some other place in the codebase with a similar shape.

A second, smaller instance of the same pattern exists in the ML pipeline result-saving path:

# ami/ml/models/pipeline.py:1049
source_images = SourceImage.objects.filter(pk__in=[int(img.id) for img in results.source_images]).distinct()

This runs inside the Celery task that saves each batch of ML pipeline results (save_results), which fires repeatedly while any processing job is running — consistent with the "re-fires whenever the app is online" observation, independent of the visible_for_user theory above. Here, though, filtering by pk__in on the primary key cannot itself produce duplicate rows, so .distinct() looks unnecessary rather than a defense against a join — it's possible this predates a de-duplication step done elsewhere, or defends against results.source_images itself containing duplicate IDs. Either way, combined with the model's default Meta.ordering, this call also produces a SELECT DISTINCT <full row> ... ORDER BY ... over however many rows are in the batch, and is worth cleaning up in the same pass even if it turns out to be a secondary contributor rather than the two-hour query itself.

Production impact (rough estimates, not measured precisely)

• Duration: roughly two hours per occurrence, observed more than once.
• Temp file usage: on the order of tens of gigabytes (our rough recollection is ~58 GB) spilled to disk for a single execution.
• Frequency: appeared to recur whenever the deployment was actively serving traffic, rather than being a rare one-off — consistent with a query embedded in a common request path rather than a scheduled job.
• Scale of the dataset where this was observed: a captures table in the tens of millions of rows.

These numbers come from operational observation during unrelated database work, not from a dedicated benchmark — treat them as order-of-magnitude, not precise.

Fix directions to discuss (ordered by effort/risk, lowest first)

1. Narrow the .distinct() in visible_for_user to the primary key instead of the full row. Django's .distinct(field) compiles to Postgres's DISTINCT ON, which requires that field to lead the ORDER BY — awkward here since callers reorder afterward (e.g. captures by timestamp). A safer equivalent: compute the deduplicated set of matching IDs in a subquery and filter the outer (unordered, unjoined) queryset against it, e.g. self.filter(pk__in=self.filter(filter_condition).values_list("pk", flat=True)). This moves the expensive sort onto a single narrow indexed column instead of the whole row, without touching the join logic. Because visible_for_user is the shared permission gate for effectively every project-scoped model, any change here needs a full test pass — correctness regressions in a permission filter are worse than the performance problem being fixed.

2. Replace the OR-across-relations with Exists() subqueries so no join multiplies rows in the first place, removing the need for .distinct() entirely — e.g. Q(project__draft=False) | Q(project__owner=user) | Q(project__in=Project.objects.filter(members=user)), or an Exists()-based membership check. This mirrors a pattern already used elsewhere in this exact viewset for the same reason (SourceImageViewSet.filter_by_has_detections, ami/main/api/views.py:702-715, uses models.Exists(...) specifically to avoid a duplicating join instead of filtering-then-.distinct()). Higher effort than option 1 because the anonymous-user branch and every model's project_accessor variant need to be re-verified for equivalence, but it addresses the pattern at its root rather than working around it.

3. Drop the .distinct() in the save_results call site (ami/ml/models/pipeline.py:1049) if we confirm results.source_images cannot contain duplicate IDs — filtering on pk__in alone doesn't need it. If duplicates are possible, dedup the Python list (set(...)) before building the queryset instead of asking Postgres to do it.

4. If 1 and 2 don't fully close the gap on the largest deployments, consider a covering index or a cached "visible project IDs" computation per request — a bigger lift, worth revisiting only after measuring the effect of the above.

What we still need to verify

• Capture a live EXPLAIN (ANALYZE, BUFFERS) or an application trace (e.g. from our APM) that pins the exact endpoint/call site responsible for the two-hour query, to confirm it's visible_for_user (and specifically the captures/SourceImage path) rather than a different call site with a similar shape. This is pending — we don't yet have a captured trace tied to a specific request.
• Confirm whether the project__owner / project__members OR in visible_for_user can actually produce duplicate rows for a real project/user combination (vs. being defensive), so we know whether removing or narrowing .distinct() is safe as-is or needs the Exists() rewrite.
• Confirm whether results.source_images in the ML pipeline results payload can contain duplicate source-image IDs, to decide whether ami/ml/models/pipeline.py:1049's .distinct() can simply be deleted.
• Once a fix candidate exists, get before/after EXPLAIN timing on a snapshot of a large production-sized dataset (tens of millions of source images) to confirm the fix actually removes the temp-file spill rather than just moving the cost elsewhere.

---

Drafted with Claude Code from operational observations during database maintenance; the root cause is from code reading and is pending an EXPLAIN/APM trace (see “What we still need to verify”).

[mihow]

APM trace — confirms the firing endpoint (was the top "still need to verify" item)

An APM trace of a production deployment pins the endpoint behind this query, which the original report could only hypothesize from code reading.

Primary offender: GET /api/v2/captures/ (SourceImageViewSet.list). Over a 7-day window it showed 58 slow calls with an average databaseDuration of ~6.6 million ms and a max around 15.7 million ms. The same SELECT DISTINCT-over-a-join shape also appears, less severely, on SourceImageCollectionViewSet.list (/api/v2/captures/collections/) and SummaryView.get (/api/v2/status/summary/) — consistent with all three inheriting the shared permission-scoping queryset. Still firing as recently as the day before this trace (dozens of slow calls that week), so it's an active problem, not historical.

The join causing the row fan-out is LEFT OUTER JOIN main_userprojectmembership — the project-membership M2M — which matches the Q(members=user) branch of visible_for_user() identified above. That confirms the mechanism: the membership join can multiply rows, the code reaches for .distinct(), and with no field list it becomes DISTINCT over the full wide row.

Pagination doubles the cost. The trace shows DRF firing two expensive statements per request off the same .distinct() queryset (same traceId): a COUNT(*) wrapper subquery for the page count, and the full row-fetch with the 50+ serializer columns. So each captures-list page pays the wide-row DISTINCT sort twice. This overlaps with the estimated-count paginator discussion in #1328 — a faster count would remove one of the two, but the underlying full-row DISTINCT sort would still make the row-fetch expensive.

New, hedged observation: these may be timing out, not completing

The worst individual durations cluster very tightly around 7.3–7.36 million ms (~121–123 minutes) across different days and trace IDs, rather than spreading out. That tight clustering near a round ~2-hour value looks more like the query being cut off by a ~2-hour ceiling (a statement_timeout, gunicorn worker timeout, or an upstream socket/proxy read timeout at ~7200s) than like queries genuinely finishing on their own at wildly varying times. This is unconfirmed — I have not checked the timeout configuration — but if it's right, the user impact is worse than "slow": those requests are failing with a timeout error after two hours of database work, not returning a large page. Worth checking the effective statement/worker/proxy timeout against that ~7200s figure.

Trace from production APM data during database-migration work; durations are measured, the timeout hypothesis is not yet confirmed.