SQLiteデータベースのPC間コピーを高速化する方法！

それ、どんなデータベースなの？何年もSQLiteの代わりを探してるんだけど…正直、あんまりちゃんと探せてないんだけどね。組み込みDBは趣味みたいなもんで、最近はあんまり時間なかったんだ。まだ見つけられてないんだよね。Rustで書かれたsledっていうのは、key-value storeに近い感じだし、ちょっと使ってみたけどうまくいかなくて。KV storeをrelational databaseにするのは手間がかかりすぎるんだ。

俺、今PoCで作ってるKV storeがあってさ、Rustで書いてるんだけど、シングルコアで秒間1億5千万件以上書き込めるんだ（8バイトのデータ入れた場合ね。ディスクの速度で制限されちゃうけど最新のNVME PCIE5 disksでもすぐに頭打ちになる）。application RAID0に対応させて複数のディスクに分散できるようにする予定だけど、それはDB作る時に設定が必要になる。その上にSQL engineを載せようと思ってるんだけど、SQL載せるとどれくらい遅くなるかは不明。でも、誰も興味なさそうなんだよね。他のDBよりperformanceが数桁上なのは分かってるんだけどさ。

@zeroqと同じで、俺もK/V storeじゃなくてちゃんとしたrelational databaseが必要なんだ。analyticsとかtime seriesにも使いたいし、embeddedが良い。それに、できるだけstrictなのがいいな—PostgreSQLはそれがすごくできてる。自分で plumbing work はやりたくないんだ。もしどうしても必要になったらやるけどね。今はDuckDBとかClickHouse(-Local)が良い候補なんだけど、まだnormal OLTP performanceは試してないんだ。(EDIT: でも、それ止めないでね。publishしてHNでもannounceしてくれよ。)

さっきも言ったけど、その上にrelational layerを追加するつもりだよ。RocksDBがFacebookのMySQL & other databasesの下に使われてるみたいにね。

kvは俺のuse caseには合わないな。importした後で、不要なデータを消すためにいくつかqueriesを実行してるんだ。import中にfilteringすることもちょっと考えたんだけど、（a）relationshipsの処理が難しい、（b）データをimportするsimple functionがあって、その後不要なものを取り除くcleanなone linersで書ける方がcodeがずっときれい、（c）後でsql queriesが必要になる、っていう理由でやめたんだ。

正直、また別のSQLiteは微妙かな。100M/s→500k/sは非現実的っぽい。タプルだけインポートだと1コアしか使わないし。並列インポート→マージも考えたけど時間なかったんだ。10Gbは大きいしね。
でも、どこでも動くSQLiteの普及率はすごいと思う。pgliteはニッチな用途以外メリットないかな。
SQLiteには隠れた名機能いっぱいあるよ。HTTPでホストしてリモートDBみたいに使えるとか！10GbファイルもS3に置いてcount(*)なら40kbくらいしか使わないんだ。

新しいSQLiteより、改善したのが欲しいって意見に同意。どこでも動く普及率は無視できないね。ほとんどのアプリは専用DBいらないと思うから、今Elixir＞Rust＞SQLiteライブラリ作ってる。
pgliteはニッチ用途以外メリットないって意見も分かる。SQLiteにPostgreSQLみたいな厳密なスキーマがあれば1000ユーロ払う！型アフィニティが嫌い。SQLiteは変わらないだろうから新しいのが必要だけど、テスト数には勝てない。S3にSQLite置いてHTTP経由で使うのは、リモートDBならPostgreSQLで良くない？って思うのは厳密さ重視な僕の偏見。

@typesの話＞君の味方だよ。SQLiteはニッチ用途で、アプリ層で型を強制すればいい。信じるな、検証しろ。protobuffersとかと同じ、正確性保証は君の責任。
@serverlessの話＞メリットは速く動けるし、メンテ箇所少ない。個人的にデフォルトの考え。2025年でもショップのフィルターで全ページリロード多いけど、在庫全部取ってきてアプリ側でフィルタリングすれば良いじゃん？95％でいけるのに。プロセス簡素化したいんだ。

RE: serverless、SQLiteは組み込みで優れてるのが一番かな。Litestreamとかはそんな重要じゃない。
RE: 厳密な型、めっちゃコード書いて検証してるよ。作るFFIライブラリ(Elixir＞Rust＞SQLite)でも最終的な型確認はユーザーに任せる。生FFIはクラッシュ防止、詳細エラーを目指す。割り込み可能なSQLite操作も作り始めてるよ。
信頼せず検証たくさん、その通り。でも生SQLで文字列を整数に入れられないとか、もっと安全性欲しいな。でもSQLiteは最高峰のソフト。ちょっとバリデーション書けば使えるなら、お得だよ。

いや、そうじゃないんだ。
pglite含めいくつか試したけど、Nodeがバージョン23でネイティブのsqliteを搭載して、僕にはこれで十分なんだ。
僕はサーバーレスファンで、SQLiteの隠れた名機能の一つが、DBをHTTPサーバーに公開してクライアントから超効率的にクエリできること。
小さなベンチマークプロジェクトも別で持ってたけど、公開は誰かの時間の価値もないと思ってやめたんだ x]

