ハクソク

世界を動かす技術を、日本語で。

SQLiteの運用に関するいくつかの知識を学ぶ

2026年7月18日原文(jvns.ca)

概要

  • Djangoサイトで SQLite を運用した経験の共有
  • ANALYZEコマンド の重要性とパフォーマンス改善
  • データベースのクリーンアップや バックアップ 手法の紹介
  • ORMクエリや複数DB運用の実践例
  • 小規模サイトでのSQLiteの利便性と今後の課題

DjangoサイトでのSQLite運用メモ

  • SQLite を本番運用する小規模サイトでの実体験
  • Django ORMの活用でデータベースの負荷増加
  • WALモード （Write-Ahead Logging）を有効化し、パフォーマンス向上を図る手法
  • SQLiteも他のDB同様に 運用知識 が必要である実感

ANALYZEコマンドの重要性

  • FTS5による全文検索クエリが 4000行で5秒 もかかる事象
  • ANALYZEコマンド 実行でクエリが0.05秒に短縮
    • ANALYZEはテーブル行数などの 統計情報 を生成し、クエリプランナーの最適化に寄与
  • クエリプランの理解や調整の必要性を実感

データベースのクリーンアップ

  • 不要な行（例：django-tasks-dbの完了タスク）の削除作業
  • DELETE文 の遅さや、トランザクション中のPythonコードの影響を疑問視
  • クリーンアップ中に他ワーカーの書き込みがタイムアウトし クラッシュ する問題
  • 現状は 小さなバッチ での削除実行で対応
  • 本格的なDB運用では Postgres など複数書き込み対応DBの利便性を再認識
  • 今後は メンテナンスモード の検討も課題

Django ORMクエリのパフォーマンス

  • ORMのパフォーマンスを特に意識せず運用
  • データ量が 1万行程度 であれば大きな問題なし
  • 今後も小規模なら現状維持方針

SQLiteのバックアップ手法

  • restic を使ったバックアップ
    • VACUUM INTOでDBファイルを抽出、gzip圧縮、S3にアップロード
    • OOMでロックが残る場合はrestic unlockで解除
    • 定期的なバックアップ・スナップショット・古いバックアップの削除運用
  • Litestream による増分バックアップ
    • 設定ファイルでAWS S3へレプリケーション
    • retention: 400hで履歴保持を試行
    • AWS認証情報の管理が手間
    • S3互換サービスへの移行検討も視野

複数データベース運用の工夫

  • プロジェクトによって DBファイルを分割 して運用
    • Mess with DNSでは3つのDBファイルに分割し、運用効率化
    • 2022年からSQLite運用を継続し、 Postgresからの移行 が正解だったと実感

学びと今後の課題

  • 基本的なSQLite運用ノウハウの習得に 時間がかかった 体験
  • 今後も新しい機能や運用テクニックを 継続的に学習 予定

参考資料

  • The definitive guide to using Django with SQLite in production
  • A gist on sqlite performance tuning
  • SQLite公式ドキュメント

Hackerたちの意見

AWSにバックアップしてるんだけど、AWSコンソールで認証情報を生成するのがめんどくさくて、いつもイライラしてる。数年前にそのことでめっちゃストレス溜まって、結局その問題を解決するためだけにツールを作っちゃったんだ。uvx s3-credentials create my-existing-s3-bucket これで、そのバケット専用の読み書きできる認証情報が出力されるんだよ。さらに制限したい場合は、--read-onlyや--write-onlyを追加すればいいし、--prefix foo/barを追加すれば、そのプレフィックスで始まるキーだけを読み書きできる認証情報も作れる。 > いつか他のS3互換の代替品に移行するかもしれないけど、Cloudflare R2とResticを使ったらすごくうまくいったよ。

└

これはかなり素晴らしいね、AWS CLIはこれをシェルロックすべきだ。書き込み専用が必要な時っていつ？

└

先行技術としてもhttps://litestream.io/があるよ。

