bvandeusen
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>
70 lines
2.7 KiB
Python
70 lines
2.7 KiB
Python
"""image_prediction table (DDL only — backfill runs as a background task)
|
|
|
|
Normalizes the per-image tagger predictions out of the JSON blob into a
|
|
queryable table (#768). This migration creates ONLY the table + indexes — it
|
|
is pure DDL and commits instantly, so web boots immediately.
|
|
|
|
The data backfill from the existing image_record.tagger_predictions JSON is
|
|
deliberately NOT done here. Doing it inline made the whole migration one
|
|
transaction over the ~100 GB TOAST: nothing committed until the very end, it
|
|
was invisible/unmonitorable mid-run, and an early MATERIALIZED-CTE form spilled
|
|
the full 100 GB to temp. Instead the backfill is the
|
|
backend.app.tasks.admin.backfill_image_predictions_task — batched by id window,
|
|
committed per chunk (visible progress + resumable), idempotent
|
|
(ON CONFLICT DO NOTHING). Trigger it from Settings → Maintenance once web is up.
|
|
|
|
The old image_record.tagger_predictions column is left in place (vestigial) and
|
|
dropped in a follow-up once the backfill + code cutover are verified — dropping
|
|
it needs an ACCESS EXCLUSIVE lock on the hot image_record table (the 0044 lock
|
|
class), so it's deferred to a quiesced-worker window.
|
|
|
|
Revision ID: 0045
|
|
Revises: 0044
|
|
Create Date: 2026-06-10
|
|
|
|
"""
|
|
from typing import Sequence, Union
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
|
|
revision: str = "0045"
|
|
down_revision: Union[str, None] = "0044"
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.create_table(
|
|
"image_prediction",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column(
|
|
"image_record_id", sa.Integer(),
|
|
sa.ForeignKey("image_record.id", ondelete="CASCADE"),
|
|
nullable=False,
|
|
),
|
|
sa.Column("raw_name", sa.String(length=255), nullable=False),
|
|
sa.Column("category", sa.String(length=64), nullable=False),
|
|
sa.Column("score", sa.Float(), nullable=False),
|
|
sa.UniqueConstraint(
|
|
"image_record_id", "raw_name", name="image_raw_name",
|
|
),
|
|
)
|
|
op.create_index(
|
|
"ix_image_prediction_image", "image_prediction", ["image_record_id"],
|
|
)
|
|
op.create_index(
|
|
"ix_image_prediction_name_score", "image_prediction",
|
|
["raw_name", "score"],
|
|
)
|
|
# No data backfill here — see the module docstring. The one-time copy from
|
|
# image_record.tagger_predictions runs as backfill_image_predictions_task
|
|
# (batched, resumable, idempotent), kept out of this transaction so web boots
|
|
# without waiting on a ~100 GB pass.
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_index("ix_image_prediction_name_score", "image_prediction")
|
|
op.drop_index("ix_image_prediction_image", "image_prediction")
|
|
op.drop_table("image_prediction")
|