在 TimescaleDB…

当使用 Timescale/TimescaleDB/TigerData 创建插入密集型（时序、IoT、指标、事件、日志、交易）表时，按步骤配置 hypertable、列存储压缩、保留策略与连续聚合。

适用场景

解决思路

1. 创建 hypertable：使用 CREATE TABLE ... WITH (tsdb.hypertable, tsdb.partition_column='timestamp', tsdb.enable_columnstore=true, tsdb.segmentby='entity_id', tsdb.orderby='timestamp DESC', tsdb.sparse_index='minmax(value_1),minmax(value_2),minmax(value_3)')；若表含向量列（pgvector）则禁用 columnstore。
2. 选择分区列：必须是时间类型（TIMESTAMP/TIMESTAMPTZ/DATE）或具有良好时序/顺序分布的整数（INT/BIGINT）；常用 timestamp、event_time、created_at、id；避免 updated_at（除非它是主要查询维度）；ingested_at 仅在它是主要查询维度时使用。
3. 选择 segment_by 列：优先单列，多列仅在高度相关且行密度足够时使用；要求常用于 WHERE 过滤、每个 chunk 中每个值 >100 行；例如 IoT 用 device_id、金融用 symbol、指标用 service_name、分析用 user_id 或 session_id；避免时间戳、唯一 ID、低密度列。
4. 选择 order_by 列：最常用 timestamp DESC；低密度列（<100 行/chunk）前置到 order_by，如 order_by='metric_name, timestamp DESC'；也可用 sequence_id DESC 或 timestamp DESC, event_order DESC；避免随机列、相邻行高方差列、与 segment_by 无关的列。
5. 配置压缩稀疏索引 sparse_index：使用 minmax 类型对数值/时间列做范围查询过滤，如 minmax(value_1),minmax(value_2)；用于异常检测（temperature > 90）或与 segmentby/orderby 高度相关的列；绝不为 segmentby 或 orderby 中的列建索引（orderby 列自动有 minmax）；可用 ALTER TABLE table_name SET (timescaledb.sparse_index = 'minmax(value_1),minmax(value_2)') 配置（v2.22.0 起显式配置）。
6. 设置 chunk 时间间隔（可选）：默认 7 天；高频用 1 小时–1 天，中频用 1 天–1 周，低频用 1 周–1 月；用 SELECT set_chunk_time_interval('your_table_name', INTERVAL '1 day')；检验标准是最近 chunk 索引应小于 RAM 的 25%。
7. 创建索引与主键：常用复合索引如 CREATE INDEX idx_entity_timestamp ON your_table_name (entity_id, timestamp DESC)；只创建真正会用到的索引；主键/唯一约束必须包含分区列，可选 (entity_id, timestamp) 复合主键、单列主键（仅当它是分区列）或不设主键。
8. 配置压缩策略（可选）：若在 Step 1 使用 tsdb.enable_columnstore=true，TimescaleDB 2.23 起会自动创建 after => INTERVAL '7 days' 的 columnstore 策略；如需自定义 after 间隔，先 CALL remove_columnstore_policy('your_table_name')，再 CALL add_columnstore_policy('your_table_name', after => INTERVAL '1 day')；after 应设为数据基本不可变且不再需要 B-tree 索引的时间点。
9. 配置保留策略：不要猜测，询问用户或注释掉；示例 SELECT add_retention_policy('your_table_name', INTERVAL '365 days')。
10. 创建连续聚合：短期（分钟/小时）用于高频数据实时仪表盘，如 time_bucket(INTERVAL '1 hour', timestamp) 聚合 COUNT/AVG/MIN/MAX/SUM；长期（天/周/月）用于报表分析，如 time_bucket(INTERVAL '1 day', timestamp) 并加入 PERCENTILE_CONT 计算中位数和 p95。
11. 配置聚合刷新策略：start_offset 通常省略（刷新全部），若启用原始数据保留策略则设为等于保留期；end_offset 设为超出活跃更新窗口（如数据 10 分钟内到达则设 15 分钟），未知时用 time_bucket 大小但不小于 5 分钟；schedule_interval 设为与 end_offset 相同但不超过 1 小时；示例：add_continuous_aggregate_policy('your_table_hourly', start_offset => NULL, end_offset => INTERVAL '15 minutes', schedule_interval => INTERVAL '15 minutes')；若不需要刷新旧数据可设 start_offset（如 INTERVAL '7 days'），但必须小于原始数据保留策略。
12. 配置实时聚合（可选）：ALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = false) 启用（查询成本更高但结果最新）；SET (timescaledb.materialized_only = true) 禁用（性能更好但结果可能陈旧）；v2.13+ 默认禁用；细粒度聚合比粗粒度更适合启用。
13. 压缩连续聚合：规则是 segment_by = 除 time_bucket 外的所有 GROUP BY 列，order_by = time_bucket DESC；如 ALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.enable_columnstore, timescaledb.segmentby = 'entity_id, category', timescaledb.orderby = 'bucket DESC') 并 CALL add_columnstore_policy('your_table_hourly', after => INTERVAL '3 days')；daily 聚合用 after => INTERVAL '7 days'。
14. 配置聚合保留策略：聚合通常比原始数据保留更久；不要猜测，询问用户或必须注释掉；示例 SELECT add_retention_policy('your_table_hourly', INTERVAL '2 years') 和 SELECT add_retention_policy('your_table_daily', INTERVAL '5 years')。
15. 为连续聚合创建性能索引：分析常用查询的 WHERE 子句，创建匹配过滤列加时间排序的索引，模式为 (filter_column, bucket DESC)；如 CREATE INDEX idx_hourly_entity_bucket ON your_table_hourly (entity_id, bucket DESC)；多列过滤用复合索引 (entity_id, category, bucket DESC)；只创建真正会用到的索引。
16. 空间分区（不推荐）：仅在始终按空间分区列过滤、有专家知识且经过大量基准测试时使用；强烈优先仅按时间分区。
17. 验证配置：查询 timescaledb_information.hypertables 检查 hypertable；用 hypertable_compression_stats('your_table_name') 检查压缩；查询 timescaledb_information.continuous_aggregates 检查聚合；查询 timescaledb_information.jobs 检查策略；查询 timescaledb_information.chunks 监控 chunk 信息。
18. 遵循性能指南：最近 chunk 索引应小于 RAM 的 25%；正确 columnstore 配置预期 90%+（10x）压缩；历史查询和仪表盘用连续聚合；自托管运行 timescaledb-tune（云上自动配置）。
19. 遵循 schema 最佳实践：用 TIMESTAMPTZ 而非 timestamp；时间戳用 >= 和 < 而非 BETWEEN；用带约束的 TEXT 而非 char(n)/varchar(n)；用 snake_case 而非 CamelCase；用 BIGINT GENERATED ALWAYS AS IDENTITY 而非 SERIAL；ID 默认用 BIGINT；默认用 DOUBLE PRECISION；用 NUMERIC 而非 MONEY；用 NOT EXISTS 而非 NOT IN；用 time_bucket() 或 date_trunc() 而非 timestamp(0) 截断。
20. 使用新 API 替代废弃 API：timescaledb.compress → timescaledb.enable_columnstore；timescaledb.compress_segmentby → timescaledb.segmentby；timescaledb.compress_orderby → timescaledb.orderby；add_compression_policy() → add_columnstore_policy()；remove_compression_policy() → remove_columnstore_policy()；compress_chunk() → convert_to_columnstore()（用 CALL）；decompress_chunk() → convert_to_rowstore()（用 CALL）；压缩统计用函数 hypertable_compression_stats('table_name') 和 chunk_compression_stats(...) 而非视图。
21. 手动压缩示例：CALL convert_to_columnstore('_timescaledb_internal._hyper_7_1_chunk')；用 SELECT number_compressed_chunks, pg_size_pretty(before_compression_total_bytes), pg_size_pretty(after_compression_total_bytes), ROUND(100.0 * (1 - after_compression_total_bytes::numeric / NULLIF(before_compression_total_bytes, 0)), 1) FROM hypertable_compression_stats('your_table_name') 检查压缩率。
22. 向用户询问：存储什么类型的数据、预期如何使用数据、会运行哪些查询、数据保留多久、列类型（若不明确）。

适用边界 · 注意事项

• 未安装 PostgreSQL 15+ 与 TimescaleDB 扩展时无法使用。
• 表含 pgvector 向量列时应禁用 columnstore（向量列索引与 columnstore 不兼容）。
• 不要猜测保留策略和聚合保留策略，未知时必须询问用户或注释掉。
• 不推荐空间分区，除非始终按空间分区列过滤且有专家知识和大量基准测试。