実行できるSQLiteドキュメント: ANALYZE と VACUUM

Q: SQLiteの`ANALYZE`と`VACUUM`は何が違うの？

ANALYZEはテーブルやインデックスの中身に関する統計情報を集めてsqlite_stat1テーブルに保存します。クエリプランナーはこの情報を見て、より良い実行プランを選べるようになります。一方のVACUUMはデータベースファイル自体を作り直して、未使用ページを回収しストレージを詰め直す処理です。役割はまったく別物で、ANALYZEはクエリを賢くするため、VACUUMはファイルを小さくするため、と覚えるとわかりやすいです。

Q: SQLiteの`VACUUM`はどのくらいの頻度で実行すべき？

実は、ほとんどのデータベースでは実行する必要すらありません。大量のDELETEやDROP TABLEの後にファイルサイズを気にする場面、あるいは長期間運用していて書き込みが多く、行の入れ替わりが激しいデータベースで、たまに走らせる程度で十分です。VACUUMはファイル全体を書き直し、排他ロックも取るので、軽い気持ちでスケジュールするものではありません。自動的に少しずつ片付けたい場合は、データベース作成時にPRAGMA auto_vacuum = INCREMENTALを設定しておきましょう。

Q: `PRAGMA optimize`は何をしてくれるの？

PRAGMA optimizeは最近推奨されているやり方で、コネクションを閉じる前に呼び出すと、データベースの変更状況を見てSQLite側がANALYZEなどのメンテナンスを実行する価値があるかどうかを自動で判断してくれます。毎回機械的にANALYZEを回すよりずっと低コストなので、たいていのアプリは終了処理でこれを呼んでおけばOKです。

用途がまったく違う2つのメンテナンス処理

ANALYZE と VACUUM はよくセットで語られますが、解決する問題はまったく別物です。

ANALYZE はデータの統計情報を集めて、クエリプランナーが賢い判断をできるようにします。書き込み先は sqlite_stat1 テーブルで、実データの行には一切触りません。
VACUUM はファイル自体を再構築して、未使用ページを解放しストレージを最適化します。クエリプランそのものを変えるわけではありません。

クエリが間違ったインデックスを選んでしまうなら ANALYZE、大量に削除した後でファイルサイズが膨らんでいるなら VACUUM の出番です。この2つを混同すると、メンテナンスの時間をムダにしがちです。

ANALYZE は実際に何をしているのか

クエリプランナーは常に「推測」で動いています。たとえば WHERE status = 'active' を見たとき、マッチする行が1件なのか100万件なのかを見積もって、初めてインデックスを使うかフルスキャンするかを判断できるわけです。統計情報がなければ、ざっくりした既定値で当てずっぽうに決めるしかありません。

ANALYZE は各インデックスを走査して、値の分布に関するサマリーを記録します。

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

sqlite_stat1 の各行には、そのインデックスがおおよそ何行あって、典型的なキーにどれくらい重複があるかという統計情報が入っています。次に WHERE status = 'pending' で検索すると、プランナは「pending はレア」と判断してインデックスを使ってくれますし、WHERE status = 'shipped' ならスキャンの方が安いと判断するかもしれません。

データベース全体ではなく、特定のテーブルやインデックスだけを ANALYZE することもできます:

ANALYZE orders;
ANALYZE idx_orders_status;

ANALYZE を実行するタイミングは、大量データの一括投入後、スキーマを大きく変更した後、あるいはデータ分布が変わったテーブルでプランナが妙な実行計画を選び始めたときです。

PRAGMA optimize:いまどきの定番

接続を閉じるたびに無条件で ANALYZE を走らせるのは無駄です。たいていの場合、再集計が必要なほどの変化は起きていません。そこで SQLite には、もっと賢いラッパーが用意されています。

PRAGMA optimize;

PRAGMA optimize は、前回の解析以降にデータベースがどう変化したかをチェックして、必要なテーブルにだけ ANALYZE を走らせてくれます。公式が推奨しているのは、長時間生きているコネクションをクローズする直前に呼ぶこと、そして数時間以上開きっぱなしのコネクションでは定期的に呼ぶことです。

