本番環境でのSQLite：WALモード、並行性、およびVFSレイヤーの最適化

本番環境でのSQLite：組み込み型という神話を超えて

SQLiteは、アプリケーションと同じサーバーにデプロイされる場合、ネットワーク遅延を排除し、メモリマップドファイル操作を通じてミリ秒未満のクエリ実行を可能にするため、本番環境のWebアプリケーションにおいて実行可能です。

並行性のためのWrite-Ahead Logging (WAL) モードの最適化

WALモードを有効にすると、トランザクションをメインのデータベースに直接書き込むのではなく、別の .sqlite-wal ファイルに保存することで、読み取りと書き込みの並行実行が可能になります。

PRAGMA journal_mode = WAL;

WALモードでは、リーダーがメインのデータベースファイルにアクセスしている間に、ライターがWALファイルに追記を行うため、相互のブロッキングを防ぐことができます。ただし、WALファイルは時間の経過とともに肥大化するため、変更内容をメインのデータベースにマージするためのチェックポイント処理が必要です。

SQLiteには4つのチェックポイントモードがあります：

  • PASSIVE: リーダーやライターをブロックせずに、可能な限り多くのページをマージします。
  • FULL: 新しい書き込みをブロックし、すべてのWALをマージする前に既存の読み取りが完了するのを待ちます。
  • RESTART: FULLと同様ですが、WALファイルをサイズゼロにリセットします。
  • TRUNCATE: RESTARTと同様ですが、ディスク上のWALファイルを0バイトに切り詰めます。

継続的な読み取りアクティビティ下での無制限なWALの肥大化を防ぐには、スケジュールされた間隔で PASSIVE または RESTART チェックポイントを使用して、バックグラウンドスレッドで明示的にチェックポイントを管理してください：

PRAGMA wal_checkpoint(PASSIVE);

WALモードを PRAGMA synchronous = NORMAL と組み合わせることで、ディスク同期のオーバーヘッドを削減できます。これにより、すべてのトランザクションのコミットごとではなく、チェックポイントのような重要な瞬間にのみ同期が行われます。

"NORMALモードでは、データベースエンジンはすべてのトランザクションコミット時ではなく、重要な瞬間（例：チェックポイント時）にのみディスクに同期します。WALモードでは、これはデータベースの破損の観点から完全に安全です。サーバーがクラッシュしても、WAL内の未コミットのトランザクションが失われるだけで、データベースの整合性は維持されます。" - Original post

"いいえ、これは安全ではありません。このpragmaを使用すると、最新のコミット済みトランザクションを失う可能性があります。" - @tnodir

並行性と SQLITE_BUSY エラーの処理

WALモードは読み取りと書き込みの並行実行を許可しますが、SQLiteは単一のライターモデルを強制します。並行した書き込みの試行は SQLITE_BUSY エラーを引き起こします。

これを円滑に処理するには：

  1. 例外を発生させる前に内部でロック取得をリトライするように、busy timeoutを設定します：

    PRAGMA busy_timeout = 5000; -- 5秒

    このウィンドウの間、SQLiteは指数バックオフを使用してスリープとリトライを行い、ピーク負荷時のアプリケーションレベルのエラーを軽減します。

  2. 書き込み操作を含むトランザクションについては、BEGIN IMMEDIATE TRANSACTION; で開始してすぐに予約ロックを取得し、デッドロックを防ぎます：

    BEGIN IMMEDIATE;
-- ここに書き込み操作
COMMIT;

    "個人的には、sqlite_busyに対処するより良い方法は、アプリケーションレベルで単一のライターを管理することだと思います。これにより、単一のプロセスという文脈においてsqlite_busyを事実上排除できます。" - @andersmurphy

    "busy_timeout + BEGIN IMMEDIATE の推奨事項とは少し意見が分かれる点があります。組み込みシステムでは、それだけでは不十分な場合があります... 私たちが最終的に必要としたのは、一度に1つのライター・トランザクションのみが実行されるようにするアプリケーションレベルのロックを追加することでした。" - @firesteelrain

