Schema v4: raw_json／definitions／FTS の重複排除と移行設計

[Iktahana]

背景

schema v3 の公開 schema、user_version=3、rowid、データ内容、Go API 契約を一切変えずに、現行 data/ から legacy/compact DB を独立再構築して計測した。

計測入力:

• source SHA-256: 2573f336446d5bc1d3fcf90a80e43528b32ad2944eb142c3673b3cf58fd2a687
• 296,924 JSON files / 1,963,906,107 source bytes
• 347,318 entries / 381,819 definitions / 46,305 variants
• 全候補で version/commit/branch/repository/build_date を固定

schema v3 の実測:

候補	raw bytes	legacy 比	結果
legacy JSON / 4096 / optimize なし	3,126,140,928	baseline	control
compact JSON / 4096 / optimize なし	3,005,964,288	-120,176,640	採用要素
compact JSON / 4096 / FTS optimize	3,005,485,056	-120,655,872 (-3.86%)	schema v3 最終候補
compact JSON / 8192	3,032,203,264	compact/4096 より +26,238,976	不採用
compact JSON / 16384	3,445,456,896	compact/4096 より +439,492,608	不採用
VACUUM/ANALYZE 順序変更	3,005,964,288	追加削減なし	不採用
MEMORY journal + EXCLUSIVE lock	3,005,964,288	追加削減なし、396.00s	不採用

圧縮後も増加なし:

format	legacy	compact + FTS optimize	差分
gzip -9 -n	771,477,376	766,713,798	-4,763,578 (-0.62%)
zstd -19 -T0	492,687,490	489,300,102	-3,387,388 (-0.69%)

完全 warm-up 後、主要 8 query を DB ごと各 1,000 回交互実行した結果、p50/p95 の合計は 12.11% 改善し、10% + 0.1ms の悪化閾値に該当する query はなかった。FTS entry の p50/p95 は約 45% 改善、FTS definition は約 18%/12% 改善した。

compact 化で減ったのは entries と definitions の JSON serialization 空白、および FTS optimize の segment 約 479KB のみ。raw_json 内の definitions と正規化済み definitions、さらに FTS content/index に同じ情報が重複する構造自体は schema v3 契約のため残る。厳格な v3 互換下で今回確認できた削減上限は 120,655,872 bytes であり、それ以上の重複排除は schema/API/rowid/FTS 契約を変えるため本変更には含めない。

schema v4 で比較する設計候補

1. raw_json の除去・再構築・dedupe
   • 正規化列から API JSON を再構築する、共通 payload を別 table に分離する、または canonical payload を一度だけ保持する。
   • key/array order、SQL NULL と空文字、未既知フィールド、API JSON の数値型を失わない設計が必要。
2. definitions / examples / citations の正規化
   • definitions の入れ子 JSON と raw_json の二重保持を解消する。
   • example/citation の順序、nullable field、複数義・heteronym を保持する。
3. external-content / contentless FTS5
   • FTS shadow content の複製を削減する。
   • MATCH、prefix MATCH、snippet()、highlight()、rank と結果順を現行と比較する。
4. JSONB または圧縮 payload
   • SQLite JSONB、application-level compressed BLOB、dictionary compression を候補にする。
   • 最低対応 SQLite version、Electron/Go driver の可搬性、標準 SQLite file としての直接利用性を明示する。

互換性マトリクス

consumer	schema v3 の依存	v4 で確認する事項
Electron	bundled DB、named table/column/index、FTS5	bundled SQLite version、JSONB/FTS 対応、v3/v4 DB 選択
Go API	entries.raw_json → api.Entry、lookup、FTS snippet/highlight	再構築 JSON の深度等価性、latency、driver/version
direct SQL users	公開 schema、declared type、rowid/id、collation、named index	compatibility view または明示的 breaking change
SQLite versions	schema v3 は通常の TEXT + FTS5	JSONB/拡張機能の最低 version と fallback
配布形式	標準 SQLite file + gzip/zstd	解凍後も標準 SQLite file か、専用 reader が必要か

versioning / migration / rollback

• PRAGMA user_version=4 と _metadata.schema_version=4 を同時に設定する。
• schema manifest を version 管理し、v3→v4 migration tool は入力 DB を変更せず新規 v4 DB を生成する。
• migration は integrity/FK/row-count/API/FTS 検証後に atomic rename する。失敗時は元の v3 を残す。
• 少なくとも 1 release window は v3/v4 の dual-version asset を公開し、Electron と API を段階移行する。
• rollback は consumer を v3 asset に戻せること、v4 から v3 を再生成できることの両方を検証する。

受け入れ条件

• user_version=4、metadata、schema manifest が一致する
• 全 entry の API JSON が type-aware に深度等価（NULL/空文字、配列順、数値/真偽値を含む）
• UUID、entry、reading、prefix、variant、Unicode、異体字、heteronym、複数義の結果と順序が期待通り
• FTS content/rowid と MATCH / prefix MATCH / snippet() / highlight() / rank を v3 と比較
• Electron、Go API、direct SQL の互換性マトリクスを実機検証
• integrity_check / foreign_key_check / query plan / dbstat を保存
• raw、gzip、zstd が v3 より縮小し、query p50/p95 と build time が合意した閾値内
• migration、dual publish、rollback の end-to-end test が通る

重要: 上記の重複排除・正規化・JSONB/BLOB・external/contentless FTS 案を schema v3 に backfill しない。必ず schema v4 として設計・配布・移行する。

[Iktahana]

schema v3 最終測量記錄（2026-09-11〜12 JST）

同一 source hash／固定 metadata での初回候補 build:

candidate	raw bytes	wall time	decision
legacy / 4096 / no FTS optimize	3,126,140,928	391.47s	control
compact / 4096 / no FTS optimize	3,005,964,288	392.21s	compact 採用
compact / 4096 / FTS optimize	3,005,485,056	393.44s	最終採用
compact / 8192	3,032,203,264	335.04s	raw size 増加で棄却
compact / 16384	3,445,456,896	336.11s	raw size 増加で棄却
compact / 4096 / VACUUM→ANALYZE	3,005,964,288	376.51s	size 改善なし、build 5% 改善未達で棄却
compact / 4096 / ANALYZE→VACUUM→ANALYZE	3,005,964,288	429.53s	size/build とも改善なしで棄却
compact / 4096 / MEMORY+EXCLUSIVE	3,005,964,288	396.00s	WAL control より遅く棄却

最終候補は raw で 120,655,872 bytes (3.86%) 削減。独立した再 build でも legacy/final がそれぞれ同じ byte size を再現した。

圧縮結果:

• gzip: 771,477,376 → 766,713,798 bytes (-4,763,578 / -0.62%); 93.85s → 91.00s
• zstd: 492,687,490 → 489,300,102 bytes (-3,387,388 / -0.69%); 115.58s → 113.61s
• gzip/zstd とも test + 解凍 SHA-256 照合済み

互換性・回帰:

• full streaming compare: 347,318 entries / 381,819 definitions / 46,305 variants / 両 FTS table、schema v3、rowid、typed scalar、type-aware JSON、MATCH/prefix/snippet/highlight/order、query plan が一致
• fully warmed query benchmark: 8 query × 各 DB 1,000 回、p50/p95 合計 -12.11%; 閾値違反なし
• Python 71 tests pass
• go test -count=1 -tags sqlite_fts5 ./... pass
• quality audit: 300,012 files / 350,406 entries、Errors 0
• actionlint pass