Why is there no real-time aggregation of data?

TimescaleDB and PostgreSQL
1

Scenario: There is a water meter that collects its readings every 15 minutes and writes the data into WATER-METER1
Now it is necessary to perform downsampling on the collected data (taking the maximum value of each water meter every hour).
When data is written, its maximum reading can be immediately seen.

Steps:

--1 Create a standard PostgreSQL table
CREATE TABLE WATER_METER1
(
    TIME       TIMESTAMPTZ      NOT NULL,
    METER_CODE TEXT      NOT NULL,
    DQLJLCC    DOUBLE PRECISION NULL
);

--2 Convert the table to a hypertable.
SELECT CREATE_HYPERTABLE('WATER_METER1','time');

--3 CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW WATER_CONSUME_HOUR
WITH (TIMESCALEDB.CONTINUOUS) AS
SELECT
  TIME_BUCKET('1 hour', "time") AS RECODE_TIME,
  METER_CODE,
  MAX(DQLJLCC) AS MAX_READING
FROM WATER_METER1
GROUP BY RECODE_TIME, METER_CODE;

The data is continuously written to the WATER-METER1 table, while WATER-CONSUME_HOUR is empty
May I ask, what is the reason for this?

2

You need to add a continuous aggregate policy to refresh the materialized view periodically.

Or, you can also control and refresh manually with refresh_continuous_aggregates.

3

I assume that you expected to see real-time aggregation without adding refresh policy, as such info can be found in the docs. Please see that according to real-time-aggregates in Timescale 2.13 and later real time aggregates are DISABLED by default.

1 Like