Finding out the size of a continuous aggregate

Question

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.

Does this help at all? dba.stackexchange.com/questions/96534/… — Don R
– Don R, Commented Aug 4, 2021 at 14:27

jonatasdp · Accepted Answer · 2021-08-04 14:29:42Z

9

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;

answered Aug 4, 2021 at 14:29

jonatasdp

1,4678 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Konstantinos Gallis · Accepted Answer · 2024-10-11 07:34:20Z

2

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;

answered Oct 11, 2024 at 7:34

Konstantinos Gallis

1,1909 silver badges17 bronze badges

Comments

anka · Accepted Answer · 2022-03-15 06:54:42Z

1

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;

answered Mar 15, 2022 at 6:54

anka

3,8571 gold badge32 silver badges38 bronze badges

Collectives™ on Stack Overflow

Finding out the size of a continuous aggregate

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related