7-Zip vs Native MySQL and PostgreSQL Compression

This article compares using 7-Zip (LZMA/LZMA2) against native compression options in PostgreSQL and MySQL for database dump files. While native database dump utilities prioritize fast execution, low memory overhead, and direct restoration capabilities, 7-Zip focuses on maximizing storage reduction through advanced compression algorithms and large dictionary sizes. Understanding the differences in compression ratio, resource utilization, and operational workflows helps determine the best approach for database backups and long-term archiving.

Compression Algorithms Used

  • 7-Zip: Primarily uses LZMA and LZMA2 algorithms within the .7z container. LZMA2 allows very large dictionary sizes (ranging from 16 MB to 1 GB or more) and sliding look-ahead buffers, making it exceptionally effective at finding recurring patterns across massive text-based files.
  • PostgreSQL (pg_dump): Uses zlib (DEFLATE) by default when using the custom directory or tar archive formats (-Fc). Modern versions (PostgreSQL 16+) also support native LZ4 and Zstandard (zstd) algorithms.
  • MySQL (mysqldump / utilities): Outputs raw SQL text by default, which is typically piped through gzip (DEFLATE). Advanced tools like Percona XtraBackup or MySQL Shell (util.dumpInstance) natively support Zstandard and LZ4.

Compression Ratio Comparison

Database dumps consist predominantly of plain-text SQL statements, repetitive schema definitions, structured data, and recurring NULL or boolean values.

  • 7-Zip (LZMA2): Consistently produces the smallest file sizes. Because it can maintain large dictionary windows, it detects repeated data patterns across distant tables in the dump. For typical text dumps, 7-Zip can achieve an 80% to 90% reduction from the raw text size.
  • PostgreSQL Native (zlib/DEFLATE): Reaches an approximate 60% to 75% reduction. It uses a sliding window limited to 32 KB, meaning it cannot detect repetitive patterns that occur far apart in the file.
  • MySQL + Gzip: Similar to PostgreSQL's zlib implementation, achieving a 60% to 75% reduction. While Zstandard matches DEFLATE with better speed, it generally remains slightly behind 7-Zip's highest compression levels.

Speed and System Resource Usage

  • PostgreSQL and MySQL Native Tools: Optimized to minimize backup windows and production server impact. Zstandard and LZ4, in particular, provide high throughput with negligible CPU overhead, allowing backups to complete at near-disk write speeds. Memory consumption is predictable and kept low.
  • 7-Zip: Prioritizes density over raw speed. Compressing a dump file with 7-Zip at "Ultra" settings requires significant CPU time and hundreds of megabytes—or even gigabytes—of RAM per thread. Running 7-Zip directly on a production database host can cause CPU starvation and memory contention.

Workflow, Streaming, and Restoration

  • Selective and Parallel Restore: PostgreSQL's custom format (pg_dump -Fc) contains an internal table of contents. This allows pg_restore to run multithreaded restores using the -j flag, reorder objects, or selectively restore specific tables without decompressing the whole backup. If a dump is compressed as a monolithic .7z archive, selective restore requires fully unpacking the file or processing it sequentially.
  • Pipeline Streaming: Native database utilities stream data directly from the database engine to the compressed output file without creating an uncompressed intermediate file on disk:
    • PostgreSQL: pg_dump -Fc dbname > backup.dump
    • MySQL: mysqldump dbname | gzip > backup.sql.gz
    • 7-Zip can accept piped standard input (mysqldump dbname | 7z a -si backup.7z), but writing via standard input restricts multithreading in certain 7-Zip formats and prevents creating random-access indexes inside the archive.
  • Use Native Database Compression (or Zstandard) for:
    • Routine daily or hourly operational backups.
    • Environments where recovery time objective (RTO) is critical.
    • Backups run directly on the database server to preserve CPU and RAM for transactions.
    • PostgreSQL backups requiring parallel or selective table restores via pg_restore.
  • Use 7-Zip for:
    • Cold storage, archival backups, and data retained for long-term compliance.
    • Transferring large database dumps over slow or metered network connections.
    • Secondary processing workflows where a raw dump is compressed on an off-host backup server to save cloud storage costs.