Refresh_continuous_aggregate: ERROR: out of memory

TimescaleDB and PostgreSQL
,
1

Hello,

When running `CALL refresh_continuous_aggregate(‘v1_30m’, ‘2025-11-18’, ‘2025-11-19’);` - just one day - my postgresql service reach up 100% CPU and my RAM gets bloated until after ~15 minutes I get this error :

ERROR:  out of memory
DETAIL:  Failed on request of size 656 in memory context "Caller tuples".
CONTEXT:  SQL statement "INSERT INTO _timescaledb_internal._materialized_hypertable_9 SELECT * FROM _timescaledb_internal._partial_view_9 AS I WHERE I.datetime >= $1 AND I.datetime < $2 ;"

Here is my view definition :

CREATE MATERIALIZED VIEW v1_30m
WITH (timescaledb.continuous, timescaledb.materialized_only = false) AS
  SELECT time_bucket (INTERVAL '30 minutes', v1.datetime ) AS datetime,
  MIN ( v1.datetime_insert ) AS datetime_insert,
  MAX ( v1.error_code ) AS error_code,
  round( AVG ( v1.signal_quality ) ) AS signal_quality,
  MIN ( ( v1.geom ) :: TEXT ) AS geom,
  v1.id_sensor,
  v1.id_group,
  v1.id_install,
  bool_or( v1.is_fixed ) AS is_fixed,
  bool_or( v1.is_wifi ) AS is_wifi,
  (
  jsonb_build_object (
  'cnt',
  COUNT ( v1.id_sensor ),
  'pm1',
  round( AVG ( ( ( v1.datas ->> 'pm1' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pm25',
  round( AVG ( ( ( v1.datas ->> 'pm25' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pm10',
  round( AVG ( ( ( v1.datas ->> 'pm10' :: TEXT ) ) :: NUMERIC ), 2 ),
  'nh3',
  round( AVG ( ( ( v1.datas ->> 'nh3' :: TEXT ) ) :: NUMERIC ), 2 ),
  'no2',
  round( AVG ( ( ( v1.datas ->> 'no2' :: TEXT ) ) :: NUMERIC ), 2 ),
  'co',
  round( AVG ( ( ( v1.datas ->> 'co' :: TEXT ) ) :: NUMERIC ), 2 ),
  'nh3_cc',
  round( AVG ( ( ( v1.datas ->> 'nh3_cc' :: TEXT ) ) :: NUMERIC ), 2 ),
  'no2_cc',
  round( AVG ( ( ( v1.datas ->> 'no2_cc' :: TEXT ) ) :: NUMERIC ), 2 ),
  'co_cc',
  round( AVG ( ( ( v1.datas ->> 'co_cc' :: TEXT ) ) :: NUMERIC ), 2 ),
  'so2',
  round( AVG ( ( ( v1.datas ->> 'so2' :: TEXT ) ) :: NUMERIC ), 2 ),
  'o3',
  round( AVG ( ( ( v1.datas ->> 'o3' :: TEXT ) ) :: NUMERIC ), 2 ),
  'bc',
  round( AVG ( ( ( v1.datas ->> 'bc' :: TEXT ) ) :: NUMERIC ), 2 ),
  'h2s',
  round( AVG ( ( ( v1.datas ->> 'h2s' :: TEXT ) ) :: NUMERIC ), 2 ),
  'no',
  round( AVG ( ( ( v1.datas ->> 'no' :: TEXT ) ) :: NUMERIC ), 2 ),
  'nox',
  round( AVG ( ( ( v1.datas ->> 'nox' :: TEXT ) ) :: NUMERIC ), 2 ),
  'c6h6',
  round( AVG ( ( ( v1.datas ->> 'c6h6' :: TEXT ) ) :: NUMERIC ), 2 ),
  'co2',
  round( AVG ( ( ( v1.datas ->> 'co2' :: TEXT ) ) :: NUMERIC ), 2 ),
  'ch2o',
  round( AVG ( ( ( v1.datas ->> 'ch2o' :: TEXT ) ) :: NUMERIC ), 2 ),
  'cov',
  round( AVG ( ( ( v1.datas ->> 'cov' :: TEXT ) ) :: NUMERIC ), 2 ),
  'micro',
  round( AVG ( ( ( v1.datas ->> 'micro' :: TEXT ) ) :: NUMERIC ), 2 ),
  'micro_c',
  round( AVG ( ( ( v1.datas ->> 'micro_c' :: TEXT ) ) :: NUMERIC ), 2 ),
  'temperature',
  round( AVG ( ( ( v1.datas ->> 'temperature' :: TEXT ) ) :: NUMERIC ), 2 ),
  'humidity',
  round( AVG ( ( ( v1.datas ->> 'humidity' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pressure',
  round( AVG ( ( ( v1.datas ->> 'pressure' :: TEXT ) ) :: NUMERIC ), 2 ),
  'temperature_c',
  round( AVG ( ( ( v1.datas ->> 'temperature_c' :: TEXT ) ) :: NUMERIC ), 2 ),
  'humidity_c',
  round( AVG ( ( ( v1.datas ->> 'humidity_c' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pressure_c',
  round( AVG ( ( ( v1.datas ->> 'pressure_c' :: TEXT ) ) :: NUMERIC ), 2 ),
  'cloudiness',
  round( AVG ( ( ( v1.datas ->> 'cloudiness' :: TEXT ) ) :: NUMERIC ), 2 ),
  'wind_dir',
  round( AVG ( ( ( v1.datas ->> 'wind_dir' :: TEXT ) ) :: NUMERIC ), 2 ),
  'wind_speed',
  round( AVG ( ( ( v1.datas ->> 'wind_speed' :: TEXT ) ) :: NUMERIC ), 2 ),
  'temperature_intern',
  round( AVG ( ( ( v1.datas ->> 'temperature_intern' :: TEXT ) ) :: NUMERIC ), 2 ),
  'temperature_extern',
  round( AVG ( ( ( v1.datas ->> 'temperature_extern' :: TEXT ) ) :: NUMERIC ), 2 ),
  'humidity_intern',
  round( AVG ( ( ( v1.datas ->> 'humidity_intern' :: TEXT ) ) :: NUMERIC ), 2 ),
  'humidity_extern',
  round( AVG ( ( ( v1.datas ->> 'humidity_extern' :: TEXT ) ) :: NUMERIC ), 2 ),
  'altitude',
  round( AVG ( ( ( v1.datas ->> 'altitude' :: TEXT ) ) :: NUMERIC ), 2 ),
  'speed_og',
  round( AVG ( ( ( v1.datas ->> 'speed_og' :: TEXT ) ) :: NUMERIC ), 2 ),
  'course_og',
  round( AVG ( ( ( v1.datas ->> 'course_og' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pm1_a',
  round( AVG ( ( ( v1.datas ->> 'pm1_a' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pm25_a',
  round( AVG ( ( ( v1.datas ->> 'pm25_a' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pm10_a',
  round( AVG ( ( ( v1.datas ->> 'pm10_a' :: TEXT ) ) :: NUMERIC ), 2 ),
  'lit03',
  round( AVG ( ( ( v1.datas ->> 'lit03' :: TEXT ) ) :: NUMERIC ), 2 ),
  'lit05',
  round( AVG ( ( ( v1.datas ->> 'lit05' :: TEXT ) ) :: NUMERIC ), 2 ),
  'lit10',
  round( AVG ( ( ( v1.datas ->> 'lit10' :: TEXT ) ) :: NUMERIC ), 2 ),
  'lit25',
  round( AVG ( ( ( v1.datas ->> 'lit25' :: TEXT ) ) :: NUMERIC ), 2 ),
  'lit50',
  round( AVG ( ( ( v1.datas ->> 'lit50' :: TEXT ) ) :: NUMERIC ), 2 ),
  'lit100',
  round( AVG ( ( ( v1.datas ->> 'lit100' :: TEXT ) ) :: NUMERIC ), 2 )
  ) || jsonb_build_object (
  'pm03_part',
  round( AVG ( ( ( v1.datas ->> 'pm03_part' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pm05_part',
  round( AVG ( ( ( v1.datas ->> 'pm05_part' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pm1_part',
  round( AVG ( ( ( v1.datas ->> 'pm1_part' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pm5_part',
  round( AVG ( ( ( v1.datas ->> 'pm5_part' :: TEXT ) ) :: NUMERIC ), 2 ),
  'delta_pressure',
  round( AVG ( ( ( v1.datas ->> 'delta_pressure' :: TEXT ) ) :: NUMERIC ), 2 ),
  'battery_voltage',
  round( AVG ( ( ( v1.datas ->> 'battery_voltage' :: TEXT ) ) :: NUMERIC ), 2 ),
  'sinr',
  round( AVG ( ( ( v1.datas ->> 'sinr' :: TEXT ) ) :: NUMERIC ), 2 ),
  'airflow_speed',
  round( AVG ( ( ( v1.datas ->> 'airflow_speed' :: TEXT ) ) :: NUMERIC ), 2 ),
  'pir',
  MAX ( ( v1.datas ->> 'pir' :: TEXT )  :: NUMERIC ),
  'door',
  MAX ( ( v1.datas ->> 'door' :: TEXT )  :: NUMERIC )
  )
  ) AS datas
FROM
  v1
GROUP BY
  1,
  v1.id_sensor,
  v1.id_install,
  v1.id_group;

I am aware that `jsonb_build_object` is an immutable function, but I have to store my data under a JSONB column, and my CAGG have to reflect the same structure.

`work_mem` is set to 16MB. I tried with 64MB but no change.

The `datetime` column is of type TIMESTAMP, not TIMESTAMPTZ. I couldn’t make the change but from what I can read it should still work.

I recently upgraded to timescaledb 2.23.0, and I didn’t have this issue under Timecaledb 1.7 (yes I come a long way)

2

I tried to do a hierarchical CAGG, one base to do the AVG operation, a second CAGG to perform the json_build_object with the ROUND operations. I also change of ::NUMERIC to ::FLOAT8 to reduce memory usage.

But even with that, simply call refresh_continuous_aggregate(‘v1_30m_base’, ‘2025-12-02’, ‘2025-12-03’); take 6 minutes to operate for one day, CPU goes up to 100% and the RAM gets used up from 10% to 35% progressively, and go back to 0-5% when it’s done. I run timescaledb on a dedicated server with 32GB of RAM, 8 cores of 3.4Ghz. I only have 400k of time seria a day. Could there be a memory management issue ?

Here is the definition of v1_30m_base :

CREATE MATERIALIZED VIEW v1_30m_base
WITH (timescaledb.continuous, timescaledb.materialized_only = true) AS
SELECT
    time_bucket('30 minutes', v1.datetime) AS datetime,
    v1.id_sensor,
    v1.id_install,
    v1.id_group,
    MIN(v1.datetime_insert) AS datetime_insert,
    MAX(v1.error_code) AS error_code,
    round(AVG(v1.signal_quality), 2) AS signal_quality,
    MIN((v1.geom)::text) AS geom,
    bool_or(v1.is_fixed) AS is_fixed,
    bool_or(v1.is_wifi) AS is_wifi,
    COUNT(*) AS cnt,

    SUM ( ( ( v1.datas ->> 'interval' ) ) :: INT8 ) as interval,
    AVG ( ( ( v1.datas ->> 'pm1' ) ) :: FLOAT8 ) as pm1,
    AVG ( ( ( v1.datas ->> 'pm25' ) ) :: FLOAT8 ) as pm25,
    AVG ( ( ( v1.datas ->> 'pm10' ) ) :: FLOAT8 ) as pm10,
    AVG ( ( ( v1.datas ->> 'nh3' ) ) :: FLOAT8 ) as nh3,
    AVG ( ( ( v1.datas ->> 'no2' ) ) :: FLOAT8 ) as no2,
    AVG ( ( ( v1.datas ->> 'co' ) ) :: FLOAT8 ) as co,
    AVG ( ( ( v1.datas ->> 'nh3_cc' ) ) :: FLOAT8 ) as nh3_cc,
    AVG ( ( ( v1.datas ->> 'no2_cc' ) ) :: FLOAT8 ) as no2_cc,
    AVG ( ( ( v1.datas ->> 'co_cc' ) ) :: FLOAT8 ) as co_cc,
    AVG ( ( ( v1.datas ->> 'so2' ) ) :: FLOAT8 ) as so2,
    AVG ( ( ( v1.datas ->> 'o3' ) ) :: FLOAT8 ) as o3,
    AVG ( ( ( v1.datas ->> 'bc' ) ) :: FLOAT8 ) as bc,
    AVG ( ( ( v1.datas ->> 'h2s' ) ) :: FLOAT8 ) as h2s,
    AVG ( ( ( v1.datas ->> 'no' ) ) :: FLOAT8 ) as no,
    AVG ( ( ( v1.datas ->> 'nox' ) ) :: FLOAT8 ) as nox,
    AVG ( ( ( v1.datas ->> 'c6h6' ) ) :: FLOAT8 ) as c6h6,
    AVG ( ( ( v1.datas ->> 'co2' ) ) :: FLOAT8 ) as co2,
    AVG ( ( ( v1.datas ->> 'ch2o' ) ) :: FLOAT8 ) as ch2o,
    AVG ( ( ( v1.datas ->> 'cov' ) ) :: FLOAT8 ) as cov,
    AVG ( ( ( v1.datas ->> 'micro' ) ) :: FLOAT8 ) as micro,
    AVG ( ( ( v1.datas ->> 'micro_c' ) ) :: FLOAT8 ) as micro_c,
    AVG ( ( ( v1.datas ->> 'temperature' ) ) :: FLOAT8 ) as temperature,
    AVG ( ( ( v1.datas ->> 'humidity' ) ) :: FLOAT8 ) as humidity,
    AVG ( ( ( v1.datas ->> 'pressure' ) ) :: FLOAT8 ) as pressure,
    AVG ( ( ( v1.datas ->> 'temperature_c' ) ) :: FLOAT8 ) as temperature_c,
    AVG ( ( ( v1.datas ->> 'humidity_c' ) ) :: FLOAT8 ) as humidity_c,
    AVG ( ( ( v1.datas ->> 'pressure_c' ) ) :: FLOAT8 ) as pressure_c,
    AVG ( ( ( v1.datas ->> 'cloudiness' ) ) :: FLOAT8 ) as cloudiness,
    AVG ( ( ( v1.datas ->> 'wind_dir' ) ) :: FLOAT8 ) as wind_dir,
    AVG ( ( ( v1.datas ->> 'wind_speed' ) ) :: FLOAT8 ) as wind_speed,
    AVG ( ( ( v1.datas ->> 'temperature_intern' ) ) :: FLOAT8 ) as temperature_intern,
    AVG ( ( ( v1.datas ->> 'temperature_extern' ) ) :: FLOAT8 ) as temperature_extern,
    AVG ( ( ( v1.datas ->> 'humidity_intern' ) ) :: FLOAT8 ) as humidity_intern,
    AVG ( ( ( v1.datas ->> 'humidity_extern' ) ) :: FLOAT8 ) as humidity_extern,
    AVG ( ( ( v1.datas ->> 'altitude' ) ) :: FLOAT8 ) as altitude,
    AVG ( ( ( v1.datas ->> 'speed_og' ) ) :: FLOAT8 ) as speed_og,
    AVG ( ( ( v1.datas ->> 'course_og' ) ) :: FLOAT8 ) as course_og,
    AVG ( ( ( v1.datas ->> 'pm1_a' ) ) :: FLOAT8 ) as pm1_a,
    AVG ( ( ( v1.datas ->> 'pm25_a' ) ) :: FLOAT8 ) as pm25_a,
    AVG ( ( ( v1.datas ->> 'pm10_a' ) ) :: FLOAT8 ) as pm10_a,
    AVG ( ( ( v1.datas ->> 'lit03' ) ) :: FLOAT8 ) as lit03,
    AVG ( ( ( v1.datas ->> 'lit05' ) ) :: FLOAT8 ) as lit05,
    AVG ( ( ( v1.datas ->> 'lit10' ) ) :: FLOAT8 ) as lit10,
    AVG ( ( ( v1.datas ->> 'lit25' ) ) :: FLOAT8 ) as lit25,
    AVG ( ( ( v1.datas ->> 'lit50' ) ) :: FLOAT8 ) as lit50,
    AVG ( ( ( v1.datas ->> 'lit100' ) ) :: FLOAT8 ) as lit100,
    AVG ( ( ( v1.datas ->> 'pm03_part' ) ) :: FLOAT8 ) as pm03_part,
    AVG ( ( ( v1.datas ->> 'pm05_part' ) ) :: FLOAT8 ) as pm05_part,
    AVG ( ( ( v1.datas ->> 'pm1_part' ) ) :: FLOAT8 ) as pm1_part,
    AVG ( ( ( v1.datas ->> 'pm5_part' ) ) :: FLOAT8 ) as pm5_part,
    AVG ( ( ( v1.datas ->> 'delta_pressure' ) ) :: FLOAT8 ) as delta_pressure,
    AVG ( ( ( v1.datas ->> 'battery_voltage' ) ) :: FLOAT8 ) as battery_voltage,
    AVG ( ( ( v1.datas ->> 'sinr' ) ) :: FLOAT8 ) as sinr,
    AVG ( ( ( v1.datas ->> 'airflow_speed' ) ) :: FLOAT8 ) as airflow_speed,
    MAX ( ( v1.datas ->> 'pir' )  :: INT2 ) as pir,
    MAX ( ( v1.datas ->> 'door' )  :: INT2 ) as door
FROM v1
GROUP BY 1,2,3,4
WITH NO DATA;
3

For those who ran through the same issue : Dropping the view and recreating it solved the issue.

The issue was not caused by immutable JSON functions or big view body.

From here, you need to be carefull with call refresh_continuous_aggregate by only going resonnable period (2 month in my case). I tried with a period of one year, I got the “Out of memory” message again. Once you met this error once, every call refresh_continuous_aggregate are failing, even if your period is only one day. I had to drop and recreate my view again, and repopulate with multiple call refresh_continuous_aggregate each 2 months of interval.

1 Like