2 ms·

> For example, one recent query planner improvement excludes data more efficiently for relative now()-based queries (e.g., WHERE time >= now()-’1 week’::interva

> For example, one recent query planner improvement excludes data more efficiently for relative now()-based queries (e.g., WHERE time >= now()-’1 week’::interval). This could be done by just calculating the start date in code too. > When hypertables are compressed the amount of data that queries need to read is reduced, leading to dramatic increases in performance of 1000x or more. At my workplace we recently experimented with storing time series data in an array in a postgres row. This gets compressed as a TOAST array, can store thousands of ints in just a few DB pages (aka loading it is about the same as an index scan). We also use Timescale for a different service mind you. I'm sure this format is more efficient than the Timescale format too. In Timescale you would need rows containing (for example) (date, user_id, time_on_site), one row per day. The postgres array format (start_date, user_id, time_on_site_by_date) indexing where 0=start_date, 1=start_date+1 is like 1/3rd the size uncompressed. And yea, even if something is compressed, you still gotta put the uncompressed version in memory somewhere.

djk447 4y ago
Disclosure, I work at Timescale. We are kindred spirits I think! I did this too [0] a while back at a previous company and it actually served as part of the inspiration for our compression work! It's fun, but a bit difficult to query at times. Our compressed columns do also get TOASTed and stored out of line. I'm not sure that it's going to be much more efficient than the Timescale format once it's compressed, we have some pretty good compression algos, but I might be missing something about your case, we generally can achieve close to 10x compression, but right now you can't write directly compressed data, so you would save on the write side I suppose. It is true that you need to put the uncompressed version into memory at some point, but we do try to limit that and in many cases you end up IO limited moreso than memory limited. We're also thinking about doing some work to push processing down towards the compressed data, but that's still in the "glint in our eye" stage, but I think it has a lot of promise. (As a side note, TOAST is still the best acronym around ;) ). [0]: https://www.youtube.com/watch?v=sPoz1OPuRUU https://www.youtube.com/watch?v=sPoz1OPuRUU
- miohtama 4y ago
  If I am running already compressed filesystem like ZFS with zstd, can I disable TOAST and compressed columns altogether somehow? https://dba.stackexchange.com/questions/315063/disable-toast-compression-for-all-columns https://dba.stackexchange.com/questions/315063/disable-toast...

djk447 4y ago

Disclosure, I work at Timescale. We are kindred spirits I think! I did this too [0] a while back at a previous company and it actually served as part of the inspiration for our compression work! It's fun, but a bit difficult to query at times. Our compressed columns do also get TOASTed and stored out of line. I'm not sure that it's going to be much more efficient than the Timescale format once it's compressed, we have some pretty good compression algos, but I might be missing something about your case, we generally can achieve close to 10x compression, but right now you can't write directly compressed data, so you would save on the write side I suppose. It is true that you need to put the uncompressed version into memory at some point, but we do try to limit that and in many cases you end up IO limited moreso than memory limited. We're also thinking about doing some work to push processing down towards the compressed data, but that's still in the "glint in our eye" stage, but I think it has a lot of promise. (As a side note, TOAST is still the best acronym around ;) ). [0]: https://www.youtube.com/watch?v=sPoz1OPuRUU https://www.youtube.com/watch?v=sPoz1OPuRUU

miohtama 4y ago
If I am running already compressed filesystem like ZFS with zstd, can I disable TOAST and compressed columns altogether somehow? https://dba.stackexchange.com/questions/315063/disable-toast-compression-for-all-columns https://dba.stackexchange.com/questions/315063/disable-toast...