変更がなければほぼノーコストで済み、変更があればきちんと効いてくれます。まずは optimize を使い、強制的に統計情報を取り直したいときだけ生の ANALYZE に頼る、というのが基本方針です。

VACUUM の役割を正しく理解する

行を削除したりテーブルを DROP したりすると、SQLite はそのページを「空き」とマークするだけで、ファイル自体は縮みません。空きページは以降の INSERT で再利用されるので、普段はこれで問題なし。ただし、長期間にわたって更新を繰り返していると、次の2つが地味に蓄積していきます。

OS から見えない空き容量。実データは 800 MB しかないのに、.db ファイルは 2 GB のまま、というやつです。
断片化。同じテーブルの行が飛び飛びのページに散らばってしまい、スキャン性能が落ちます。

VACUUM はこの両方を一気に解消します。データベース全体を新しいファイルにぎゅっと詰め直してコピーし、元のファイルと差し替える、という仕組みです。

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'エントリ ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

VACUUM を実行すると、ファイルサイズは「最初から残っている100行だけを入れ直した」ときと同じ大きさまで縮みます。副作用として、rowid はすべて維持されたまま、ディスク上の配置だけが再び連続した状態に整えられます。

実行前に押さえておきたいポイントがいくつかあります。

実行中はデータベースに排他ロックがかかります。他のコネクションから書き込むことはできません。
データベースサイズのおよそ2倍の空きディスク容量が必要です。古いファイルの隣に新しいファイルを作る仕組みだからです。
トランザクション内では実行できません。アクティブなトランザクションが残っているとエラーになります。
数GB級のデータベースでは、それなりに時間がかかります。余裕を持って計画しましょう。

sqlite vacuum を実行すべきタイミング

ほとんどのアプリでは、特別な理由がない限り走らせる必要はありません。

VACUUM を実行する妥当なケース:

大きなテーブルを DROP した直後や、大量の行をまとめて削除した後にディスク容量を回収したいとき。
何年も書き込みと削除を繰り返してきたデータベースで、テーブルスキャン系のクエリが昔より遅く感じるとき。
リリース成果物としてデータベースファイルを配布するので、できる限り小さくしておきたいとき。

逆に、やらないほうがいいケース:

「念のため」。VACUUM は毎回ファイル全体を書き直します。本番稼働中のシステムで安易にやるのはむしろ危険です。
削除のたびに毎回。空いたページはどのみち再利用されるので、実行する意味がありません。

auto_vacuum と incremental VACUUM

空きページの管理を SQLite に任せたい場合は、データベース作成時に auto_vacuum を設定します。後から変更するにはフル VACUUM が必要になるので注意してください。

PRAGMA auto_vacuum = INCREMENTAL;

3つのモードがあります。

NONE（デフォルト）: 空きページはファイル内に残り、以降のINSERTで再利用されます。
FULL: ページを解放するたびにコミット時にファイルが自動で切り詰められます。手間はかかりませんが、トランザクションごとにコストが発生します。
INCREMENTAL: SQLiteが空きページを追跡しておき、明示的に要求したタイミングでのみ解放します。

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

PRAGMA incremental_vacuum(N) を使うと、最大 N ページ分の空きページを OS に返却できます。処理は速く、排他ロックの保持時間も短いので、定期実行に向いています。フル VACUUM のコストは避けたいけれど、書き込みが多くてサイズを抑えたいデータベースには、ちょうどいい落としどころです。

VACUUM INTO:コンパクトなコピーを書き出す

VACUUM INTO は、元のファイルには一切手を加えず、最適化済みのコピーを別ファイルとして書き出します。

VACUUM INTO 'backup.db';

これは本当に役に立ちます。

バックアップ用途。出力されるのは整合性が取れた、完全に VACUUM 済みのスナップショットです。書きかけのページも .wal の心配もありません。cp でファイルをコピーするより安全です。
書き込みを長時間ロックせずにファイルサイズを縮小。別ファイルへ VACUUM してからアトミックに差し替えるので、VACUUM の実行中ずっとライターがブロックされるわけではありません。
配布。開発用データベースの小さくデフラグ済みのコピーを配って回せます。

なお、出力先のファイルがすでに存在しているとエラーになります。事前に削除しておきましょう。

