3 ms·

I don't want to diss on the effort, I have an honest question: wouldn't this something that would be more appropriate to be done at the filesystem level?

xdfgh1112 4y ago
Yeah but your app doesn't have control over the filesystem. Usually an sqlite db is bundled with an application or created by it. That said, it looks like you could get the same results by doing client side compression and decompression of data yourself, and storing compressed blobs in sqlite. It only compresses text column data, not entire pages.
- Gehinnn 4y ago
  But it uses a shared zstd dictionary among rows! The details are described here: https://phiresky.github.io/blog/2022/sqlite-zstd/ https://phiresky.github.io/blog/2022/sqlite-zstd/
- d1l 4y ago
  https://www.sqlite.org/vfs.html https://www.sqlite.org/vfs.html
fnord123 4y ago
Great idea. zfs can compress data on disk. But it's also useful to keep the data compressed in memory so you can fit more data in memory. There's a lot of cpu/memory tradeoffs so it's worth having the options to do it on the data. It's also worth noting that compression on the application layer works way better on column stores than row stores.
- ciupicri 4y ago
  > Btrfs also supports transparent file compression. There are three algorithms available: ZLIB, LZO and ZSTD (since v4.14). Basically, compression is on a file by file basis. You can have a single btrfs mount point that has some files that are uncompressed, some that are compressed with LZO, some with ZLIB, for instance (though you may not want it that way, it is supported). https://btrfs.wiki.kernel.org/index.php/Compression https://btrfs.wiki.kernel.org/index.php/Compression
Someone 4y ago
No. If you did that, SQLite would have to (logically) decompress about your entire database on every query. For example, how would it read the database schema to determine whether “select foo from bar” is valid sql for the database? If it figures that out, how would it figure out there’s an appropriate index to use? How would it read that index? Databases do a lot of seeking into database files and typically only read a small part of them. That’s not a scenario that’s a good fit with whole-file compression. Compressing individual pages is a bit better, but I think this can still be better. Your file system probably would cache the decompressed database file, but that would use memory, lots of it if your database is large.
- formerly_proven 4y ago
  FS-level compression is usually limited to compressing relatively small blocks (think ~dozens of sectors), they don't normally compress a large file as a single solid block.
  - Someone 4y ago
    Yes, but still, the file system would do more decompression than needed, and that will cost performance (CPU and caching)
antman 4y ago
With this you can selectively compress per column
hexmiles 4y ago
It depends on the data and application, I use a similar approach for compressing data that go in a couple of database/store. Having control on when and how to compress data can significantly improve performance, also, it sometimes makes sense to keep compress data even in memory since it can allow you to make better use of it. Also keep in mind that often you can't control the filesystem. One use case for which I am working right now is that I have is storing highly repetitive data on a very slow flash storage, (that technically support compression, but it is slow and with not great compression ratio), I can significantly improve performance. edit: grammar
phiresky 4y ago
It would be interesting to try something like this on the file system level, yes. Basically you could heuristically find interesting files, then train a compression dictionary for e.g. each file or each directory. Then you'd compress 32kB blocks of each file with the corresponding dictionary. Note that this would be pretty different from how existing file system compression works (by splitting everything into blocks of e.g. 128kB and just compressing those normally). I think it would be hard to find heuristics that work well for this method. The result would be pretty different than what I'm doing here with different tradeoffs. sqlite-zstd is able to use application-level information of which data is similar and will compress well together. You couldn't do that within the filesystem. It also works "in cooperation" with the file format of the database instead of against it - e.g. you probably wouldn't want to compress the inner B-tree nodes. On the other hand, it would then work for any file format not just SQLite databases. E.g. a huge folder of small json files
marginalia_nu 4y ago
Context-aware compression can be a lot of faster than blind filesystem level compression. Some data, like plain text, JSON, or XML, compresses extremely well. You're almost certainly looking at compression rates of up to 90%. Dense binary formats, especially formats that are already compressed like most image formats won't compress barely at all (sometimes they can even get bigger), and attempting to compress them is an enormous waste of processing power.
- JulianWasTaken 4y ago
  Continuing the hypothetical without really knowing about today's filesystems, such a system could presumably calculate a moving per page, fd or process- compression factor and turn it down or up if it started seeing lots of incompressible data?
  - marginalia_nu 4y ago
    For a columnar database, this would probably work fairly well as it would put data of similar compressability adjacent to more of its kind, but more row-based databases where various types of data is intermingled would probably not benefit nearly as much.
- zimpenfish 4y ago
  Tried this on my twitter database's `json` column (ie the JSON response from the API fetching a singular tweet) with a `dict_chooser` of '%Y-%W' to bundle weeks together and it took 985k rows down from 6.6G to 1.5G. Not as good as compressing the whole thing obviously but really handy for a queryable backup.
- cmurf 4y ago
  At least in btrfs there's compress vs compress-force mount options. compress uses a cheap estimator of compressibility, whereas compress-force always passes data through the compression algorithm. ZSTD has its own estimator to avoid doing unnecessary work, but I haven't found a comparison of these estimators (which is cheaper). compress results in max extent size of 128K for compressed extents, and 128M for uncompressed data. Whereas compress-force results in 512K max extent size for uncompressed data. While there's a cost to tracking more extents, it might be less than the cost of frequent modification of large extents, particularly if snapshots are used. Of course there's no one answer, it'd be workload and hardware dependant. Hard drives have significant latency impact from non-contiguous extents. NVMe much less impact.
ajconway 4y ago
This kind of solution should work fine event if the database is encrypted.
gred 4y ago
> wouldn't this something that would be more appropriate to be done at the filesystem level? Sometimes? You've gotten a ton of replies digging into the "why" and "when", and I only wanted to add that your question brought to mind this quote from the official SQLite website: > "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()" https://www.sqlite.org/about.html https://www.sqlite.org/about.html

