TimescaleDB 时序数据库实战

# TimescaleDB 时序数据库实战 ## TimescaleDB 简介 TimescaleDB 是 PostgreSQL 的时序数据扩展，兼容完整的 SQL 语法，同时针对时序数据的写入和查询进行了深度优化。 ## Hypertable 创建 ```sql -- 启用扩展 CREATE EXTENSION IF NOT EXISTS timescaledb; -- 创建普通表 CREATE TABLE sensor_data ( time TIMESTAMPTZ NOT NULL, sensor_id INTEGER NOT NULL, location TEXT NOT NULL, temperature DOUBLE PRECISION, humidity DOUBLE PRECISION, pressure DOUBLE PRECISION ); -- 转换为 Hypertable（自动按时间分区） SELECT create_hypertable('sensor_data', 'time', chunk_time_interval => INTERVAL '1 day', create_default_indexes => TRUE ); -- 添加空间分区（多设备场景） SELECT add_dimension('sensor_data', 'sensor_id', number_partitions => 4); ``` ## 连续聚合（Continuous Aggregates） ```sql -- 创建每小时聚合视图 CREATE MATERIALIZED VIEW sensor_hourly WITH (timescaledb.continuous) AS SELECT time_bucket('1 hour', time) AS bucket, sensor_id, location, avg(temperature) AS avg_temp, min(temperature) AS min_temp, max(temperature) AS max_temp, avg(humidity) AS avg_humidity, count(*) AS sample_count FROM sensor_data GROUP BY bucket, sensor_id, location WITH NO DATA; -- 设置刷新策略（每30分钟刷新最近2小时的数据） SELECT add_continuous_aggregate_policy('sensor_hourly', start_offset => INTERVAL '2 hours', end_offset => INTERVAL '30 minutes', schedule_interval => INTERVAL '30 minutes' ); -- 查询聚合视图 SELECT bucket, location, avg_temp, avg_humidity FROM sensor_hourly WHERE bucket > now() - INTERVAL '7 days' AND location = 'warehouse-A' ORDER BY bucket DESC; ``` ## 压缩与保留策略 ```sql -- 启用压缩 ALTER TABLE sensor_data SET ( timescaledb.compress, timescaledb.compress_segmentby = 'sensor_id, location', timescaledb.compress_orderby = 'time DESC' ); -- 自动压缩7天前的数据 SELECT add_compression_policy('sensor_data', INTERVAL '7 days'); -- 自动删除90天前的数据 SELECT add_retention_policy('sensor_data', INTERVAL '90 days'); -- 查看压缩效果 SELECT pg_size_pretty(before_compression_total_bytes) AS before, pg_size_pretty(after_compression_total_bytes) AS after, round(100 - (after_compression_total_bytes::numeric / before_compression_total_bytes * 100), 1) AS ratio FROM hypertable_compression_stats('sensor_data'); -- 通常压缩比可达 90% 以上 ``` TimescaleDB 非常适合 IoT 监控、运维指标收集等场景。由于完全兼容 PostgreSQL，可以使用 pgAdmin、DBeaver 等常用工具进行管理。