优化TimescaleDB分层连续聚合的方案咨询

原始表结构 #

现有如下超表结构：

CREATE TABLE IF aqs.measurement
(
    ts timestamp with time zone NOT NULL DEFAULT now(),
    value real,
    measurement_pt_id integer,
    tag_id integer NOT NULL,
    sensor_id integer NOT NULL,
    CONSTRAINT measurement_raw_pkey PRIMARY KEY (ts, sensor_id, tag_id),
    CONSTRAINT fk_measurement_pt_id FOREIGN KEY (measurement_pt_id)
        REFERENCES aqs.measurement_pt (id) MATCH SIMPLE,
    CONSTRAINT fk_sensor_id FOREIGN KEY (sensor_id)
        REFERENCES aqs.sensor (id) MATCH SIMPLE,
    CONSTRAINT fk_tag_name_id FOREIGN KEY (tag_id)
        REFERENCES aqs.tag_name (id) MATCH SIMPLE
)

业务场景说明 #

该超表存储大量传感器数据，部分传感器可采集多类物理参数，每个参数由sensor_id与tag_id的组合唯一标识，多数传感器每分钟采样一次。查询长周期（数周以上）数据时耗时极长，为满足交互式图表的数据探索需求，需对数据进行下采样。

现有连续聚合视图方案 #

为加速长周期数据的绘图查询，已创建两个分层连续聚合视图，用于提供下采样数据及统计指标：

小时级聚合视图 #

CREATE MATERIALIZED VIEW val_hourly_agg
WITH (timescaledb.continuous) AS
SELECT
  time_bucket(INTERVAL '1 hour', "ts") AS ts_hour,
  sensor_id, measurement_pt_id, tag_id,
  avg(aqs.measurement.value)::real AS mean,
  max(aqs.measurement.value) AS max_val,
  min(aqs.measurement.value) AS min_val,
  stddev(stats_agg(aqs.measurement.value))::real AS std_dev,
  approx_percentile(0.1, percentile_agg(aqs.measurement.value))::real as p10,
  approx_percentile(0.9, percentile_agg(aqs.measurement.value))::real as p90,
  percentile_agg(aqs.measurement.value) as pct_agg,
  stats_agg(aqs.measurement.value) AS stats_agg
FROM aqs.measurement
GROUP BY ts_hour, sensor_id, measurement_pt_id, tag_id;

日级聚合视图 #

CREATE MATERIALIZED VIEW val_daily_agg
WITH (timescaledb.continuous) AS
SELECT
  time_bucket(INTERVAL '1 day', "ts_hour") AS ts_day,
  sensor_id, measurement_pt_id, tag_id,
  average(rollup(stats_agg))::real AS mean,
  max(max_val) AS max_val,
  min(min_val) AS min_val,
  stddev(rollup(stats_agg))::real AS std_dev,
  approx_percentile(0.1, rollup(pct_agg))::real as p10,
  approx_percentile(0.9, rollup(pct_agg))::real as p90
FROM val_hourly_agg
GROUP BY ts_day, sensor_id, measurement_pt_id, tag_id;

需求与疑问 #

由于对TimescaleDB经验不足，认为当前方案仍有较大优化空间，恳请提供优化建议；同时不确定数据分组逻辑是否正确，虽经初步测试结果合理，但仍需验证，欢迎提出意见。

内容的提问来源于stack exchange，提问作者JanV