postgresqlmaterialized-viewstimescaledbcontinuous-aggregates

Finding out the size of a continuous aggregate

Have hypertable table with a couple million rows. I'm able to select the size of this just fine using the following:

SELECT pg_size_pretty( pg_total_relation_size('towns') );

I also have a continuous aggregate for that hypertable:

WITH (timescaledb.continuous, timescaledb.materialized_only=true) AS
SELECT time_bucket(INTERVAL '1 minute', timestamp) AS bucket,
 /* random query */
FROM towns
GROUP BY bucket, town
WITH NO DATA;

I've refreshed the view and the data is showing as expected. However, I cannot seem to figure out how much space this new view is taking up.

SELECT pg_size_pretty( pg_total_relation_size('towns_income') ); returns 0 bytes which I know isn't correct. I thought that maybe the total_relation_size for towns would increase, but that also seems the same. Am I missing something? I've tried hypertable_size as well with no success as mv isn't technically a hypertable.

Solution

  • The following SQL can help :)

     SELECT view_name, hypertable_size(format('%I.%I', materialization_hypertable_schema , materialization_hypertable_name )::regclass)
  FROM timescaledb_information.continuous_aggregates;