こんにちは！最近Djangoのサイトを作っていて、データベースにSQLiteを使うことにしました。ウェブサイトのデータベースとしてSQLiteを使い始めたとき、たくさんのブログ記事を読んで、小規模なサイトなら本番環境でSQLiteを使ってもまったく問題ないと知りました。それは確かにその通りだと思います。ただ、SQLiteもやはりデータベースであり、データベースは複雑なもので、自分がデータベースの運用についてほとんど何も知らないということを、十分に理解していませんでした。

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

例に漏れず、まずはブログ記事で勧められていた通りにWALモードをオンにして、うまくいくことを祈りました。

ANALYZEはどうやら重要らしい

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

調べてみると、やるべきだったのはANALYZEを実行することでした！すると問題のクエリはすぐに5秒から0.05秒くらい（正確な数字は、気にならないほど小さくなったのでそれ以上調べていません）に速くなりました。クエリプランのどこが悪かったのかは今でも正確にはわかりませんが、おそらく意図せず二次関数的（accidentally quadratic）な動きになっていたのだろうと思います。

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は4年間（2022年から）SQLiteで動き続けていて、とても順調です。Postgresからの移行は、このプロジェクトにとっては大正解でした。

以上です！

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

参考資料

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

• The definitive guide to using Django with SQLite in production
• a gist on sqlite performance tuning