{"id":6089,"date":"2025-12-26T15:32:39","date_gmt":"2025-12-26T15:32:39","guid":{"rendered":"https:\/\/www.voodoo.business\/blog\/?p=6089"},"modified":"2025-12-26T15:32:40","modified_gmt":"2025-12-26T15:32:40","slug":"best-compression-algorithim-for-my-sql-file-backups","status":"publish","type":"post","link":"https:\/\/www.voodoo.business\/blog\/2025\/12\/26\/best-compression-algorithim-for-my-sql-file-backups\/","title":{"rendered":"Best compression algorithim for my sql file backups"},"content":{"rendered":"\n<p>SQL files are highly repetitive, I have been using bzip2 (pbzip2) for over 20 years, but is it really a sane choice in 2025 ?<\/p>\n\n\n\n<p>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 &#8220;zstd&#8221; and &#8220;xz (LZMA2)&#8221; and &#8220;brotli&#8221;<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">zstd -19 -T12 myfile.bin (-19 for maximum compression (5 for low) T12 for 12 CPUs), original file is kept by default<br>unzstd myfile.bin.zst<br><br>xz -k -T0 -9 myfile.bin (Auto threads, high compression, -k for keeping original file)<br><\/pre>\n\n\n\n<p>Compressing a 200GB SQL file  on an intel i7-4930K using  8\/12 threads<\/p>\n\n\n\n<p>zstd at compression level 19 (MAX) = 20.63% ( 40 GB)<br>xz at compression level 9 (Max) = 38GB<\/p>\n\n\n\n<p>So, xz at max compression took much longer to finish, but resulted in a 2GBs smaller file&#8230;.<\/p>\n\n\n\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8220;zstd&#8221; and &#8220;xz (LZMA2)&#8221; and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[219],"tags":[],"class_list":["post-6089","post","type-post","status-publish","format-standard","hentry","category-file-compression"],"_links":{"self":[{"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/posts\/6089","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/comments?post=6089"}],"version-history":[{"count":11,"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/posts\/6089\/revisions"}],"predecessor-version":[{"id":6100,"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/posts\/6089\/revisions\/6100"}],"wp:attachment":[{"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/media?parent=6089"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/categories?post=6089"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.voodoo.business\/blog\/wp-json\/wp\/v2\/tags?post=6089"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}