そのベンチマークのデータが小さい（28MB）ってことに注目すべきだね。これはDBエンジンで違うけど、「全て一つのトランザクションで」ってのは、アロケーションを保持してるってこと。
最適なトランザクションサイズは計算難しいから測るべきだけど、一つのトランザクションに数秒費やすのはほぼ間違いなく得策じゃないよ。
データ（やインデックス付きデータ）のサイズがメインメモリ超えると、変なパフォーマンス変化も出てくるだろうね。

そう、でもこの問題には触れてないね。
CREATE INDEXしてからINSERTするか、INSERTしてからCREATE INDEXするか。
つまり、INSERTにかかる時間だけを測ってて、全INSERT後のCREATE INDEXは測ってないんだ。

面白いね、質問じゃないStack Overflowの投稿に3000票以上なんて。でも面白い記事だね。ルール全部破っても許されるくらい面白いってことかな？

それは（かなり古い）コミュニティwiki投稿なんだよ。これらは（特に当時は）機能してて、違う扱いをされてたんだ。

最適化ってどこがボトルネックかだよね。この記事の人は回線みたいだけど、俺の環境はCPUとかDisk IOは速いから、職場のサーバーから2GBのSQLiteテストDBを1Gbps回線で15秒で取ってこれるんだ。

30分は長い気がするな。データ量多いの？
俺は大量のjsonデータからsqlite dbs作る作業してたんだけど、値のリスト持って1万件ずつinsertしたら、めちゃくちゃ速いポイントを見つけたんだ。これで数百万行のinsertが数分でできる。
bloom filtersとかLRU cachingとか小技も使ったけど、今は6ギガのDBを20分くらいで作れるようになったよ。

コメント3411さんへ。データはCSV複数ファイルで約10ギガ。インメモリDBに単一トランザクションで全テーブルインポートしてる。毎分1500万行insertできるけど、毎秒45万insertが限界。その後、不要データ削除やインデックス追加、optimize, vacuumもしてるよ。Ryzen 5900Xでのログも貼っとくね。（約199字）

数百万行/分ってのは、テーブルのカラムが多いとかならわかるけど、普通じゃない気がするな。SQLiteのinsert性能って、ディスクの最大書き込み帯域の少なくとも1％、できれば5％以上が目安だよ。俺がやった最後のバルクinsertでは20％以上出てたし、8カラムのINTテーブル（小さい整数）で毎秒90万insertだったよ。

ちなみに、SQLiteには公式ツールでこれ（rsync）があるよ：https://www.sqlite.org/rsync.html
ページ単位で動くんだ：
レプリカ側が各ページの暗号化ハッシュをオリジナル側に送って、ハッシュが一致しないページの全内容をオリジナル側が送り返すんだって。

うん、でも残念ながらSQLiteチームは、多くのdistrosやbrewがSQLiteをパッケージする時に使う”autotools”のtarballにそのツールを含めてないんだよね。
ツールを使う唯一の方法は、自分でコンパイルすることだよ。

うん、それは残念だね。
でも、nixpkgsには入ってるみたいだよ：
nix-shell -p sqlite-rsync
試してみて。

現実的にさ、SQLite使ってるなら、自分でコードコンパイルしたりソース管理したりしないの？
それってそんなに大したことなの？

そうだよ、自分で何も、ましてやCとかサポートライブラリとかコンパイルせずにSQLite使うのって、めっちゃよくあることだよ。

sqlite3_rsyncはリモートにも入れる必要あるんだね。クロスコンパイルしたりPATH通したり、ssh設定もいじったり。面倒な作業が増えちゃうな。難しくはないけど、ちょっと本題と関係ない手間がかかる感じ。

最近出たsqlite_rsyncツール、すごいよ。SQLiteの内部構造に最適化されてて、効率よくページ比較して変更だけ同期できるんだ。記事のやり方も良いけど、こっち使った方が楽かもね。詳しいことはブログに書いたから見てみて！
https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-w…

そうそう、sqlite3_rsyncはもうrsync.netで使えるようになってるんだって。sshで繋いでコマンド打つだけ。先週追加されたばっかで全部じゃないみたいだけど、使った人はみんな期待通りだって言ってるよ。

sqlite_rsyncはWALモードじゃないとダメなんだって。あとWALモードだとDBファイルはローカルディスクにないと使えない制約もあるみたい。普段は大丈夫だろうけど、そうじゃない時はこのツール使えないね。

sqlite3_rsyncに実験的な変更入れたとこ。–wal-onlyなしなら非WALでも動くようになったよ。ただし、同期中は元DBは書き込みNG、レプリカDBは読み書きどっちもNGになっちゃう。WALじゃないとしょうがないんだけどね。でもDELETEモードのDBも同期できるのは便利だろうし。もし試せそうだったら、これで問題解決したか教えてほしいな。パッチはここだよ
https://sqlite.org/src/info/2025-05-01T16:07Z

アップデート情報：この改善、もうメイン開発ラインに入ったよ。次のSQLite 3.50.0リリースに含まれるって！4週間後くらいかな。

