4 ms·
> 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. Posgr
by 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 agoNot 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 agoInteresting. 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.