Faster SQLite DB Transfer

April 9, 2025

Faster SQLite DB Transfer

Problem:

  • SQLite indexes are large (can be >50% of DB size)
  • Sending multi-GB .db files over network is slow
  • Exporting without indexes avoids this overhead

Solution:

  • Dump DB as SQL (indexes become lightweight CREATE statements)
  • Compress, transfer, and rebuild on target device

Commands:

# On remote: dump and compress
ssh user@host "sqlite3 my.db .dump | gzip -c > my.db.txt.gz"

# Transfer to local
rsync --progress user@host:my.db.txt.gz .

# Cleanup remote
ssh user@host "rm my.db.txt.gz"

# Reconstruct locally
gunzip my.db.txt.gz
cat my.db.txt | sqlite3 my.db
rm my.db.txt

Benefits:

  • ~14× smaller transfer size (e.g. 3.4GB → 240MB)
  • Indexes rebuilt automatically from SQL
  • Dump is stable (no corruption from mid-transfer DB writes)