■ 1. SQLiteの再評価とDB Proでの活用
- DB ProではSQLiteを愛用している
- SQLiteには制限があるが弱点ではなく適切にデプロイされ慎重にチューニングされればプロダクションで使用できる
- SQLiteは過去数年で復活を遂げている
- libSQLやTursoへのフォークやPocketBaseのような人気バックエンドフレームワークでの採用などが見られる
- DB Pro自体のローカルデータベースもSQLiteで動作している
- DB Proのユースケースにはこれ以上の代替手段は存在しない
- 過去3ヶ月間SQLiteを本格的に使用してきた結果多くのことを学んだ
- SQLiteのより興味深い機能とニュアンスを扱う短いブログ投稿シリーズを計画している
■ 2. SQLiteのJSON機能の発見
- SQLiteにはJSON関数と演算子があることを最近まで知らなかった
- Hacker Newsのコメントでbambaxという人物がSQLiteのJSON演算子について述べた内容を発見した
- bambaxのコメントの要点:
- 各JSONドキュメントをそのまま1つのカラムに保存する
- json_extractの組み合わせを使用してクエリしたい特定の情報を保存する仮想カラムを作成する
- これらのカラムにインデックスを作成する
- これにより超高速検索が可能になる
- 挿入時にインデックス対象を選択する必要がなく必要なときにいつでも仮想カラムを追加できる
- インデックスされていない生のJSONも検索できるが大規模コレクションでは時間がかかる可能性がある
■ 3. bambaxの提案する4つのステップ
- bambaxの提案を実際に試してみることにした
- DB Proのブログには埋め込みSQLite-in-the-browserコンポーネントがあるため動作例を作成した
- bambaxが述べている内容の分解:
- JSONドキュメントを生のまま保存する
- json_extractを使用して仮想生成カラムを作成する
- これらの生成カラムにインデックスを追加する
- 完全なB-treeインデックス速度でJSONをクエリする
- これによりインデックス戦略を事前に選択する必要がなくなる
- 後で新しいJSONフィールドでクエリする必要があることに気づいた場合は生成カラムを追加してインデックスを追加するだけで完了する
- データ移行もスキーマの書き換えもETLも不要で純粋な柔軟性が得られる
■ 4. 実装手順1:生JSONの保存
- まずJSONカラムを持つシンプルなテーブルを作成する
- JSONドキュメントは到着したそのままに自然に保存される
- スキーマの複雑な操作も不要である
■ 5. 実装手順2:仮想生成カラムの追加
- bambaxが魔法が起こると言っている箇所である
- 仮想生成カラムを追加する
- 生成カラムはオンデマンドで値を計算する
- これらは実際にはデータを保存しない
- 書き込みは発生しないと考えられる
- バックフィルも不要で即座に実行される
- 仮想カラムはクエリするたびにJSONデータからオンザフライで計算される
■ 6. 実装手順3:パフォーマンスのためのインデックス追加
- これがケーキのアイシングである
- インデックスを追加してこれらの仮想カラムを超高速にする
- 突然JSONが完全なインデックスサポートを持つ通常のリレーショナルカラムのように動作する
■ 7. 実装手順4:フルスピードでのクエリ
- クエリが非常に高速になる
- いくつかの例でそれを試すことができる
■ 8. 後からの新しいクエリパターンへの対応
- このパターンの最も強力な点の1つであると考えられる
- 後日JSONの形状が変わった場合でも別のカラムを追加して別のインデックスを作成するだけでよい
- 例えばuser_idでクエリする必要があることに気づいた場合:
- ALTER TABLEで新しい仮想カラムを追加する
- CREATE INDEXでインデックスを作成する
- 既存の行に触れることなく最適化される
■ 9. このパターンが強力な理由
- このパターンはSQLiteでJSONを扱う方法についての考え方を完全に変えた
- スキーマレスデータの柔軟性とリレーショナルデータベースのパフォーマンスとエルゴノミクスを組み合わせることができる
- 早期にコミットしすぎたり自分自身を追い込んだりすることなくこれを実現できる
- これらの小さなSQLiteスーパーパワーは他にもたくさん隠れている
- これは共有したい最初のものにすぎない