Segment materialized view

TimescaleDB and PostgreSQL
,
1

Hi everyone,

I have a consumption table that contains 10-minute interval consumption data from various IoT systems. Recently, I created a materialized view that adds additional information such as the IoT system brand, geographic region where the IoT component was located, etc.

I have a few questions:

  • Is it possible to create a hypertable directly from my materialized view and segment it by brand and world region, for example?

  • Or do I first need to create a hypertable from my consumption table and then create the materialized view? If so, will I still be able to segment by the new columns I added in the materialized view?

Thanks in advance for your help!

1 Like
2

That’s a great question @remy,

You can do it for sure. Remember that all materialized views are in the end a hypertable :nerd_face:

Use a set of !Hierarchical Continuous Aggregates.

3

Thank you for your answer.

I have another question regarding aggregation.

I’m working on a dashboard connected to a materialized view in TimescaleDB. The dashboard displays monthly averaged data, with several dynamic filters available (such as brand, region, etc.). I have around 6 possible filters, which can be combined using WHERE clauses.

The issue I’m facing is that, since these filters can be combined in many different ways, it’s difficult to pre-aggregate the data by month. As a result, I’m forced to calculate the averages “on the fly” from the raw data, which has a 10-minute resolution. This leads to significant performance slowdowns when querying the dashboard.

I’m wondering if there is an approach or a feature in TimescaleDB that could help optimize these dynamic aggregations while accounting for the filters. Is there a way to efficiently pre-aggregate data despite the possible filter combinations, or any other method to improve the performance of such queries?

Thank you

4

That’s a great question @remy ! We don’t have anything in place for such cases but I’d consider the continuous aggregates per filter as they’re only 6.

You can also think about building extra pre-aggregated data using FILTER on your components:

SELECT
 AVG(i) AS overall_avg,
  AVG(i) FILTER (WHERE i < 5) AS avg_less_than_five
FROM generate_series(1,10) AS s(i);

If you have a limited subset of frequent queries, it may worth to just materialize them as combinations, and you can pick the combination you want.