コンピュータ間でSQLiteデータベースをより高速にコピーする方法

• SQLiteデータベースをコンピュータ間でより高速にコピーする方法についての説明
• データベースのインデックスがコピー速度を遅くする主な原因
• SQLiteの.dumpコマンドを使ってデータベースをテキストファイルとしてダンプできる
• テキストファイルは元のデータベースより小さく、圧縮するとさらに小さくなる
• この方法により、大容量データベースをより高速かつ安定してコピーできる

---

コンピュータ間でSQLiteデータベースをより高速にコピーする方法

• リモートサーバーに保存されたSQLiteデータベースをローカルコンピュータにコピーする方法についての説明
• 初期のプロジェクトではrsyncコマンドを使って簡単にコピーできる
• データベースが大きくなるとコピー速度が遅くなり、信頼性も低下する

データベースダンプをテキストファイルにする

• SQLiteは.dumpコマンドを使ってデータベースをテキストファイルにダンプできる
• このテキストファイルはSQL文で構成されており、元のデータベースより小さくなることがある
• インデックスはテキストファイル内では単一行に縮約され、保存容量を節約できる

圧縮による保存容量の節約

• テキストファイルは圧縮するとさらに小さくなる
• 例えば、元のSQLiteデータベースが3.4GBの場合、gzipで圧縮したテキストファイルは240MBまで減る
• 圧縮されたテキストファイルをダウンロードすれば、データベースのコピーははるかに高速になる

新しいssh+rsyncコマンド

• サーバー上でgzip圧縮したテキストファイルを生成し、ローカルコンピュータにコピーしたあとデータベースを再構築する
• サーバーで圧縮済みテキストファイルを生成: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
• ローカルコンピュータへファイルをコピー: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
• 展開してデータベースを再構築したあと、ローカルファイルを削除する

データベースダンプは信頼できるコピー元

• データベースのコピー中に更新が発生すると、rsyncが壊れたデータベースファイルを生成してしまう可能性がある
• テキストダンプを作成して安定したコピー元を提供することで、この問題を解決できる
• この方法は大容量データベースを扱う際の時間を節約し、ダウンロードをより高速かつ信頼性の高いものにする

[GN⁺]

Hacker Newsの意見

• SQLiteは公式ツールを提供している。ページレベルで動作し、レプリカが各ページの暗号学的ハッシュを送信元に送り、ハッシュが一致しないページの内容全体を送信元が再送する
• 実行中のデータベースファイルをコピーすると破損する可能性がある。安全なレプリケーションのためにLitestreamを使える
• データベースをコンピュータ間でコピーする方法として、円を送って残りの部分は無視する方法がある
  • 増分rsyncのほうが速いが、SQL文を送るほうがデータベース自体を送るより速いという主張には同意しない。SQL文を実行し、最適化やVACUUMを行う必要がある
  • CSVファイルからデータベースを「増分的に再構築」しなければならないシナリオがある。データベースを最初から作り直すほうがより最適だが、メモリ内の空のデータベースにバッチ挿入を実行するだけで30分かかる
• 最近リリースされたsqlite_rsyncユーティリティは、SQLiteデータベースの内部構造に最適化されたrsyncアルゴリズムのバージョンを使っている。内部データページを効率的に比較し、変更または欠落したページだけを同期する
• テキストファイルとして保存するのは非効率だ。VACUUM INTOを使ってsqliteデータベースを保存する
  • VACUUMコマンドはバックアップAPIの代替であり、結果のバックアップデータベースは最小サイズになるため、ファイルシステムI/Oが減る
• rsyncが提供する圧縮機能を使っていないのは驚きだ。gzipで圧縮してから転送したほうが速いかもしれない
• DuckDBではParquetにエクスポートしてデータサイズを減らせる。転送とロードの速度がより速い
• SQLiteはセッション拡張を提供しており、テーブルの変更を追跡し、以前のバージョンのSQLiteデータベースにパッチを当てられる変更セット/パッチセットを生成する
• gzipの--rsyncableオプションを使って最適化できる。圧縮率はやや下がるが、差分を局所化して圧縮出力全体に影響しないようにする
  • ダンプ出力の圧縮をスキップし、rsyncに以前の非圧縮ダンプと現在のダンプの差分を計算させたうえで、rsyncがネットワーク経由で送る変更セットを圧縮させることができる
• 2008年にPostgresを使って複数のマシンへバックアップを送る必要があった経験がある。ネットワークが飽和状態になったため、udpcastを使ってバックアップを一度にすべての宛先へ送信した