CODEWITHSUNDEEP
postgresqlmaterialized-viewstimescaledbcontinuous-aggregates
Nicole Staline
Nicole Staline

Reputation: 671

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.

Upvotes: 4

Views: 1088

Answers (3)

Konstantinos Gallis
Konstantinos Gallis

Reputation: 1140

The following query displays the total size in a kb, mb format, which is more insightful:

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

Upvotes: 1

anka
anka

Reputation: 3857

Another approach would be the following SQL query

SELECT pg_size_pretty(SUM(total_bytes)), aggs.view_name  
FROM "_timescaledb_internal".hypertable_chunk_local_size hcls, timescaledb_information.continuous_aggregates aggs
WHERE hcls.table_name = aggs.materialization_hypertable_name
GROUP BY aggs.view_name;

Upvotes: 1

jonatasdp
jonatasdp

Reputation: 1412

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;

Upvotes: 9

Related Questions