kobotoolbox / kpi / 30450583610

push

github

perf(longRunningMigrations): avoid full index scan in LRM 0027 xform paging DEV-2427 (#7333)

### 📣 Summary

Speeds up an internal data migration that backfills missing submission
`root_uuid`s, so it no longer times out on projects with a very large
number of submissions.

### 👷 Description for instance maintainers

Long-running migration 0027 (`root_uuid` backfill) pages through XForms
by repeatedly finding the next batch of distinct `xform_id`s that still
have `Instance` rows with a null `root_uuid`. That pagination query used
a plain `SELECT DISTINCT xform_id ... ORDER BY xform_id LIMIT`, relying
on PostgreSQL to deduplicate as it scans.

PostgreSQL has no native loose ("skip") index scan: it has to walk every
matching index entry in order, including duplicates, before it can move
on to the next distinct value. When a single XForm has millions of
`Instance` rows with a null `root_uuid`, the scan has to read all of
them just to advance past that one XForm, even though the query only
asks for 100 distinct ids.

In production this was confirmed with `EXPLAIN`, the planner estimated
million of rows to walk to produce the next 100 distinct ids. In
practice, the query got killed by PostgreSQL's `statement_timeout`
(4200s in Celery workers) with `canceling statement due to statement
timeout`, stalling the migration on that page of XForms.

Rewrote the pagination query as a recursive CTE that seeks directly to
the next distinct `xform_id` at each step (`xform_id > current ORDER BY
xform_id LIMIT 1`), instead of scanning through every duplicate row.
Verified against production data: the same lookup that used to time out
at 4200s now returns in under 1s.

### 💭 Notes

- No behavior change from the migration's point of view: same XForms are
found, same order, same batch size (`CHUNK_SIZE`). Only the SQL used to
find them changed.
- Timeout is barely impossible to reproduce locally without a huge
amount of submissions.

9488 of 12791 branches covered (74.18%)

30721 of 37496 relevant lines covered (81.93%)

5.51 hits per line