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');