本番環境での SQLite の運用: パフォーマンスチューニングとバックアップ戦略

中小規模のウェブサイトで本番環境に SQLite を使用することは、実行可能なアーキテクチャの選択肢ですが、パフォーマンスの低下や可用性の問題を避けるためには、データベースの内部構造を理解しておく必要があります。主な運用要件には、ANALYZE による定期的な統計情報の更新、大量削除時の書き込みロックの管理、および非ブロッキングなバックアップ戦略の実装が含まれます。

ANALYZE によるクエリパフォーマンスの最適化

SQLite のクエリプランナーが効率的な実行方法を選択するためには、ANALYZE コマンドの実行が極めて重要です。最新の統計情報がないと、クエリプランナーが最適ではないプランを選択してしまい、深刻なパフォーマンスの低下を招く可能性があります。

ある記録された事例では、わずか 4,000 行のテーブルに対する全文検索クエリ（FTS5 を使用）の実行に 5 秒かかっていました。ANALYZE を実行した後、実行時間は約 0.05 秒に短縮されました。

ANALYZE の仕組み

ANALYZE は、インデックスやテーブル内の値の分布に関する統計情報を生成します。これらの統計情報は、内部テーブル（sqlite_stat1 や sqlite_stat4 など）に保存されます。

インデックスの値の分布に関するさまざまな統計的ビューを提供することで、プランナーがそのインデックスがどれほど有用（選択的）であるかを推定できるようにします。sqlite_stat1 は平均値のみを提供しますが、sqlite_stat4 を有効にするとヒストグラムデータが保存されます。

書き込み競合と大量削除の管理

SQLite のシングルライターモデルは、長時間実行される書き込み操作が他のワーカーをブロックする場合、アプリケーションのクラッシュやタイムアウトを引き起こす可能性があります。これは、大量の DELETE 文を含むデータベースのクリーンアップ作業中に特に顕著になります。

タイムアウトの問題

一括削除操作が設定されたデータベースのタイムアウト時間を超えると（例：5 秒）、データベースに書き込みを試みる他のワーカーがタイムアウトし、アプリケーションや VM がクラッシュする原因となることがあります。

緩和策

クリーンアップ中の可用性を維持するために、以下の戦略が推奨されます。

• バッチ処理: 単一の大きなトランザクションではなく、小さなバッチに分けて削除を行うことで、単一のクエリがタイムアウトの閾値を超えないようにします。
• RowID のプリロード: 削除対象の行をまず SELECT 文で特定します。WAL (Write-Ahead Logging) モードでは、読み取りは書き込みをブロックしません。
• 逐次削除: ストレージ媒体によっては、行が追加された順序（またはその逆）で削除することで、パフォーマンスが向上する場合があります。
• CLI による直接実行: アプリケーションレベルの ORM やトランザクション内で実行される Python コードによる潜在的なオーバーヘッドを回避するため、クリーンアップ操作には SQLite Command Line Interface (CLI) を使用します。

バックアップとリカバリのワークフロー

信頼性の高い SQLite のバックアップには、データベースを長時間ロックせず、リモートのオブジェクトストレージに効率的に保存できる手法が必要です。

方法 1: VACUUM INTO と Restic

一つのアプローチは、VACUUM INTO コマンドを使用してデータベースの一貫性のあるスナップショットを一時ファイルに作成し、それを圧縮して Restic のようなツールを使用して S3 互換ストレージにアップロードする方法です。

方法 2: Litestream

Litestream は、WAL (Write-Ahead Log) を S3 にレプリケーションすることで、増分バックアップを提供します。これは一般的にフルスナップショットよりもリソース効率が高く、大規模なデータベースファイルを圧縮する際に発生することがある Out-of-Memory (OOM) 問題を回避できます。

方法 3: 圧縮されたダンプ

.dump と zstd のような圧縮ツールを組み合わせることで、非常に圧縮率が高く、rsync に適したバックアップを作成できます。WAL モードが有効な場合、これらは書き込みをブロックしません。

アーキテクチャ上の考慮事項

SQLite は多くのユースケースで非常に効率的ですが、いくつかのアーキテクチャパターンを用いることで、さらに使用を最適化できます。

• データベースの分割: 独立したデータドメインを持つプロジェクトでは、テーブルを複数の別々のデータベースファイルに分割することで、競合を減らし、管理を簡素化できます。
• WAL モード: Write-Ahead Logging (WAL) を有効にすることは、ほとんどの本番環境のウェブアプリケーションにおいて必須の前提条件です。これにより、複数の読み取りと一つの書き込みを同時に実行できるようになります。
• クライアント・サーバー型 DB への移行: 要件が進化し、ネットワーク経由のアクセスや高コンカレンシーな同時書き込みが必要になった場合は、PostgreSQL のようなシステムへの移行が標準的なパスとなります。