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)

Notes:

Streaming replication for SQLite https://github.com/benbjohnson/litestream https://litestream.io/alternatives/cron/