DB関連の刷新

[hmjn023]

概要

PostgreSQL 18 / pgvectorへの移行、CCIP vectorのPostgres統合、画像領域の永続化、background job基盤の刷新を段階的に行う。

評価状態（2026-07-18）: 条件付き承認

• 方向性は妥当。
• #619および#613の移行リハーサルは、各Issueの追加受け入れ条件を満たす前提で着手可能。
• [CCIP] media_regions / ccip_embeddings とPostgres vector storeを実装する #615〜#621は、以下の横断条件と各sub-issueの追加受け入れ条件を確定してから実装する。

決定済みの方針

• PG17 data directoryをPG18で直接起動せず、新規volumeへdump/restoreする。
• PGliteはpgvector extensionを登録する共通factoryから起動する。
• CCIPは media_regions -> ccip_embeddings として正規化する。
• 初回のCCIP移行・検索対象はfull imageのみとし、crop CCIPは実験・評価後まで延期する。
• crop画像バイナリを正本にせず、region座標・検出metadataを正本とする。
• CCIP検索は当初pgvector exact cosineで候補取得し、既存Rust metricで再rankingする。
• 現行jobsを先に止血してからdomain processing stateと用途別run/itemへ移行する。
• CCIP用LanceDBとsource dump/export用LanceDBは別機能として扱う。
• source dump/export用LanceDBはPostgreSQLの差分dump cacheへ置き換え、常時Lance同期を廃止する。

横断的な実装ゲート

1. PostgreSQL cutover

• 現在の約632MBのPG17.7 dumpはtimed rehearsal用とする。
• 本番cutoverではアプリをwrite-freezeした後にfresh final dumpを取得する。
• plain SQL restoreは psql -X -v ON_ERROR_STOP=1、将来backupは pg_dump -Fc / pg_restore --exit-on-error を標準とする。
• restore後にmigration、件数、制約、SELECT version()、pg_extension.extversion、ANALYZEを検証する。
• PG18へwriteを開始できる時点と、PG17へ戻せなくなるrollback境界をrunbookへ明記する。

2. Region / embedding契約

• kind = 'full' はmediaごとに1件だけになるpartial unique indexをDBで保証する。
• full regionはbboxがNULL、crop regionはbbox必須かつ正規化範囲内、元画像寸法は正数、scoreは許容範囲内、というCHECK制約を置く。
• vector storeの契約をregion/model/version-awareにし、異なるembedding空間を検索で混在させない。
• stale判定をdurableな input_revision / digestへ統一する。
• 次元違いに加え、NaN、Infinity、zero norm vectorを拒否する。

3. CCIP cutover / rollback

• LanceDBからのbackfillには安定cursor、checkpoint、resume、冪等upsertを持たせる。
• dry-runはLanceDBのmanifest/index/versionを一切変更しないread-only動作とする。
• parity基準をcosine epsilon、top-K overlap、順位・tie規則まで数値化する。
• Postgres-only writeへ切り替えた後もrollback可能とする場合は、rollback期間中のdual-write、write-freeze、Postgresからのreplayのいずれかを採用する。
• rollback観測期間の終了前にCCIP用LanceDBを削除しない。

4. Job correctness

• type別dedupe keyとmerge/supersede規則を定義し、active jobの重複をDB制約で防ぐ。
• claim/heartbeat/complete/fail/requeueをclaim tokenによるCASで保護する。
• side effect書込とprocessing-state完了を claim_token + requested_revision でfenceする。
• unknown job typeまたはinvalid payloadを成功扱いせず、non-retryable failureとして記録する。
• retryable/permanent error、max attempts、backoff+jitter、lost-lease cancellationを定義する。
• 実PostgreSQL上で2 workerの競合、lease喪失、retry、batch reconciliationを検証する。

5. Generic jobs廃止条件

• processMedia が担うmetadata抽出・thumbnail生成にも明示的な移行先を用意する。
• 全job type / producer / readerから新しいstateまたはrun/itemへの移行表を作る。
• dual-write期間もclaim authorityは常に一方だけとする。
• active/pending jobをdrainまたはreconcileし、job type文字列・producer・readerがゼロであることを検査してから jobs を削除する。
• exact progress、履歴、取消、再開を提供する処理ではitem tableを必須とする。