xdfgh1112 4y ago

Yeah but your app doesn't have control over the filesystem. Usually an sqlite db is bundled with an application or created by it. That said, it looks like you could get the same results by doing client side compression and decompression of data yourself, and storing compressed blobs in sqlite. It only compresses text column data, not entire pages.

Gehinnn 4y ago
But it uses a shared zstd dictionary among rows! The details are described here: https://phiresky.github.io/blog/2022/sqlite-zstd/ https://phiresky.github.io/blog/2022/sqlite-zstd/
d1l 4y ago
https://www.sqlite.org/vfs.html https://www.sqlite.org/vfs.html

fnord123 4y ago

Great idea. zfs can compress data on disk. But it's also useful to keep the data compressed in memory so you can fit more data in memory. There's a lot of cpu/memory tradeoffs so it's worth having the options to do it on the data. It's also worth noting that compression on the application layer works way better on column stores than row stores.

ciupicri 4y ago
> Btrfs also supports transparent file compression. There are three algorithms available: ZLIB, LZO and ZSTD (since v4.14). Basically, compression is on a file by file basis. You can have a single btrfs mount point that has some files that are uncompressed, some that are compressed with LZO, some with ZLIB, for instance (though you may not want it that way, it is supported). https://btrfs.wiki.kernel.org/index.php/Compression https://btrfs.wiki.kernel.org/index.php/Compression

Someone 4y ago

No. If you did that, SQLite would have to (logically) decompress about your entire database on every query. For example, how would it read the database schema to determine whether “select foo from bar” is valid sql for the database? If it figures that out, how would it figure out there’s an appropriate index to use? How would it read that index? Databases do a lot of seeking into database files and typically only read a small part of them. That’s not a scenario that’s a good fit with whole-file compression. Compressing individual pages is a bit better, but I think this can still be better. Your file system probably would cache the decompressed database file, but that would use memory, lots of it if your database is large.

formerly_proven 4y ago
FS-level compression is usually limited to compressing relatively small blocks (think ~dozens of sectors), they don't normally compress a large file as a single solid block.
- Someone 4y ago
  Yes, but still, the file system would do more decompression than needed, and that will cost performance (CPU and caching)

antman 4y ago

With this you can selectively compress per column

hexmiles 4y ago

It depends on the data and application, I use a similar approach for compressing data that go in a couple of database/store. Having control on when and how to compress data can significantly improve performance, also, it sometimes makes sense to keep compress data even in memory since it can allow you to make better use of it. Also keep in mind that often you can't control the filesystem. One use case for which I am working right now is that I have is storing highly repetitive data on a very slow flash storage, (that technically support compression, but it is slow and with not great compression ratio), I can significantly improve performance. edit: grammar

phiresky 4y ago

It would be interesting to try something like this on the file system level, yes. Basically you could heuristically find interesting files, then train a compression dictionary for e.g. each file or each directory. Then you'd compress 32kB blocks of each file with the corresponding dictionary. Note that this would be pretty different from how existing file system compression works (by splitting everything into blocks of e.g. 128kB and just compressing those normally). I think it would be hard to find heuristics that work well for this method. The result would be pretty different than what I'm doing here with different tradeoffs. sqlite-zstd is able to use application-level information of which data is similar and will compress well together. You couldn't do that within the filesystem. It also works "in cooperation" with the file format of the database instead of against it - e.g. you probably wouldn't want to compress the inner B-tree nodes. On the other hand, it would then work for any file format not just SQLite databases. E.g. a huge folder of small json files

marginalia_nu 4y ago

Context-aware compression can be a lot of faster than blind filesystem level compression. Some data, like plain text, JSON, or XML, compresses extremely well. You're almost certainly looking at compression rates of up to 90%. Dense binary formats, especially formats that are already compressed like most image formats won't compress barely at all (sometimes they can even get bigger), and attempting to compress them is an enormous waste of processing power.

JulianWasTaken 4y ago
Continuing the hypothetical without really knowing about today's filesystems, such a system could presumably calculate a moving per page, fd or process- compression factor and turn it down or up if it started seeing lots of incompressible data?
- marginalia_nu 4y ago
  For a columnar database, this would probably work fairly well as it would put data of similar compressability adjacent to more of its kind, but more row-based databases where various types of data is intermingled would probably not benefit nearly as much.
zimpenfish 4y ago
Tried this on my twitter database's `json` column (ie the JSON response from the API fetching a singular tweet) with a `dict_chooser` of '%Y-%W' to bundle weeks together and it took 985k rows down from 6.6G to 1.5G. Not as good as compressing the whole thing obviously but really handy for a queryable backup.
cmurf 4y ago
At least in btrfs there's compress vs compress-force mount options. compress uses a cheap estimator of compressibility, whereas compress-force always passes data through the compression algorithm. ZSTD has its own estimator to avoid doing unnecessary work, but I haven't found a comparison of these estimators (which is cheaper). compress results in max extent size of 128K for compressed extents, and 128M for uncompressed data. Whereas compress-force results in 512K max extent size for uncompressed data. While there's a cost to tracking more extents, it might be less than the cost of frequent modification of large extents, particularly if snapshots are used. Of course there's no one answer, it'd be workload and hardware dependant. Hard drives have significant latency impact from non-contiguous extents. NVMe much less impact.

ajconway 4y ago

This kind of solution should work fine event if the database is encrypted.

gred 4y ago

> wouldn't this something that would be more appropriate to be done at the filesystem level? Sometimes? You've gotten a ton of replies digging into the "why" and "when", and I only wanted to add that your question brought to mind this quote from the official SQLite website: > "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()" https://www.sqlite.org/about.html https://www.sqlite.org/about.html