TimescaleDB 连续聚合与保留/压缩策略实战

初始化与创建 hypertable：CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (

ts timestamptz NOT NULL,

host text NOT NULL,

cpu double precision NOT NULL

);

SELECT create_hypertable('metrics', 'ts', chunk_time_interval => interval '1 day');

创建连续聚合视图：CREATE MATERIALIZED VIEW metrics_cpu_hourly

WITH (timescaledb.continuous) AS

SELECT time_bucket('1 hour', ts) AS bucket,

host,

avg(cpu) AS avg_cpu

FROM metrics

GROUP BY bucket, host;

刷新策略（近 7 天、每小时调度）：SELECT add_continuous_aggregate_policy('metrics_cpu_hourly',

start_offset => interval '7 days',

end_offset => interval '1 hour',

schedule_interval => interval '1 hour');

保留与压缩策略：SELECT add_retention_policy('metrics', INTERVAL '30 days');

ALTER TABLE metrics SET (

timescaledb.compress,

timescaledb.compress_segmentby = 'host'

);

SELECT add_compression_policy('metrics', INTERVAL '7 days');