6. Dump cache / export correctness

• dump用の完成済みNDJSONをmaterialized viewとして全件再生成せず、(source_id, media_id) 単位の差分cacheをPostgreSQLに保持する。
• cache payloadは jsonb とし、dump schema versionと生成元revisionを保持して再構築可否・stale判定を明示する。
• media本体と関連metadataの更新transaction内でdirty markerを立て、source単位のdebounce/coalescing後にbatch UPSERTする。
• NDJSON出力は COPY (SELECT payload::text ... ORDER BY ...) TO STDOUT を基本とし、アプリ側で全件materializeしない。
• dump開始時のfreshness契約（dirty反映待ち、snapshot境界、失敗時の扱い）を定義し、画像tarとNDJSONの整合性を保証する。
• 現行Lance dumpとの件数・内容・順序・再開性・メモリ/CPU/所要時間を比較し、rollback手順を用意してからLance依存を削除する。

実測baseline（2026-07-18）

• PG17.7 dump: 約632MB
• media: 88,496件（image 88,184件、video 312件）
• jobs: 20,774件
• PGlite data directory: 約2.4GB
• CCIP LanceDB: raw 62行 / unique media 46件 / 同一内容の重複16組
• full image 88,184件すべてへ768次元vectorを保存した場合、vector payloadのみで約259MiB。全件抽出またはcrop展開前に容量・p95検索時間を計測し、ANN導入判断を別ゲートで行う。

Sub-issues / 実施順

Phase 0: 現行jobの止血

• [Job] 現行jobsの永続性・重複投入・batch停止を止血する #619

Phase 1: PostgreSQL 18 / pgvector基盤

• [DB] Docker PostgreSQLを18 + pgvectorへ安全に移行する #613
• [DB] PGliteでpgvectorを有効化し起動経路を共通化する #614

#613のscript/CI整備と#614は並行可能。現在のdumpでtimed rehearsal後、write-freezeとfresh dumpを伴う本番cutoverを行う。

Phase 2: CCIP Postgres実装

• [CCIP] media_regions / ccip_embeddings とPostgres vector storeを実装する #615

LanceDBをdefault backendのままPostgres adapterを導入する。

Phase 3: 既存CCIP移行

• [CCIP] LanceDBからPostgresへvectorを移行して安全に切り替える #616

backfill -> dual-writeまたはfreeze -> final delta -> parity -> read切替 -> rollback観測の順で実施する。

Phase 4: Region lifecycle / crop実験

• [Crop] 画像領域を永続化し派生cropを管理する #617
• [Crop][CCIP] 画像領域単位の特徴量抽出・類似検索を実装する #618

#617でmedia-backed regionの永続化を確立する。#618は #620 完了後に着手する実験タスクとし、初回移行の必須条件には含めない。

Phase 5: Domain processing state / generic jobs廃止

• [Job] media・crop処理状態をdomain side-tableへ移行する #620
• [Job] import・download・batch・source syncを専用tableへ分離してjobsを廃止する #621

#620へmetadata/thumbnailを含める。#621はimport、download、batch、source sync、最終的なjobs削除へ分割して実施する。

Phase 6: source dump/export用LanceDB廃止

• source dump/export用LanceDBをPostgreSQL dump cacheへ移行する #638

Phase 2/3のCCIP用LanceDB cutoverとは独立工程とする。新しい汎用jobs依存は増やさず、Phase 5のdomain-specific run/item方針へ合わせる。

完了条件

• [DB] Docker PostgreSQLを18 + pgvectorへ安全に移行する #613〜#621の受け入れ条件がすべて満たされている。
• PG18/PGliteのmigration・restore・vector queryが再現可能な手順と自動テストで検証されている。
• CCIP検索parityとrollback手順が実データで検証されている。
• source dump/exportがLanceDBなしで生成でき、NDJSON/tarのparity、snapshot整合性、bounded memory、通常時負荷とdump速度の基準を満たす。
• domain処理および専用run/itemへの移行後、generic jobsの参照・書込がゼロになっている。
• 再起動、複数worker、retry、cancel、直接URL再読込後も処理状態をDBから復元できる。