A faster way to copy SQLite databases between computers (alexwlchan.net)

Article summary

The article discusses a method to efficiently copy SQLite databases between computers by dumping the database as a text file, compressing it, and then reconstructing the database on the local machine. This approach reduces the size of the database by excluding indexes, making the transfer faster and more reliable. The author claims that this method has saved them hours when working with large databases. The technique involves using SQLite's built-in .dump command and gzip compression.

Main themes

• SQLite database transfer
• compression algorithms
• rsync and ssh optimization
• database backup and restore
• network transfer efficiency
• compression vs transfer speed tradeoff

What commenters say

• Using zstd compression is superior to gzip for its better performance and efficiency.
• Rsync's built-in compression can be a viable alternative to the proposed method.
• Skipping indexes during database transfer is essential for efficient compression and faster transfer times.
• Compression is not always necessary and its usefulness depends on the specific situation and network conditions.
• The proposed method may not be the best approach for incremental backups, where rsync or sqlite3_rsync might be more suitable.
• Using a single pipeline with ssh and gzip/gunzip can simplify the process and eliminate redundant steps.
• The efficiency of compression algorithms like zstd and gzip can vary greatly depending on the hardware and data being compressed.
• In-flight compression, as provided by rsync or ssh, can be a better option than pre-compressing the data.