【SQLite】SQLiteのバックアップとVACUUMコマンド

SQL
2023.06.17

こんにちは、かつコーチです。

WALモードまで解説してきて、SQLiteの内部の仕組みはかなり理解が深まったのではないでしょうか。

今回は、実際に運用していく上で欠かせないバックアップとVACUUMコマンドを扱います。

「ファイルをコピーするだけで良いのでは？」と思うかもしれませんが、安全なバックアップにはいくつかの落とし穴があります。

SQLiteの安全なバックアップ方法

なぜ単純なcpコマンドは危険なのか

第2回の記事でも触れましたが、稼働中のSQLiteファイルをcpコマンドでそのままコピーすると、書き込みの途中の状態を掴んでしまう可能性があります。

特にWALモードで運用している場合、本体ファイルと-walファイルの内容が一体となって初めて最新のデータになるため、片方だけをコピーすると不整合が起きます。

# ❌ 稼働中のWALモードDBをこの方法でコピーするのは危険
cp app.sqlite3 backup.sqlite3
# -walファイルの内容が反映される前の古いデータになる可能性がある

.backupコマンドによる安全なバックアップ

SQLite公式が提供するオンラインバックアップAPIを使うと、稼働中のDBに対しても整合性を保ったままバックアップを取得できます。

sqlite3コマンドラインからは.backupコマンドで利用できます。

sqlite3 app.sqlite3 ".backup backup_$(date +%Y%m%d).sqlite3"

このコマンドは内部的に、バックアップ対象のページを少しずつコピーしながら、その間に発生した変更も追跡してくれるため、DBを止めずに安全なバックアップが取れます。

Pythonなど各言語のドライバでも、同じAPIをラップした関数が用意されています。

import sqlite3

source = sqlite3.connect("app.sqlite3")
dest = sqlite3.connect("backup.sqlite3")

with dest:
    source.backup(dest)

source.close()
dest.close()

VACUUM INTOで特定バージョンの内容を出力する

もう1つの方法が、前回までにも触れたVACUUM INTOです。

VACUUM INTO 'backup_20260902.sqlite3';

.backupとの違いは、VACUUM INTOが後述する最適化（断片化の解消）を同時に行いながら、新しいファイルとして書き出す点です。

定期バックアップと同時にファイルサイズの最適化もしたい場合は、VACUUM INTOが適しています。

VACUUMコマンドの役割

削除しても縮まらないファイルサイズ

SQLiteでは、DELETE文で行を削除しても、確保していたディスク領域はすぐには解放されません。

削除された領域は「空きページ」として内部的に管理され、次回以降のINSERT時に再利用されるだけで、ファイルサイズそのものは縮小しないのです。

-- 100万件のログを一括削除
DELETE FROM logs WHERE created_at < '2026-01-01';

-- ファイルサイズを確認してもすぐには小さくならない

私が運用していたログテーブルで、古いデータを大量に削除した後に「ディスク容量が全然減らない」と焦った経験があります。

原因を調べて初めて、SQLiteはDELETEだけでは物理的なファイルサイズが縮小しない仕組みだと知りました。

VACUUMによる断片化の解消とファイルサイズ縮小

この空きページを実際に回収し、ファイルサイズを縮小するコマンドがVACUUMです。

VACUUM;

VACUUMはデータベース全体を新しいファイルとして再構築する処理で、空きページの回収に加えて、データの物理的な並び順を整理し、断片化を解消する効果もあります。

-- VACUUM実行前後でファイルサイズを比較する例
-- 実行前: 850MB → 実行後: 320MB（削除済みデータの分だけ縮小）

❌ Before：大量削除をしたまま放置する

DELETE FROM logs WHERE created_at < '2026-01-01';
-- VACUUMを実行せず放置 → ファイルサイズは850MBのまま

✅ After：定期的なVACUUMをメンテナンス手順に組み込む

DELETE FROM logs WHERE created_at < '2026-01-01';
VACUUM;
-- ファイルサイズが実データ量に応じて縮小される

大量データを定期的に削除する運用（古いログの削除など）を行っている場合は、削除処理のあとにVACUUMを実行する手順をメンテナンスに組み込んでおくことをおすすめします。

VACUUMのコストと実行タイミングの注意点

VACUUMはデータベース全体を再構築するため、対象ファイルのサイズに比例して時間がかかり、一時的に元のファイルとほぼ同サイズの作業領域をディスク上に必要とします。

また実行中はデータベース全体に対して排他ロックがかかるため、アプリケーションからの読み書きができなくなります。

-- VACUUM実行前に、十分なディスク空き容量があるか確認しておく

本番運用中のDBに対して日中いきなりVACUUMを実行するのは避け、メンテナンスウィンドウを設けて夜間バッチなどで実行するのが安全です。

つまずきやすいポイント：auto_vacuumの設定漏れ

毎回手動VACUUMを忘れてファイルが肥大化し続ける

手動でのVACUUM実行を忘れがちな場合、auto_vacuumプラグマを使って自動化する選択肢もあります。

-- 新規データベース作成時にのみ設定可能（既存DBには影響しない）
PRAGMA auto_vacuum = INCREMENTAL;

auto_vacuum = FULLにすると、削除のたびに自動でファイルサイズが縮小されますが、削除のたびに追加の処理コストがかかるため、書き込み頻度が高いテーブルでは性能に影響します。

INCREMENTALモードであれば、空きページの回収タイミングをPRAGMA incremental_vacuumで自分でコントロールできるため、書き込み性能への影響を抑えつつ定期的に縮小できます。

-- 指定したページ数分だけ空き領域を回収する
PRAGMA incremental_vacuum(1000);

注意点として、auto_vacuumはデータベース新規作成時にしか設定できません。

既存のDBに後から適用したい場合は、一度VACUUMを実行してから設定を変更し、再度VACUUMを実行する必要があります。

まとめ

この記事のポイント

  • 稼働中のSQLiteファイルはcpせず、.backupコマンドかVACUUM INTOで安全にバックアップする
  • DELETEだけではファイルサイズは縮小しない。空きページの回収にはVACUUMが必要
  • VACUUMは排他ロックを伴う重い処理。メンテナンスウィンドウで実行する
  • auto_vacuumを設定すれば自動化できるが、既存DBには新規作成時以外は適用できない

次に読むべき記事

SQLiteの運用に必要な知識が一通り揃ったところで、次はいよいよシリーズ最終回として、SQLiteがどんなアプリに向いているのか、MySQL・PostgreSQLとの比較検証を行います。

→ 次の記事：SQLiteはどんなアプリに向いているか？MySQL・PostgreSQLとの比較検証

タグ: SQLite, 中級者向け, 運用

タイトルとURLをコピーしました