いつかクエリプランを読むことを学ぶかもしれない。SQLiteの.expertモードを使えば、その日をもう少し先延ばしにできるよ: https://www.sqlite.org/cli.html#index_recommendations_sqlite... sqlite> CREATE TABLE x1(a, b, c); -- データベースにテーブルを作成 sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- このSELECTを分析 CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (a=? AND b>?) sqlite> CREATE INDEX x1ab ON x1(a, b); -- 推奨インデックスを作成 sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- 同じSELECTを再分析（新しいインデックスはなし） 0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?) それと、> これまでのアプローチは、5秒以上かかるデータベースクエリを避けるために、クリーンアップ操作を小さなバッチで行うことだった。この経験から、同時に複数のライターがいる「本物の」データベース、例えばPostgresを使いたくなる理由がわかってきた。これらの「本物の」データベースに関するアドバイスも、一般的にはクリーンアップ操作を小さなバッチで行うことなんだけど、小さいケースではパフォーマンスが悪いことをやってるのがあまり目立たないんだよね。君の言ってることは、思ってたよりも正しいよ！

└

パフォーマンスが悪いことをやってるのがあまり目立たないって、これが強みとして位置づけられてるの？

└

大きなMySQLデータベースで行ベースのレプリケーションを使ってたことがあって、数百万行に影響を与えるUPDATEやDELETEはバッチで適用しなきゃいけなかったんだ。そうしないと、一つのSQLクエリで百万行が一度に全てのレプリカに送信されることになっちゃうから。

└

いくつかのデータベース（例えばOracle）で1000万行を削除するもう一つの副作用は、データベースが1000万行分のアンドゥを書き出すことだよ。これがアーカイブログ用に確保しているディスクスペースを圧迫することがあるから、早くバックアップしてクリアできないと大変だよね。頻繁にコミットするのが助けになるけど、大規模なデータベースがあって定期的にパージする必要があるなら、私の経験上、パーティショニングを使うのが一番いい方法だと思う。古い（または何でもいいけど）パーティションを削除するのはほぼ瞬時で痛みもないし。著者が何をしていたのかははっきりしなかったけど、「ワーカーがデータベースに書き込めなくてクラッシュして、VMがシャットダウンする」って、なんでVMがシャットダウンするの？ここでのVMは仮想マシン（OS）を指してると思うけど。

└

EXPLAIN QUERY PLANに似てるね: https://sqlite.org/eqp.html 生のEXPLAINはバイトコードをダンプするけど、通常は必要以上に冗長だよ。EXPLAIN QUERY PLANは要約をダンプする。

それと、他にも色々？ インデックスの値分布に関するさまざまな統計的ビューがあって、プランナーがそのインデックスがどれだけ有用（選択的）であるべきかを推定できるようになってる。sqlite_stat1は平均（インデックス内のレコード数と値ごとの平均レコード数）を提供するだけで、sqlite_stat4を有効にするとヒストグラムデータを保存する。

バックアップはこんな感じでやってるよ: OUT="${i}.sql.zst" PART="${OUT}.part" sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" - mv "${PART}" "${OUT}" これだとライターをブロックしないし（ライターがWALを使ってるとき）、圧縮もよくて、同期も簡単にできるんだ。俺のHome AssistantのDBは1.8GBで、ダンプは圧縮して286MBになってるけど、その90%は毎日一貫してると思う。

└

それはライターをブロックしない（ライターがWALを使っているとき） VACUUM INTOや".backup"（バックアップAPIを使う）やsqlite3_rsync、litestreamも同様だね。

└

Home Assistantから何をバックアップしてるの？デフォルトのバックアップはすごく大きいけど、結局は設定だけにして、動画やキャッシュはオフにしてる。HACSでダウンロードしたリポジトリも全部オフにしてるんだけど、これで何か見逃してるのかな？HAのDBには何が入ってて、そんなに大きくなってるの？たくさんの履歴のタイムシリーズを保持してるの？

Hacker Newsで議論の続きを見る