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
16.89k stars 853 forks source link

Support continuous aggregates from multiple hypertables? #3314

Open kramer65 opened 3 years ago

kramer65 commented 3 years ago

We've got a materialized view which joins multiple hypertables. Refreshing that view takes a lot of time, so I wanted to convert it to a continuous aggregate, but got this:

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 you guys had good reasons for not permitting a continuous aggregate from multiple hypertables.

So my question; why does timescale not allow for continuous aggregates from multiple hypertables?

Fockaert commented 3 years ago

This would really help, we just need a simple join to enrich with additional metadata. Could we ignore this exception maybe with a configuration?

kramer65 commented 2 years ago

@mkindahl - I was wondering why your commits referenced this issue. Are you guys working on support for multiple hypertables in continuous aggregates?

mkindahl commented 2 years ago

@kramer65 There are architectural reasons to why we cannot support multiple hypertables in a query, but we are considering ways to either work around it or re-design it. The reference from the release is a mistake by GitHub: we added a pull request with the same number as this issue and GitHub mistook it for a issue reference.

kramer65 commented 2 years ago

@mkindahl - Thanks for your response. I understand it's hard to give a timeline, but how big would you say is the chance that this will be implemented in 2022?

I'm currently in the process of designing the basis for a new data platform. A big part of it is the ingress, for which we're going to use timescale. However the part in which we're going to make aggregations on a continuous basis we need to find a solution. If timescale would implement this within half a year we'll probably use that. If it takes longer we need to decide whether we're going find something else or build something homegrown.

clarkbw commented 2 years ago

we added a pull request with the same number as this issue and GitHub mistook it for a issue reference.

Actually the PR the release notes should reference is #3341 but instead they reference this #3314. PRs and Issues are the same table in GitHub so they can never have the same number within a repo.

mkindahl commented 2 years ago

we added a pull request with the same number as this issue and GitHub mistook it for a issue reference.

Actually the PR the release notes should reference is #3341 but instead they reference this #3314. PRs and Issues are the same table in GitHub so they can never have the same number within a repo.

Aha, that explains it! Thanks for explaining how GitHub implements the references, I was wondering about this.

xLarry commented 2 years ago

I think ~the same~ a similar (thanks @phemmer) feature has already been requested in #1446. @phemmer posted a some kind of workaround in the most recent comment within that issue, but I haven't looked into it yet. Just wanted to let you know.

phemmer commented 2 years ago

I don't think these issues are the same. This issue is about multiple hypertables. #1446 is about multiple tables (e.g. a lookup table). However the workaround I provided does work for both.

mfreed commented 1 year ago

I know it might not be quite what's asked here, but wanted to share that the team has been working on supporting continuous aggregates that support JOINing a hypertable against a standard PG table. Hope to release this coming quarter (Q1 2023).

=> https://github.com/timescale/timescaledb/pull/4874