A faster way to copy SQLite databases between computers

Original Article ↗ Hacker News Discussion ↗

Approaches to copying SQLite databases

  • Many see the article’s SQL-dump-over-ssh approach as standard practice across databases: dump on source, pipe over network, recreate on destination.
  • Several suggest simplifying to a single pipeline (no temp files), e.g. remote .dump piped directly into local sqlite3.
  • Others favor copying the DB file itself using scp or rsync, especially when indexes should be preserved and bandwidth is not the bottleneck.

Compression choices and performance

  • Suggestions to replace gzip with zstd (or sometimes brotli, lz4, pigz/igzip) due to better compression ratios and throughput.
  • Debate on whether compression helps only on slow links vs almost always (because CPU is usually faster than disk/network).
  • Benchmarks shared: zstd can reach multi-GB/s on large files, but real-world speeds and benefits depend heavily on data type, file size, and hardware.
  • Some note that compressing text dumps vs compressed SQLite binaries gives modest size differences; in one example, text+gzip was smaller, in another, raw DB+zstd won when indexes were removed.

Incremental sync vs one-off snapshots

  • Several argue incremental backup via rsync (or sqlite3_rsync) is generally faster than repeated full dumps, especially when data changes are small.
  • For one-off or occasional transfers over slow links, text dumps without indexes can be faster overall despite rebuild costs.
  • rsync -z is highlighted as a simpler alternative, though some point out that precompressing defeats rsync’s delta algorithm.

Safety and consistency for live databases

  • Strong warnings that naïvely copying a live SQLite file can corrupt or produce inconsistent databases.
  • Recommended options:
    • SQLite’s .backup / backup API.
    • VACUUM INTO to create a compact, consistent copy.
    • Filesystem or block-level snapshots (LVM, ZFS, Btrfs), combined with WAL guarantees.
    • Dedicated tools like Litestream or SQLite’s sqlite3_rsync.
  • Discussion acknowledges subtleties around WAL, crash consistency, and snapshot behavior.

Indexes, size, and rebuild cost

  • Core idea of the article—skip indexes during transfer—receives mixed reactions:
    • Pro: avoids sending often-massive index pages that compress poorly.
    • Con: index recomputation can dominate time for large DBs; some workflows show tens of minutes spent on bulk inserts + index builds.
  • Examples of schemas where index size greatly exceeds data, justified by query performance needs.

Alternative formats and tooling

  • DuckDB suggested for exporting SQLite data to Parquet (with zstd), yielding dramatically smaller, analytics-friendly files; but this typically omits indexes and full schema unless using EXPORT DATABASE.
  • SQLite session extension mentioned for change-data capture and offline-first sync use cases.
  • Other ecosystem parallels: PostgreSQL (pg_dump/pg_basebackup, replication), ZFS snapshots, Git with custom SQLite diffs.