4 ms·

I would imagine SQLite would do a lot of random access IO, which would make this difficult even without indexing. But there is a gzip “variant” that we use in

I would imagine SQLite would do a lot of random access IO, which would make this difficult even without indexing. But there is a gzip “variant” that we use in genomics called bgzip that supports this. It’s basically a chunked gzip file (multiple gzip records concatenated together) with an extra flag (in the gzip header) for the uncompressed length of each chunk. Using this information, you can do random IO on the compressed file while only uncompressing the chunks necessary. I’m sure other compression formats have the same support.

masklinn 5y ago
> I would imagine SQLite would do a lot of random access IO, which would make this difficult even without indexing. To individual blobs? Seems unlikely. Posgres automatically compress large blobs, it's part of the TOAST system: > The TOAST management code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than TOAST_TUPLE_TARGET bytes (also normally 2 kB, adjustable) or no more gains can be had. So if a TOAST-able value is larger than TOAST_TUPLE_THRESHOLD it'll get compressed (using pglz by default, from 14.0 LZ4 will finally be supported), and if it's still above TOAST_TUPLE_TARGET it'll get stored out-of-line (otherwise it gets stored inline).
- mbreese 5y ago
  Not the filesystem... to the database file itself. I thought that was what we were talking about. The blobs in the sqlite archive are compressed, but one of the critiques was that the compression was better when you used multiple files to build the compression dictionary (zip vs tarbal). And now I realize that I probably replied to the wrong comment...
- dunham 5y ago
  Interesting. Sqlite moves some data off-page (out of the btree node) if the row is bigger than a certain threshold (the beginning of it is still stored in-page). I wonder if this could be hooked to compress the overflow data.

masklinn 5y ago

> I would imagine SQLite would do a lot of random access IO, which would make this difficult even without indexing. To individual blobs? Seems unlikely. Posgres automatically compress large blobs, it's part of the TOAST system: > The TOAST management code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than TOAST_TUPLE_TARGET bytes (also normally 2 kB, adjustable) or no more gains can be had. So if a TOAST-able value is larger than TOAST_TUPLE_THRESHOLD it'll get compressed (using pglz by default, from 14.0 LZ4 will finally be supported), and if it's still above TOAST_TUPLE_TARGET it'll get stored out-of-line (otherwise it gets stored inline).

mbreese 5y ago
Not the filesystem... to the database file itself. I thought that was what we were talking about. The blobs in the sqlite archive are compressed, but one of the critiques was that the compression was better when you used multiple files to build the compression dictionary (zip vs tarbal). And now I realize that I probably replied to the wrong comment...
dunham 5y ago
Interesting. Sqlite moves some data off-page (out of the btree node) if the row is bigger than a certain threshold (the beginning of it is still stored in-page). I wonder if this could be hooked to compress the overflow data.