We are big enthusiasts of making use of SQLite for nearly anything of even moderate complexity exactly where you could or else use a file. The positive aspects are various, but from time to time you want to be lean on file storage. [Phiresky] has a good respond to to that: the sqlite-zstd extension provides clear row-stage compression for SQLite.
There are other options, of course, but as the write-up mentions, each individual of these have some downsides. Nevertheless, by compressing each row of a desk, you can retain random access devoid of some of the disadvantages of other strategies.
A compressed table has an uncompressed watch and an fundamental compressed table. The compression dictionary is loaded for every single desk and cached to make improvements to efficiency. From the application’s level of perspective, the uncompressed look at is just a typical table and you shouldn’t have to have any code modifications.
You can pick how the compression groups knowledge which can enable with effectiveness. For example, as an alternative of chunking together a fastened range of rows, you can compress teams of records based mostly on dates or even just have a single dictionary fixed which may well be handy for tables that never adjust.
Speaking of overall performance, decompression occurs on the fly, but compression and dictionary developing is performed in the track record when the database is normally idle. Benchmarks present some functionality strike, of program, but which is generally the situation: you trade velocity for place. On the other hand, for random access, it is truly a lot quicker to use compressed tables considering the fact that there is significantly less facts to browse. Random updates, however, were slower even even though compression doesn’t take place at that time.
If you want a brief start off to employing SQLite, there’s a Linux Fu for that. You can even use versioning with a Git-like process, an additional benefit in excess of standard documents.