WALモードは、一度に1台のPCだけ書き込むなら、結構いろんなネットワークファイルシステムで動くよ。

ごめん、コメントの意味がよく分かんないんだけど。WALとかネットワークファイルシステム関係なく、SQLiteファイルって複数のプロセスから同時に書き込めないよね？何か見落としてる？それとも言い間違えた？

いや、複数のプロセスから（同じPC上なら）同時に書き込み接続はできるんだよ。SQLiteが自動で順番に処理（シリアライズ）してくれるから安全。低レベルで見れば同時じゃないけど、使う側からしたら自分で順番考えなくていいから同時みたいなもん。

しょっちゅう同期すると、ページサイズ大きくしないといけなくて（帯域幅食う）、大変になるかも。

テキストファイルに保存するのは非効率だな。俺はVACUUM INTOを使ってsqliteデータベースを保存してるよ、こんな感じでさ： sqlite3 -readonly /path/db.sqlite ”VACUUM INTO ’/path/backup.sqlite’;”
VACUUM INTOを使うとサイズが最小になってファイルシステムのI/O量が減るかも。詳しくはここ見て：https://sqlite.org/lang_vacuum.html

それはクールだけど、元の記事で言ってたインデックスの問題は解決しないね。遅いリンク経由でインデックスデータを持っていかないのがキモだったんだ。VACUUM INTOの方法だとインデックスが残るからね。テキストファイルはそのままじゃ非効率でも、gzipみたいな簡単なツールでも完璧に圧縮できるよ。

SQLiteはSQLをバイトコードにパースしたら、もうSQLは捨てちゃうんだ。テキストを使うのは、きっとうまくいくんだろうけど、時間かかるだけだよ。バキュームする前にインメモリデータベースを作って、選んだテーブルだけそこにコピーして、それからディスクにバキュームする方法もあるよ。

前のコメントの圧縮の話だけど、インデックスのこと言ってるんだよね？圧縮がどう動くか、考え直してみるといいかもよ。テキスト+圧縮は、同じ内容を表すバイナリ+圧縮より常に大きくて遅くなるはずだけど？バイナリの方が構造をコードで持ってるから元々コンパクトで効率良いはずだよ。

俺はデータベースコピーの速さより圧縮サイズの話をしてるんだ。インデックスやb-treeもあるけど、バイナリとdump+圧縮どっちが有利か検証してみたよ。767MBファイルで、gzip圧縮後サイズはdumpが285MB、VACUUM INTOが303MB。dumpは少し小さいけど、dumpに時間かかるから微妙。インデックス無しだとバイナリがdumpより8％小さかったよ。

この結果は予想通りだよ。インデックスの影響も言ってた通り。dumpのテキスト形式は「INSERT INTO …」とかのDDLや、BLOB・数値データがテキストエンコーディングになるから膨らむんだ。これはb-treeのオーバーヘッドよりも大きくなるね。

他の検証データもあるよ。zstdで圧縮した結果、VACUUM INTO + zstd (–long -12)だと19.1秒でdump + zstd (–long -5)の109％のサイズになったみたい。dump + zstd (-12)が827MBで31.7秒、VACUUM INTO + zstd (-12)が885MBで21.5秒。76MB多くても13.7秒速いってこと。

ターゲットサイトの帯域幅によるね。かなり遠隔地かもしれないし、公開インターネットサービスに繋がってないかもだし。

それってインデックスも保持するの？TFA（この記事）にも書いてたけど、インデックスがあるからsqliteファイルが大きくなるんでしょ。

多分インデックスは保持しないと思うけど、text sqlを実行するときにインデックスを再作成するんじゃないかな。

rsyncが提供してる転送中の圧縮を使ってみなかったのに驚いたな: -z, –compress ファイルデータを転送中に圧縮する –compress-level=NUM 圧縮レベルを明示的に設定する
たぶん一度gzipで圧縮してから転送する方が速いんだろうけど、フラグ一つで転送を改善できるのはいいね。

もっといいのは、記事で破損の問題に触れてるんだから、sqlite3_rsync (https://sqlite.org/rsync.html) を-zsqliteオプション付きで使うことだよ。トランザクションとWALを考慮したrsyncで、転送中圧縮もできる。

重要なのはインデックスをスキップすることで、これは圧縮の前にやらなきゃいけない。俺がこういうことやるときは、ダンプを直接gzipにストリームしてるよ。（中間ファイルなしで直接宛先にストリームする方法はたいてい見つけられる。）
さらにこの方法だと、宛先でも圧縮されたまま保存されるんだ。もし目的が安易なレプリケーションじゃなくてバックアップならね。

重要な点は転送時間を減らすことだったでしょ？もしrsync -zで十分短くなるなら、インデックスがあろうとなかろうと関係ないし、テキストファイルからDBを再作成するステップもスキップできるじゃん。

記事のポイントは、インデックスがあるかどうかが”重要”なんだよ。それにインデックスってそもそもあんまり圧縮できないんだよね。うまく圧縮できるのは、だいたい人間が読めるテキストフィールドとか、booleanとかenumみたいなものだよ。