We are big fans of using SQLite for anything of even moderate complexity where you might otherwise use a file. The advantages are numerous, but sometimes you want to be lean on file storage. [Phiresky] has a great answer to that: the sqlite-zstd extension offers transparent row-level compression for SQLite.
There are other options, of course, but as the post mentions, each of these have some drawbacks. However, by compressing each row of a table, you can retain random access without some of the drawbacks of other methods.
A compressed table has an uncompressed view and an underlying compressed table. The compression dictionary is loaded for each table and cached to improve performance. From the application’s point of view, the uncompressed view is just a normal table and you shouldn’t need any code changes.
You can select how the compression groups data which can help with performance. For example, instead of chunking together a fixed number of rows, you can compress groups of records based on dates or even just have a single dictionary fixed which might be useful for tables that never change.
Speaking of performance, decompression happens on the fly, but compression and dictionary building is done in the background when the database is otherwise idle. Benchmarks show some performance hit, of course, but that’s always the case: you trade speed for space. On the other hand, for random access, it is actually faster to use compressed tables since there is less data to read. Random updates, though, were slower even though compression doesn’t occur at that time.
If you want a quick start to using SQLite, there’s a Linux Fu for that. You can even use versioning with a Git-like system, another advantage over traditional files.
0 Commentaires