fix(migration): make 0045 DDL-only; backfill image_prediction via batched task (#768) #95

2026-06-11T09:22:14-04:00

bvandeusen commented

2026-06-11 09:22:14 -04:00

Why

The previous deploy wedged: web booted an image whose migration 0045 still ran an inline INSERT…SELECT backfill over the ~100 GB image_record.tagger_predictions TOAST. Because table creation + backfill were one transaction, nothing committed for ~1h44m, it was unmonitorable, and the earlier MATERIALIZED-CTE form spilled the full 100 GB to temp on NFS. (Compounded by Swarm's :latest re-pull trap — service update --image …:latest didn't fetch the streaming build.)

Change

Split the table creation from the data copy:

Migration 0045 is now DDL-only — creates image_prediction + indexes and commits instantly, so web boots in seconds regardless of library size.
New backend.app.tasks.admin.backfill_image_predictions_task copies the >= store-floor predictions from the JSON into image_prediction, walking image_record in 2000-id windows with a bounded INSERT…SELECT over json_each (CASE-guarded against scalar/null rows), 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. Self-resumes on the 600 s chunk boundary / soft limit; maintenance_long lane.
POST /api/admin/maintenance/backfill-predictions + a Settings → Maintenance "Backfill predictions now" card to trigger the one-time run after upgrading.
Registration test for the new task.

Deploy notes

Force a genuine image re-pull (digest pin, or docker pull …:latest + docker service update --force) — the :latest tag-string update alone won't fetch the new image.
Web boots → instant DDL migration → verify \d image_prediction.
Trigger the backfill card; watch SELECT count(*) FROM image_prediction climb.

Step 3 of #768 (stop the JSON dual-write, drop image_record.tagger_predictions, VACUUM FULL to reclaim ~100 GB, exclude image_prediction from backups) stays HELD until the backfill + read cutover are verified in prod.

Dev CI green (run 877: lint, backend pytest, integration, frontend-build all ✓).

🤖 Generated with Claude Code

## Why The previous deploy wedged: web booted an image whose migration 0045 still ran an inline `INSERT…SELECT` backfill over the ~100 GB `image_record.tagger_predictions` TOAST. Because table creation + backfill were one transaction, nothing committed for ~1h44m, it was unmonitorable, and the earlier MATERIALIZED-CTE form spilled the full 100 GB to temp on NFS. (Compounded by Swarm's `:latest` re-pull trap — `service update --image …:latest` didn't fetch the streaming build.) ## Change Split the table creation from the data copy: - **Migration 0045 is now DDL-only** — creates `image_prediction` + indexes and commits instantly, so web boots in seconds regardless of library size. - **New `backend.app.tasks.admin.backfill_image_predictions_task`** copies the `>=` store-floor predictions from the JSON into `image_prediction`, walking `image_record` in 2000-id windows with a bounded `INSERT…SELECT` over `json_each` (CASE-guarded against scalar/null rows), **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. Self-resumes on the 600 s chunk boundary / soft limit; `maintenance_long` lane. - **`POST /api/admin/maintenance/backfill-predictions`** + a Settings → Maintenance **"Backfill predictions now"** card to trigger the one-time run after upgrading. - Registration test for the new task. ## Deploy notes 1. Force a genuine image re-pull (digest pin, or `docker pull …:latest` + `docker service update --force`) — the `:latest` tag-string update alone won't fetch the new image. 2. Web boots → instant DDL migration → verify `\d image_prediction`. 3. Trigger the backfill card; watch `SELECT count(*) FROM image_prediction` climb. Step 3 of #768 (stop the JSON dual-write, drop `image_record.tagger_predictions`, `VACUUM FULL` to reclaim ~100 GB, exclude `image_prediction` from backups) stays HELD until the backfill + read cutover are verified in prod. Dev CI green (run 877: lint, backend pytest, integration, frontend-build all ✓). 🤖 Generated with [Claude Code](https://claude.com/claude-code)

bvandeusen added 1 commit 2026-06-11 09:22:14 -04:00

fix(migration): make 0045 DDL-only; backfill image_prediction via batched task (#768 )

CI / lint (push) Successful in 3s

Details

CI / backend-lint-and-test (push) Successful in 35s

Details

CI / frontend-build (push) Successful in 42s

Details

CI / integration (push) Successful in 3m16s

Details

65211a3f2f

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>

bvandeusen merged commit 028ea33a7c into main

2026-06-11 09:22:23 -04:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: bvandeusen/FabledCurator#95