SQL files are highly repetitive, I have been using bzip2 (pbzip2) for over 20 years, but is it really a sane choice in 2025 ?
We already know about gzip (even older than bzip2), it is super fast, but the compression is not great, so the options in question are “zstd” and “xz (LZMA2)” and “brotli”
zstd -19 -T12 myfile.bin (-19 for maximum compression (5 for low) T12 for 12 CPUs), original file is kept by default
unzstd myfile.bin.zst
xz -k -T0 -9 myfile.bin (Auto threads, high compression, -k for keeping original file)
Compressing a 200GB SQL file on an intel i7-4930K using 8/12 threads
zstd at compression level 19 (MAX) = 20.63% ( 40 GB)
xz at compression level 9 (Max) = 38GB
So, xz at max compression took much longer to finish, but resulted in a 2GBs smaller file….
zstd at compression level 15 seems to be the sweet spot, I will redo the experiments soon and bring in more precise results on what compression and how much time.