実践的なメンテナンス手順

一般的なアプリケーションのデータベースであれば、次のような流れがおすすめです。

-- 長時間維持される各接続で、クローズ前に実行:
PRAGMA optimize;

-- 大規模なバルクロードやスキーマ変更の後:
ANALYZE;

-- 大量のデータを削除してディスク容量を取り戻したい場合:
VACUUM;

-- バックアップ用:
VACUUM INTO '/backups/app-2026-04-23.db';

書き込みや削除が頻繁で、しかも 24 時間動かしっぱなしのデータベースなら、作成時に auto_vacuum = INCREMENTAL を指定しておき、PRAGMA incremental_vacuum(N) をトラフィックの少ない時間帯（たとえば 1 日 1 回の深夜帯）に定期実行するのがおすすめです。

「ファイルサイズがやけに大きい…」を切り分ける

原因を探るときに役立つ pragma が 2 つあります。

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

page_count × page_size が現在のファイルサイズ。
freelist_count × page_size が未使用ページとして無駄になっているバイト数です。

page_count に対して freelist_count の割合が大きいなら、VACUUM(あるいは incremental_vacuum)を実行すればファイルが目に見えて小さくなります。逆に小さければ、すでに無駄なくパックされているので VACUUM してもほぼ効果はありません。

ハマりやすいポイント

トランザクションの中で VACUUM を実行する。 これは不可能です。先にコミットしてください。
VACUUM に必要な空き容量を忘れる。 10 GB のデータベースをバキュームするには、もう 10 GB ほどの空きディスクが必要です。
データを入れたあとに auto_vacuum を設定する。 次に完全な VACUUM を実行するまで何も起きません。使いたいなら、データベースを作成した時点で設定しましょう。
ANALYZE を実行してファイルが小さくなることを期待する。 それは VACUUM の仕事です。
VACUUM を実行してクエリプランの改善を期待する。 それは ANALYZE の仕事です。

この 2 つのコマンドは互いを補い合う関係で、片方がもう片方の代わりになることはありません。

次回:トランザクション

VACUUM のようなメンテナンスコマンドを見ると、これまで当たり前のように使ってきたものが浮かび上がってきます。SQLite のトランザクションモデルと、何を・いつロックするのかという話です。次の章はそこから始めます。トランザクションがどう動くのか、BEGIN / COMMIT / ROLLBACK が実際に保証してくれるものは何か、そして複数文の処理をアトミックに保つためにどう使えばいいのかを見ていきましょう。

よくある質問

SQLiteのANALYZEとVACUUMは何が違うの？

ANALYZEはテーブルやインデックスの中身に関する統計情報を集めてsqlite_stat1テーブルに保存します。クエリプランナーはこの情報を見て、より良い実行プランを選べるようになります。一方のVACUUMはデータベースファイル自体を作り直して、未使用ページを回収しストレージを詰め直す処理です。役割はまったく別物で、ANALYZEはクエリを賢くするため、VACUUMはファイルを小さくするため、と覚えるとわかりやすいです。

SQLiteのVACUUMはどのくらいの頻度で実行すべき？

実は、ほとんどのデータベースでは実行する必要すらありません。大量のDELETEやDROP TABLEの後にファイルサイズを気にする場面、あるいは長期間運用していて書き込みが多く、行の入れ替わりが激しいデータベースで、たまに走らせる程度で十分です。VACUUMはファイル全体を書き直し、排他ロックも取るので、軽い気持ちでスケジュールするものではありません。自動的に少しずつ片付けたい場合は、データベース作成時にPRAGMA auto_vacuum = INCREMENTALを設定しておきましょう。

PRAGMA optimizeは何をしてくれるの？

PRAGMA optimizeは最近推奨されているやり方で、コネクションを閉じる前に呼び出すと、データベースの変更状況を見てSQLite側がANALYZEなどのメンテナンスを実行する価値があるかどうかを自動で判断してくれます。毎回機械的にANALYZEを回すよりずっと低コストなので、たいていのアプリは終了処理でこれを呼んでおけばOKです。

SQLite ANALYZEとVACUUM｜統計情報と空き領域の回収