I've got a couple time series data tables in my Postgres database, which I recently converted to TimescaleDB hypertables. I've got a massive materialized view, but refreshing that takes ages. For that reason I wanted to use TimescaleDB Continuous Aggregates. Unfortunately that gives an error saying:
ERROR: only 1 hypertable is permitted in SELECT query for continuous aggregate
So I was planning on building my own continuous aggregate by simply creating a new table and calculate/append the new records every 10 min or so. A colleague suggested that maybe the timescale guys had good reasons for not permitting a continuous aggregate from multiple hypertables, but I just don't see what it could be.
I created an issue about it on their github page, but for now I haven't gotten any response. So before I start building my own Continuous Aggregate, I thought I'd ask you smart people from stackoverflow.
What could be a reason for the people of TimescaleDB to not allow Continuous Aggregates from multiple hypertables? What challenges would you see in building something like that yourself?
The idea of continuous aggregates is to refresh only current window on hot data and don't touch old data. However, it is not always possible to calculate a current increment to continuous aggregate materialisation in the case of a view query with joins. If the join will require to check old data, it will kill performance.
Saying this, there are certain use cases where it should be possible to support limited join queries, where the limitations might affect, which tables can be joined and what should be join conditions and filters.
I suggest to describe your case in an issue to TimescaleDB, so it can be considered when the feature will be designed.