こんにちは！最近Djangoのサイトを作っていて、データベースにSQLiteを採用しました。使い始めるときに「小規模なサイトなら本番環境でSQLiteを使ってもまったく問題ない」という趣旨のブログ記事をたくさん読みましたし、実際まったく問題ないと思います。ただ、見落としていたのは、SQLiteもやはりデータベースであること、データベースは複雑なものであること、そして自分はデータベースの運用についてあまり詳しくないということでした。

というわけで、SQLiteの運用について学んだちょっとしたことをいくつか紹介します。SQLiteを使ったウェブサイトはこれで4つ目なのですが、今回はDjango ORMの力で以前よりもデータベースに多くの仕事をさせているせいか、少し難しく感じています。

まずはブログ記事で勧められているとおりにWALモードを有効にして、あとはうまくいくことを祈りました。

ANALYZEはどうやら重要らしいです

今日、4000行ほどのテーブルに対してクエリを実行したところ（SQLiteのFTS5を使った全文検索です）、5秒もかかってしまいました。これはおかしいと感じました。コンピューターは速いはずですから！

原因はANALYZEを実行する必要があったことでした！実行した途端、問題のクエリは5秒から0.05秒程度まで一気に速くなりました（それ以上細かく調べる必要を感じないくらい十分な速さでした）。クエリプランのどこが悪かったのかは今でも正確にはわかっていませんが、おそらく何らかの意図せず二次関数的に重くなる現象だったのだろうと思います。

ANALYZEは、クエリプランナーがより適切な判断をできるように「統計情報」（おそらく各テーブルの行数など、ほかにもいろいろあるのでしょう）を生成します。

いつかクエリプランの読み方も覚えたいところです。

データベースの掃除は意外と厄介です

ときどき、データベースに不要な行が大量に入ってしまい（たとえばdjango-tasks-dbの完了済みタスクなど）、削除したくなることがあります。

そういうときに何度か起きたのは、次のような流れでした。

1. 行を削除するための何らかのコマンドを実行します
2. 行数が多いためコマンドが5秒以上かかります（正直、なぜこんなにDELETEが遅いのかはまだ疑問です。トランザクションの中で大量のPythonコードが実行されているのかもしれませんが、よくわかりません）
3. その間に別のワーカーがデータベースへの書き込みを試み、5秒でタイムアウトします（タイムアウトは5秒に設定しています）
4. データベースに書き込めなかったワーカーがクラッシュし、VMがシャットダウンします

今のところは、5秒以上かかるクエリを実行しなくて済むように、クリーンアップを小さなバッチに分けて行うことで対処しています。ただ、こうした経験を通じて、同時に複数の書き込みができるPostgresのような「本物の」データベースを使いたくなる理由もよくわかるようになりました。

今後はこうした作業が必要なときは、割り切ってメンテナンスのためにサイトを一時停止する方法も考えていますが、まだ運用の流れは決められていません。

ORMクエリのパフォーマンスについてはまだ特になし

今のところ、DjangoのORMで思いつくままにクエリを書いていて、パフォーマンスはまったく気にしていませんが、ANALYZEの一件以外はおおむね問題なく動いています。データベース自体もかなり小さく（1万行程度でしょうか）、今後もずっと小さいままだろうと思っているので、このやり方でいけると期待しています。

SQLiteのバックアップ

SQLiteのバックアップは2通りの方法でやってきました。バックアップからの復元を実際に試したことはたぶんありませんが、デッドマンズスイッチで監視するようにはしています。

方法1: restic

sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite

# Upload backup to S3
# Sometimes the backup gets OOM killed and so it stays locked, do an unlock
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
# Do the backup & prune old backups
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
restic -r s3://s3.amazonaws.com/some_bucket/ prune

方法2: litestream

最近Litestreamを試し始めました。増分バックアップのほうが効率的かもしれないと思ったからです。resticでのバックアップはときどきOOMキラーにやられてしまい、少しうんざりしていました。基本的には設定ファイルを書いて、次のコマンドを実行するだけです。

litestream replicate -config litestream.yml

設定ファイルではデータベースの履歴をある程度残すためにretention: 400hと指定していますが、これが本当に機能しているのかはよくわかりません。

バックアップ先はAWSにしていますが、認証情報を作るためにAWSコンソールを操作するのがいつも面倒です。いつかS3互換の別のサービスに移るかもしれません。

データベースは複数使えます

今のプロジェクトではデータベースは1つだけですが、Mess with DNSではテーブルを3つの別々のデータベースファイルに分けるという工夫をしました。テーブルを同じDBに入れておく必要がなかったからです。これは役に立ったと思います。

Mess with DNSは2022年から4年間SQLiteで運用していますが、とても順調です。Postgresからの移行はあのプロジェクトにとって良い選択だったと思っています。

以上です！

使っている技術の基本的なことを覚えるのにどれだけ時間がかかるかを見るのは、いつもちょっと面白いものです。ウェブプロジェクトで初めてSQLiteを使ったのは2022年だったと思いますが、ANALYZEという機能があることを知ったのは今日が初めてでした！1、2年後にはまた別の基本的な機能を学んでいるんだろうなと思います。

参考資料

公式ドキュメント以外で参考にしたブログ記事などです。

• 本番環境でDjangoとSQLiteを使うための決定版ガイド
• SQLiteのパフォーマンスチューニングに関するGist