SQLite PRAGMA設定 本番運用のベストプラクティス

PRAGMA はエンジンとの対話窓口

PRAGMA は SQLite 独自のステートメントで、エンジンの挙動を確認したり変更したりするためのものです。書き方は普通の SQL と同じですが、操作対象はデータではなくデータベース自体の設定になります。

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

PRAGMAはクエリとして実行すれば現在の値を取得でき、代入の形で書けば値を変更できます。

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

基本となる考え方として、ほとんどの PRAGMA は 接続ごと に効くという点を押さえておきましょう。新しいコネクションを開けば、設定はデフォルトに戻ります。だからこそ、本番のコードでは接続を確立した直後に PRAGMA をまとめて流す小さなブロックを置くのが定番なのです。

本番で効く SQLite PRAGMA 推奨設定

覚えておくべき PRAGMA をたった5つに絞るなら、以下の設定です。

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT '設定完了' AS status;

SQLite を本番のメインストアとして使うなら、ほぼどんなアプリでもこのあたりが無難なデフォルトになります。とはいえ、それぞれの PRAGMA がなぜ効くのかを把握しておく価値はあるので、ここから順に見ていきましょう。

journal_mode = WAL

journal_mode は、書き込みをどうやって永続化するかを決める設定です。デフォルトの DELETE はロールバックジャーナル方式で、書き込み中は読み込みがブロックされ、読み込み中は書き込みがブロックされます。CLI ツールなら問題ありませんが、Web アプリだと一気にボトルネックになります。

そこで登場するのが WAL(Write-Ahead Logging)です。読み込みと書き込みが互いをブロックしなくなり、書き込みがコミット中でも、読み込み側は一貫したスナップショットを見続けられます。同時に書けるのは依然として 1 つだけですが、負荷がかかっても読み込みのレスポンスが落ちません。

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

知っておきたいポイントがいくつかあります:

• journal_mode は 永続的 です。一度設定するとそのデータベースファイルに保存されるので、接続ごとに毎回設定し直す必要はありません(設定しても問題はありません)。
• WAL を有効にすると、.db ファイルと並んで -wal と -shm の 2 つのファイルが作られます。データベースを開いている最中にこれらを削除してはいけません。
• WAL は NFS や SMB のようなネットワークファイルシステムとは相性が悪いので、データベースはローカルディスクに置きましょう。

WAL モードと並行処理についてはより詳しい別ドキュメントがあります。とりあえず今は、有効にしておけば OK です。

synchronous = NORMAL に設定する

synchronous は、SQLite がディスクへどれだけ厳格にフラッシュするかを決めます。耐久性(durability)と速度のトレードオフですね。

• FULL(デフォルト) — コミットごとにフラッシュ。耐久性は最大ですが、その分遅め。
• NORMAL — 安全なチェックポイントでフラッシュ。WAL と組み合わせれば安全で、速度も出ます。
• OFF — OS 任せ。速いですが、電源断時に破損するリスクがあります。

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

結果として返ってくる整数 1 は NORMAL を表しています。WALモードと組み合わせる場合、NORMAL が推奨設定です。クラッシュしてもコミット済みのトランザクションは失われず、停電時に直近のごく一部が失われる可能性があるだけ。ほとんどのアプリにとって、これがちょうどいいバランスです。

OFF は、使い捨てのデータベースを構築していて、いつでもゼロから作り直せる場合以外は使わないでください。

foreign_keys = ON で外部キー制約を有効化

ここでハマる人が多いポイントです。SQLiteは外部キーをサポートしていますが、デフォルトでは制約チェックが_オフ_になっており、しかも接続ごとに設定する必要があります。

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('幽霊', 999);

foreign_keys = ON を設定していれば、最後の INSERT は失敗します。id が 999 の著者は存在しないからです。PRAGMA を効かせずに走らせると、SQLite は孤立した行を平気で書き込み、数ヶ月後に「なんだこのデータ…」と気づくハメになります。

新しいコネクションを張ったら、まず最初に PRAGMA foreign_keys = ON; を実行しましょう。たいていの ORM は自動でやってくれますが、生のドライバを直接叩いているなら自分で面倒を見る必要があります。

busy_timeout = 5000 で書き込み競合をしのぐ

SQLite はライターが同時に1つしか動けません。1つ目のコネクションがトランザクションの最中に2つ目が書き込みにいくと、デフォルトでは SQLITE_BUSY がすぐに返ってきて即終了します。

そこで busy_timeout の出番です。すぐ諦めるのではなく、指定時間まで待ってリトライするように SQLite に指示できます。

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

値の単位はミリ秒です。5000 なら「ロックが解放されるまで最大5秒待ってから諦める」という意味になります。WALと組み合わせれば、並行処理が走るアプリケーションでよく見る database is locked エラーのほとんどを潰せます。