パフォーマンスのためのメモリとキャッシュの最適化

SQLiteのデフォルトのキャッシュサイズ（通常2MB）は、本番環境のワークロードには不十分です。ワーキングセットをメモリ内に保持するために、サイズを増やしてください。

PRAGMA cache_size = -64000; -- キャッシュ用に約64MBのRAMを割り当て

メモリマップドI/Oを有効にして、データベースファイルをアプリケーションの仮想アドレス空間に直接マッピングします。これにより、OSがページキャッシュを管理できるようになり、データベースがmmapサイズより小さい場合、ディスク読み取りをポインタ演算に変換できます。

PRAGMA mmap_size = 2147483648; -- データベースファイルの最大2GBをメモリにマッピング

"データベースのサイズがmmap_sizeよりも小さい場合、データベース全体がメモリにマッピングされ、ディスク読み取りが単純なポインタ演算に変わります。" - Original post

"はは、そういう仕組みだったのか？」 - @kev009

耐久性と分散のためのカスタムVFSレイヤーの活用

SQLiteの仮想ファイルシステム (VFS) 抽象化は、ファイル操作をVFSモジュールに委譲し、クラウド環境向けのカスタムレイヤーを可能にします。

これは、最新のレプリケーションエンジンを促進します：

  • Litestream: 増分WALフレームをオブジェクトストレージ（例：AWS S3）にストリーミングし、ポイントインタイムリカバリを可能にする別プロセス。
  • LiteFS: アプリケーションノード間でSQLiteデータベースを分散させ、トランザクションをリードレプリカにリアルタイムで複製するFUSEベースのVFS。

エフェメラル（一時的）なローカルストレージを持つクラウド環境（AWS ECS、Kubernetes、Fly.io）では、耐久性と高可用性を確保するために、VFSベースのレプリケーションツールの実行が必要になります。

"ローカルディスクの永続性がエフェメラルなクラウド環境（AWS ECS、Kubernetes、Fly.ioなど）でSQLiteを実行している場合、耐久性と高可用性を確保するためにVFSベースのレプリケーションツールを実行することが不可欠です。" - Original post

"SQLiteは大好きで、本当に本番環境で動かしたいのですが、サーバーの1つがダウンしたときにクライアントは最小限のデータ損失とダウンタイムを期待します。LiteFSやLiteStreamの上で動かすという答えは、セットアップを非常に複雑にし、十分にテストされているとは言い難いものに感じられ、Postgresを使う利点を打ち消してしまうように思えます。" - @mikeocool

"SQLiteの本番利用を真剣に検討している者として、ぶつかる壁の一つは、実行中のデータベースと対話するための優れたGUIをどう確保するかです。現在の本番データベースでは、DBeaverなどを接続してデータを閲覧したり、クエリを実行したり、時折修正したりできます。データベースがアプリが動作している同じVPS上の単なるファイルである場合、これはかなり厄介な問題になりそうです。" - @graboid

"誰かそれを試した人はいますか？」 - @wg0 (テナントごとのデータベースについて言及)

本番構成の設計図と考慮事項

データベース接続を初期化する際、各接続を開いた直後に以下の一連のpragmasを実行してください：

-- Write-Ahead Loggingを有効にする
PRAGMA journal_mode = WAL;

-- 破損のリスクなしに同期オーバーヘッドを削減する
PRAGMA synchronous = NORMAL;

-- ロックを適切に待機することでデッドロックを防ぐ
PRAGMA busy_timeout = 5000;

-- アクティブなワーキングセットに合わせてキャッシュサイズをスケールさせる (64MB)
PRAGMA cache_size = -64000;

-- 高速な読み取りのためにメモリマップドI/Oを有効にする (1GB)
PRAGMA mmap_size = 1073741824;

