TimescaleDB 连续聚合与数据保留策略

TimescaleDB 连续聚合与数据保留策略初始化与超表CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (time TIMESTAMPTZ NOT NULL, key TEXT, value DOUBLE PRECISION);

SELECT create_hypertable('metrics', 'time');

连续聚合CREATE MATERIALIZED VIEW metrics_5m WITH (timescaledb.continuous) AS

SELECT time_bucket('5 minutes', time) AS bucket, key, avg(value) AS avg_value

FROM metrics GROUP BY bucket, key WITH NO DATA;

SELECT add_continuous_aggregate_policy('metrics_5m',

start_offset => INTERVAL '1 day',

end_offset => INTERVAL '5 minutes',

schedule_interval => INTERVAL '5 minutes');

保留与压缩SELECT add_retention_policy('metrics', INTERVAL '90 days');

ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_segmentby = 'key');

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

总结连续聚合与保留/压缩策略可显著降低存储与查询成本，同时保持近端数据的高可用性。