0

TimescaleDB materialized view can be created with or without DATA.

Normal Postgres way to check view definition does not have trailing pragmas including DATA.

# \sv wmd

# select pg_get_viewdef('wmd', true)

# select definition from pg_views where viewname = 'wmd2';

TimescaleDb meta table doesn't shed light either:

select materialized_only,  hypertable_schema , hypertable_name, view_schema, view_name  from timescaledb_information.continuous_aggregates;
select materialized_only,  hypertable_schema , hypertable_name, view_schema, view_name  from timescaledb_information.continuous_aggregates;
 materialized_only | hypertable_schema | hypertable_name | view_schema | view_name 
-------------------+-------------------+-----------------+-------------+-----------
 f                 | public            | wm              | public      | wmd
 f                 | public            | wm              | public      | wmd2

i.e. 2 views look indistinguishable, but query performance difference is 20 times.

2 Answers 2

1

Not sure if this is the best way to do it but you could count the number of partitions for each table and deduce that if one has more data then it was likely enabled with WITH DATA.

select count(*) from timescaledb_information.chunks where hypertable_name = 'wmd';
1

Options WITH DATA and WITHOUT DATA mean if the materialized view will be refreshed/populated during the creation (see the doc). After the creation the option doesn't have much sense, thus this information is not stored with the view and cannot be checked after the creation (you might use log to see what statements were issued).

If data were refreshed/materialized in the view or not affects the performance of queries against the view. If data wasn't refreshed, the original hypertable will be queried (and the result joined with data from the view) and this should be visible in the query plan by using EXPLAIN. One reason for this situation, i.e., not having data in the view is described in the question: WITHOUT DATA was used during the creation while the hypertable already contained data. There are also other reasons leading to the situation: no continuous aggregate policy was added or refresh was never called.

I am not sure if there is an easy way to see what data was not populated. Few approaches:

  1. The easiest way to investigate the performance issue due to not-populated data is to EXPLAIN the query and investigate the query plan.
  2. It is also possible to alter the view and allow to query only the materialized data of the view without retrieving data from original query.
  3. It is possible to understand internal structure behind materialized view, i.e., how it is stored in internal hypertable and chunks, and try to find the coverage. Since the internals can change over time, I don't think I know how to do it now.
  4. Also there might be some internal metadata, which stores data about materialized intervals, however I don't find the information about them in the documentation.

I suggest to read [this doc](https://docs.timescale.com/timescaledb/latest/overview/core-concepts/continuous-aggregates/#real-time-aggregates\) about the continuous aggregate concept as I think it explains it quite well.

Conclusion

To my knowledge, it is not possible to see if a view was created with DATA or not, and, in general, imho, it doesn't make much sense, since it is one of several methods to refresh data into the view. The missing data in the view can lead to bad performance due to querying data from original hypertable. I made few suggestions how to investigate it, but there is no easy official way to my knowledge.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.