search

timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
17.9k stars 882 forks source link

Enable compression on materialized hyper table behind the view #1765

Open aqureshi-ssi opened 4 years ago

aqureshi-ssi commented 4 years ago

I want to compress the old chunk for the continuous aggregates view but when I try to enable the compression on its respective hyper table it give me following error:

ALTER TABLE _timescaledb_internal._materialized_hypertable_6 SET ( timescaledb.compress, timescaledb.compress_segmentby = 'measuringpointid' );

ERROR: operation not supported on materialization tables SQL state: 0A000

ssube commented 4 years ago

Being able to compress the data underlying an aggregate would be very helpful for me as well, especially with the 1.6 features that allow aggregates to outlive their source data. I'd like to keep aggregates for at least a year, and with compression (seeing ~98% typical, 2 bytes/sample amortized), a 5 minute aggregate should come out to about 52kb/timeseries/year.

schultztom commented 4 years ago

This would be extremely helpful, as we compress our "main" table within an hour. But the aggregate remains uncompressed, so we have to drop data from the aggregate to be able to run this on an office machine.

ddorian commented 4 years ago

@schultztom I think you can create yet another hypertable and periodically move data from the aggregate table to the new one.