"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'); 总结连续聚合与保留/压缩策略可显著降低存储与查询成本，同时保持近端数据的高可用性。