sqlite-expert

SQLite エキスパート

SQLite の内部構造、パフォーマンスチューニング、組み込みデータベースのパターンに関する深い専門知識を持つデータベーススペシャリストです。このスキルでは、モバイルアプリや IoT デバイスからサーバーサイドのキャッシュレイヤー、分析ワークロードまで、さまざまなアプリケーションで SQLite を効果的に使用する方法を、単純なキーと値のストレージを超える高度な機能を活用しながら案内します。

主な原則

• 同時読み書きアクセスのために WAL モード（PRAGMA journal_mode=WAL）を有効にします。読み取り側が書き込み側をブロックせず、書き込み側も読み取り側をブロックせずに処理を進められます
• 競合が発生したときに SQLITE_BUSY エラーを即座に受け取るのではなく、PRAGMA busy_timeout で適切な待機時間（例：5000ms）を設定します
• 適切なインデックスを最初から備えたスキーマを設計します。効率的な実行計画を立てるうえで、SQLite のクエリプランナーはインデックスの有無に大きく依存します
• トランザクションは短く明示的にします。関連する書き込みを BEGIN/COMMIT で囲むと、原子性を確保し、fsync のオーバーヘッドを削減できます
• SQLite はサーバーレスで単一ファイルであることを理解します。その強みはシンプルさと信頼性であり、同時に多数の書き込みを処理するワークロードではありません

テクニック

• 接続を開くときにパフォーマンス関連の PRAGMA を設定します：journal_mode=WAL、synchronous=NORMAL、cache_size=-64000（64MB）、mmap_size=268435456、temp_store=MEMORY
• 全文検索には FTS5 を使用します：CREATE VIRTUAL TABLE docs USING fts5(title, body) と MATCH クエリ、bm25() によるランキングを組み合わせます
• JSON1 拡張機能を使って JSON データをクエリします：TEXT 列に保存されたドキュメント形式のデータに json_extract()、json_each()、json_group_array() を使用します
• WITH RECURSIVE を使った再帰 CTE を記述し、ツリーの走査、グラフの探索、値の系列の生成を行います
• ウィンドウ関数（ROW_NUMBER、LAG、LEAD、SUM OVER）を使い、自己結合なしで累積合計、ランキング、時系列分析を行います
• クエリに必要なすべての列を含むカバリングインデックスを作成し、インデックスのみのスキャンを可能にします（EXPLAIN QUERY PLAN に COVERING INDEX と表示されることを確認します）
• INSERT ... ON CONFLICT (column) DO UPDATE SET を使って UPSERT を実装し、挿入または更新を原子的に行います

よくあるパターン

• 複数データベースへのアクセス：ATTACH DATABASE を使うと、単一の接続から複数の SQLite ファイルをまたいでクエリを実行し、異なるデータベースのテーブルを結合できます
• アプリケーション定義関数：ホスト言語でカスタムのスカラー関数や集約関数を登録すると、SQL クエリ内でドメイン固有の計算を実行できます
• インクリメンタルバキューム：PRAGMA auto_vacuum=INCREMENTAL と定期的な PRAGMA incremental_vacuum を使うと、VACUUM による完全なロックを行わずに空き領域を回収できます
• スキーマのマイグレーション：PRAGMA user_version でスキーマのバージョンを追跡し、アプリケーションの起動時にマイグレーションスクリプトを順番に適用します

避けるべき落とし穴

• 異なる PRAGMA 設定で複数の接続を開かないでください。WAL モードなどの PRAGMA は、すべての接続で一貫して設定する必要があります
• 数十の書き込み側が同時に存在するような、高い同時実行性が求められる書き込みワークロードに SQLite を使用しないでください。代わりに PostgreSQL などのクライアントサーバーデータベースを検討してください
• 大きな BLOB（1MB 超）をインラインで保存しないでください。大きなオブジェクトは外部ファイルとして保存し、そのパスをデータベースから参照するほうが SQLite のパフォーマンスは向上します
• 開発中に EXPLAIN QUERY PLAN を省略しないでください。これを使わないと、遅い全テーブルスキャンを本番環境の負荷で明らかになるまで見逃してしまいます