もしこの値を30秒以上に引き上げたくなったら、本当に直すべきはタイムアウトの長さではなく、トランザクションを短くすることです。

cache_size でキャッシュサイズを調整する

cache_size は、SQLiteがメモリ上に保持するデータベースページ数を指定するpragmaです。キャッシュが大きいほどディスク読み取りが減り、ホットデータへのクエリが速くなります。

指定方法は2通りあります。

• 正の数 — ページ数で指定。デフォルトのページサイズ4 KBなら、2000 は8 MBに相当します。
• 負の数 — キビバイト単位で指定。-20000 はページサイズに関係なく20 MBになります。

PRAGMA cache_size = -20000;
PRAGMA cache_size;

負の値で指定するほうが直感的に扱えます。ページサイズから逆算するのではなく「キャッシュを20MB確保する」と書けるからです。小さめのアプリなら20〜50MBもあれば十分。読み込みが多くデータベースも大きい場合は、もっと増やしてもかまいません。synchronous と同様、cache_size も接続ごとの設定です。

mmap_size

メモリマップドI/Oを使うと、SQLiteはOSのページキャッシュから直接データベースファイルを読み込めるようになり、コピーを1回省略できます。大きなデータベースでは読み込み速度の向上が期待できます。

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

これは 256 MB という意味です。空きがあれば、SQLite はデータベースをこのサイズまでメモリにマップします。ページングは OS 任せなので、最初から 256 MB を確保するわけではありません。あくまで「ここまでマップしてもいいよ」という上限です。

mmap_size は読み取り中心のワークロードで真価を発揮します。小さなデータベースでも悪さはしないので、安心して指定できます。デフォルトはかなり控えめなので、引き上げておくと得をするケースがほとんどです。

PRAGMA optimize の使い方

クエリプランナはインデックスを選ぶ際に統計情報を参照します。統計が古いと、当然プランも悪くなります。PRAGMA optimize を使えば、その統計情報を低コストで更新できます。

PRAGMA optimize;

推奨されるパターンとしては、長く生きるコネクションをクローズする直前に実行するのがよいです。たとえばアプリのシャットダウン時や、コネクションをしばらく握っているリクエストハンドラの終わり際などですね。実行コストは小さく（だいたいミリ秒オーダー）、本当に更新が必要なときだけ動きます。

ANALYZE とは別物で、ANALYZE が統計情報をフルで作り直すのに対し、optimize は軽量で頻繁に呼べる兄弟分という位置づけです。

現在の PRAGMA 設定を確認する

コネクションが今どんな設定になっているかを確認したいときは、値を代入せずに PRAGMA をクエリすれば取得できます。

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

デバッグ時にも役立ちます。別のドライバから接続して挙動が変わったときの原因は、ほぼ間違いなく PRAGMA の違いです。

ちなみに PRAGMA pragma_list; を使うと、そのビルドでサポートされている PRAGMA 一覧をすべて出力できます。

PRAGMA pragma_list;

暗記するほどのものではないですが、必要になったときに手元にあると便利です。

ランタイムではなく CREATE 時に決める設定

データベースファイル自体に関わる PRAGMA がいくつかあって、これらはテーブルを作成する 前 にしか効きません。

• PRAGMA page_size = 8192; — ディスク上のページサイズ。デフォルトは 4096 で、たいていのワークロードならこれで十分です。行サイズが大きい場合は、ページを大きくすると効いてきます。
• PRAGMA encoding = 'UTF-8'; — テキストのエンコーディング。

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

既存のデータベースで page_size を変更した場合は、設定を反映させるために VACUUM を実行する必要があります。これらの設定はデータベース作成時に一度だけ行い、あとは触らないのが基本です。

実際の接続初期化スニペット

アプリケーションコードでは、こうした設定は接続を開く処理の中にまとめておくのが一般的です。イメージとしては次のような感じです。

-- 新しい接続ごとに一度実行:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- 定期的に、またはクローズ前に実行:
PRAGMA optimize;

temp_store = MEMORYは、一時テーブルや一時インデックスをRAM上に置く設定です。インデックスのないソートや集計を行うクエリで効果を発揮します。

本番向けのチェックリストはこれで全部です。たった数行のPRAGMA設定で、SQLiteは「開発中ならOK」レベルから「実運用にも耐えられる」レベルへと変わります。

次は: よくあるエラー

PRAGMAをきちんと設定していても、SQLiteではおなじみのエラーに遭遇します。database is locked、disk I/O error、constraint failedといった面々ですね。次のページでは、これらのエラーが実際に何を意味していて、どう対処すればいいのかを順番に見ていきます。