It's a db dump, does that matter a lot? Let's say in the future I would like to achieve the same goal with another type of file?
back
4 comments
> It's a db dump, does that matter a lot?
This is like the only thing that ever matters in compression. I can easily produce 1GB of data that is nearly incompressible next to 1GB of data that can be reduced by a lousy but fast algorithm that just prints '1GB of \x00'. There are statistical tools that can apparently give you an estimate how random your data is, but I know next to nothing of them. A DB dump is probably (but not necessarily) in human-readable SQL so should compress similar to any novel you can download from project Gutenberg. For some special use cases (and of course, most conspicuously, AV media data streams), specialized compression algorithms should be able to make a difference, but I doubt the generic tools we have can be surpassed by much for human-readable text when used with the most aggressive settings (i.e. small size at the cost of time and maybe memory consumption). This is what they were optimized for in the first place. Trying to gzip a PNG image is a fruitless endeavor.
Very interesting, thanks for the explanation!
Yes, the data type matters a lot and it can be quite challenging, especially when dealing with media files such as videos due to existing compression as media codecs are very efficient.
In your case, with the database dump being a text file, I expect the compression results to be quite impressive. However, it's also crucial to consider the similarity of the data within your database. Although I haven't worked with compression in a while, I've experienced notable success in the past by increasing the dictionary size.
Take a look at for compression algos benchmarks: https://www.mattmahoney.net/dc/text.html
Some databases can compress each column differently (https://www.timescale.com/blog/building-columnar-compression...) decreasing overall size. That's not possible in a dump I think.
For smallest possible size you'd select an algorithm that is as specific as possible for your data. Once specific it would have worse impact on another type of file.
It's horses for courses. If the data is random bytes compression is unlikely. Text is easy. The different algorithms have their strengths and weaknesses.
More than that if you want the best results you need to preprocess.
PNG uses a standard compression algorithm but it can subtract rgb pixel values from the pixel above, to the left or some combination, which makes the numbers smaller and more predictable most of the time. If you had a column of numbers that were hourly temperature measurements you should do the same.
If it is like a csv file you are better off compressing a whole column together than compressing a row. if you keep the rows together it will usually compress faster if you sort it.
Almost any simple program which compresses the data a bit based on knowledge of the file will improve the results of a gzip/zstd/bzip2 kind of compressor run after it.
I'd add that when you look into the DB and process by columns I do not doubt that frequently you will be able to come up with a compression scheme that beats generic compression of an SQL dump.
But at what cost?
Data is only successfully compressed when it can be reliably restored. This is not the case when I invent a new compression format that is super efficient but also hard to reverse engineer and only exists on a single or a few machines on the planet. things like gzip are bound to be around for some time, my homebrew format not so much.
A compression scheme that is not widely reconstructable is a fancy way to hit the delete button for that piece of data, only with more costs involved.