fix(api): bound worker_jobs history with 30d purge + index-covered ProcessingSummary #1251
Loading…
Reference in a new issue
No description provided.
Delete branch "fix/worker-jobs-retention-summary-cost"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Problem
Production (2026-10-15):
worker_jobsgrew unbounded to 992,096 rows / 6.7 GB of lifetime churn (plant_detection 255k+115k, discovery_people 79k+5k, thumbnail jobs in the hundreds of thousands, owner_face_recognition 45k+11k). The per-owner ProcessingSummary lane aggregates (COALESCE(SUM(CASE WHEN status …)), joined viami.id = wj.media_item_id) scan that history on every web poll / SSE refetch → sustained ~109 % CPU on the postgres container with DataFileRead IO waits.Fix (2 parts)
1. Retention —
WorkerJobHistoryPurgeService: batched purge of terminal jobs older than 30 days (@Scheduled, startup+60 s, then every 10 min; 2,000 rows/batch, each batch its own REQUIRES_NEW transaction, capped at 50 batches/tick → initial 960k backlog drains over ~10 ticks without long transactions).Safety rules (load-bearing, each pinned by tests):
failed/expiredare NEVER purged — WorkerJobRecoveryService retries them (attempts < 3) and the enqueue anti-joins inWorkerJobEnqueueServiceuse old failed rows as permanent-failure guards; purging them would convert guards into infinite enqueue loops.worker_job_results,worker_artifacts,worker_media_access_grants(NOT NULL FKs) +processing_issuespointer rows (no FK → would orphan).input_versiontombstones); the bounded table IS the retention policy.2. Aggregate cost — V166 indexes (CREATE INDEX only, no rewrite, no migration-time DELETE):
idx_worker_jobs_media_type_status_updated (media_item_id, job_type, status, updated_at)→ makes the lane aggregates index-only via the media join. Explicitly NOT owner-first:worker_jobs.owner_idis NULL for all media-scoped enqueues (V121 backfilled non-terminal only) — an owner-first index would serve empty ranges = silently wrong counts.PURGEABLE_STATUSES, pinned by a test readingpg_get_indexdef).Rejected: Caffeine cache (new dependency, staleness vs SSE refetch), rollup table (transition-sync failure modes), partitioning (overkill while purge bounds the table — revisit only if steady-state still hurts).
Verification
WorkerJobHistoryPurgeServicePostgresTest(Testcontainers PG, full Flyway chain V001→V166 on the real deploy/postgres image): 9 tests — strict<retention boundary, queued/leased/running/failed/expired untouched, bothcancelled/canceledspellings, child cleanup + surviving-job children kept, idempotency, batch limit, index/service status-list sync.rtk ./gradlew :apps:api:test: 2094 tests, 0 failures.Out of scope
Partitioning, archiving purged rows, the 12
semantic_embedding ml_image_unreadablefailures (corrupt input images, terminal after 3 attempts).Production evidence 2026-10-15: worker_jobs at 992,096 rows / 6.7 GB of lifetime churn; postgres at ~109% CPU with the per-owner ProcessingSummary lane aggregate (COALESCE(SUM(CASE ...)) per lane, joined via media_items) hammered by web polling + SSE refetches. Two-part fix, no UI-visible behavior change: 1. V166 indexes (CREATE INDEX only, no table rewrite): - idx_worker_jobs_media_type_status_updated (media_item_id, job_type, status, updated_at): media-first key matches the mi.id = wj.media_item_id join so each lane aggregate reads contiguous per-media index ranges. Deliberately NOT owner-scoped: V121 backfilled owner_id only for non-terminal rows and media-scoped enqueues leave it NULL — an owner-first index would serve empty ranges (silently wrong counts). - idx_worker_jobs_terminal_updated_at: partial index over exactly the purgeable statuses (kept in sync with the service list, pinned by test). - child-table indexes for the FK-enforced batch deletes (worker_job_results / worker_artifacts / worker_media_access_grants) plus a partial index for processing_issues worker_job pointer rows. 2. WorkerJobHistoryPurgeService (@Scheduled 10 min, startup +60s): deletes terminal worker_jobs (completed, completed_with_issues, cancelled/canceled) older than 30 days in REQUIRES_NEW batches of 2,000 (children first: worker_job_results, worker_artifacts, worker_media_access_grants, processing_issues pointer rows; worker_jobs has no ON DELETE CASCADE children). NEVER touches queued/leased/running (live queue) or failed/expired (recovery retry state + deliberate permanent-failure guards in enqueue anti-joins — purging those would create infinite re-enqueue loops). No tombstone table: reset flows re-enqueue with fresh input_versions, which falsifies any (media, type, input_version) tombstone — the bounded table IS the retention policy. 30d comfortably covers the 7d recent-batch window (ProcessingSummary), the 7d JobRunsService outcome window, and the WorkerResultService resubmit dedupe. Nullable provenance references (discovery_results.worker_job_id, class_instance_occurrence_candidates.worker_job_id) are write-side-only and left dangling by design — no read path joins them to worker_jobs. Operator status: worker_job_history_purge idleScheduler row in GET /api/admin/job-runs. Config: memories.worker.history-purge.retention-days (default 30) and .batch-size (default 2000). Tests: WorkerJobHistoryPurgeServicePostgresTest runs the full Flyway chain on Testcontainers PG — pins retention boundary (strict <), terminal-status safety (queued/leased/running/failed/expired untouched), both cancelled spellings, child-row cleanup, surviving-job children kept, idempotency, batch limit, and the partial-index/service status-list sync. EXPLAIN (ANALYZE, BUFFERS) on a 500k-row synthetic worker_jobs: lane aggregate 56 ms unbounded -> 23 ms purged (2.4x), Index Only Scan path confirmed; aggregate cost is now proportional to the bounded window, not lifetime history. KNOWN_ISSUES: nothing left open — bounded retention + covering index close the growth and CPU regressions; partitioning deliberately not pursued (family-install complexity outweighs benefit while the table is bounded).