Hacker News Reader

hm but can't you do the same thing (gzipping) with the db itself?

Edit. Regardless of if you maybe even meant that, I made a small test too: A 42 MB db zipped is 8.8M, but its sql dump zipped is 4.9M, so almost half the size. Pretty good already. If you don't just `.dump` but actually output the tables in a consistently sorted manner, the size might go down even more and will enable very efficient delta-ing. Questionable value to effort ratio though...

View on HN

re: gzipping the db itself - yep, I tried and saw the same effect as you did: about half a size. I think it's because of indexes (I once played with sqlite3_analyzer[1] and on some databases it showed about half of disk space was used by indexes) and unused pages (all the space which can be vacuum'ed - again, size of some databases can be decreased almost twice by vacuum'ing).

[1]: https://www.sqlite.org/sqlanalyze.html

On the other side, indeed, text dump is likely not the most space-efficient way of storing raw DB data (compared to some binary one), so I wouldn't be surprised to find databases for which gzipped sql dump is bigger than (gzipped) database itself. I would even say that I'm surprised that it's not true for most databases :)

Reply on news.ycombinator.com