The inline INSERT…SELECT backfill in migration 0045 wrapped the table
creation and a ~100 GB pass over image_record.tagger_predictions in one
transaction: nothing committed until the end, it was unmonitorable, and an
earlier MATERIALIZED-CTE form spilled the full 100 GB to temp on NFS. A
deploy got stuck on it for ~2h with image_prediction never appearing.
Split the concerns:
- 0045 now creates ONLY the table + indexes (instant DDL → web boots).
- New backend.app.tasks.admin.backfill_image_predictions_task copies the
>= store-floor predictions from the JSON into image_prediction, batched by
id window and committed per chunk: live progress, resumable (re-enqueues
from the last committed id), idempotent (ON CONFLICT DO NOTHING). json_each
stays in the DB executor streaming each window — no Python-side 100 GB load,
no materialization.
- POST /api/admin/maintenance/backfill-predictions + a Maintenance-tab card
to trigger the one-time run after upgrading.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The MATERIALIZED-CTE scalar guard forced Postgres to materialize all object
rows with their full JSON (~100 GB) to temp before json_each — on NFS that's a
huge spill and pathologically slow (risks disk-full). Replace with an inline
CASE that feeds json_each an empty object for non-object rows: same scalar
guard, but a single streaming pass with no materialization.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Some image_record rows store tagger_predictions as a JSON scalar/null rather
than an object; json_each throws 'cannot deconstruct a scalar' on those,
rolling back the whole migration. Filter to json_typeof = 'object' in a
MATERIALIZED CTE so the guard runs before json_each ever evaluates a scalar.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The #764 in-place prune (rewrite tagger_predictions to >=0.70) is too slow on
100 GB of TOAST and fails at its soft limit (interrupts a query mid-flight ->
'another command is already in progress'). #768 supersedes it: extract only
the >=floor predictions into image_prediction via this set-based backfill,
then drop the column (step 3) — reading 100 GB once + writing ~840k small rows
beats rewriting 100 GB in place.
So this backfill no longer assumes the prune ran: it filters by
ml_settings.tagger_store_floor (default 0.70) itself, handling the full or
partially-pruned JSON identically.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Normalize tagger predictions out of the image_record.tagger_predictions JSON
blob into a queryable per-prediction table. Step 1 of the cutover (expand):
additive + low-risk — reads still use the JSON, this just adds the table and
keeps it populated.
- ImagePrediction(image_record_id, raw_name, category, score) — stores the
RAW tagger vocab name (not tag_id) so read-time alias→canonical resolution
is unchanged. Indexed for per-image reads + by (raw_name, score).
- Migration 0045: create table + set-based backfill from the JSON via
json_each (fast post-#764-prune). The old column stays (vestigial) and is
dropped in a later follow-up — DROP needs an ACCESS EXCLUSIVE lock on the
hot image_record table, so it waits for a quiesced-worker window.
- tag_and_embed dual-writes the rows (delete-then-insert, idempotent);
tagger_store_floor already applied in infer().
Next: switch suggestion + allowlist reads to the table, then drop the JSON
write. Plan-task #768.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
bvandeusen