Refresh Continuous Aggregates take forever

TimescaleDB and PostgreSQL
1

Hey all, I’m having an issue where refreshing continuous aggregates take forever and it seems to be in a “stuck” state. There are no other ongoing hanging queries in db except for the refresh continuous aggregates query, as observed from running the below query.

SELECT pid, now() - query_start AS duration, query
    FROM pg_stat_activity
    WHERE state = 'active' AND (now() - query_start) > INTERVAL '10 second'
    ORDER BY duration DESC;
   pid   |    duration     |                                                                  query                                                                   
---------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------
 2367774 | 01:01:01.727752 | CALL refresh_continuous_aggregate('cagg_a_1h', '2024-06-29T00:00:00Z'::timestamp, '2024-06-30T00:00:00Z'::timestamp)
 2367819 | 01:01:01.717308 | CALL refresh_continuous_aggregate('cagg_b_1h', '2024-06-29T00:00:00Z'::timestamp, '2024-06-30T00:00:00Z'::timestamp)

The refreshes are only ran for 1 day worth of data for intervals of 1h.

cagg_a_1h and cagg_b_1h are also hierarchical aggregates and they depend on cagg_a_15m and cagg_b_15m respectively.

Any idea on what could be causing this?

2

Hi @George_Tan1 , can you share more details of what version you’re using, any updates or other relevant information?

it should not stuck. Also, it’s just a single day of data, do you have a lot of data? have you checked any other locks are going on?

3

Hey Jonas, we are running 2.15.2. We found a temp solution
ALTER MATERIALIZED VIEW cagg_a_15m set (timescaledb.materialized_only = true);

This solved the issue, and cagg was refreshed in a few seconds