-- 外部キー制約を強制する
PRAGMA foreign_keys = ON;

-- WALファイルが無限に肥大化するのを防ぐ
PRAGMA journal_size_limit = 67108864; -- 64MB

-- インデックスページ割り当てとクエリプランを最適化する
PRAGMA auto_vacuum = INCREMENTAL;

"これらの最適化をデフォルトのSQLite設定と比較した本番環境向けのベンチマークがあれば素晴らしいのですが。" - @madhu_ghalame

"これはAIが生成したものだと確信していますが、それとは別に考えていることがあります。このような記事を見るたびに、彼らが実際に本番環境でSQLiteを使っているのか疑問に感じます。なぜなら、WALの使用のようなパフォーマンス最適化のポイントはいつも見かけますが、それを気にする前に直面するであろう厄介な問題や問題点については、一度も目にすることがないからです。" - @yladiz

"カラム定義は、作成後に alter column のようなもので変更することはできません。カラム定義を変更するには、writable_schema pragmaを使用して、基礎となるスキーマを手動で更新する必要があります。これを失敗すると、データベースが破損する可能性があります。" - @yladiz

"カラムの型はかなり限定的です。" - @yladiz

"スキーママイグレーションの処理には限られた選択肢しかありません。基本的に、マイグレーションをサーバーにコピーしてそこで実行するか（手動またはAnsibleなどを使用）、アプリケーションの起動時にマイグレーションを実行するかのどちらかです。" - @yladiz

"AIエージェントの時代にはサーバーレスSQLが必要です。インターネットを動かすためのコスト効率の高い技術を持つことは非常に重要です。そのため、私たちはPostgres互換エンジン用に独自のWALを書き上げましたが、驚いたのは、ログの形式よりもfsyncの順序に多くの作業が必要だったことです。それを緩和することは、クラッシュ時に最後の数件のトランザクションを失うという明確な選択になります。一部のワークロードには適していますが、それは誰かが意図的に下すべき決定であるべきです。" - @kiranravi1995

本番環境でSQLiteを選択すべき時

SQLiteは、数百ギガバイト以内に収まり、単一サーバー上での超低レイテンシを必要とする、読み取り中心のアプリケーションにとって、パフォーマンスが高く、運用が簡単で、コスト効率の高い選択肢です。

ただし、以下のような場合はSQLiteを避けてください：

  • 複数の地理的リージョンにわたる複雑な分散書き込みトランザクションを必要とするアプリケーション。
  • 数テラバイトを超えるデータセット。
  • スキーマの柔軟性と堅牢なマイグレーションツールが極めて重要となる環境。

"アプリケーションが複数の地理的リージョンにわたる複雑な分散書き込みトランザクションを必要とする場合、またはデータセットが数テラバイトを超える場合は、PostgreSQLのような従来のシステムが適切なツールであり続けます。しかし、システムが読み取り中心で、数百ギガバイト以内に収まり、超低レイテンシを必要とする場合は、アプリケーションサーバー上で直接SQLiteを実行することが、非常にパフォーマンスが高く、運用が簡単で、コスト効率の高いアーキテクチャの選択となります。" - Original post

"SQLiteが注目されているのは素晴らしいことです。本当に有能なデータベースですから。しかし、自分で試してみた結果、Postgresのようなデータベースが持つ多くのパワーが欠けているため、本番環境では決して使わないと思います。そのパワーのいくつかは、実際の本番環境の設定において実際に重要となるものです。" - @yladiz

"耐久性を犠牲にする準備ができている場合にのみ、これを行ってください（つまり、トランザクションを失う可能性がある場合）。" - @andersmurphy (PRAGMA synchronous = NORMAL について言及)

"一部のワークロードには適していますが、それは誰かが意図的に下すべき決定であるべきです。" - @kiranravi1995 (fsyncの順序の緩和について言及)

Sources