setup-timescaledb-hypertables

TimescaleDB 完整设置

适用于数据以插入为主、很少更改的场景：

• 时序数据（传感器、指标、系统监控）
• 事件日志（用户事件、审计跟踪、应用程序日志）
• 交易记录（订单、付款、金融交易）
• 顺序数据（具有自动递增 ID 和时间戳的记录）
• 仅追加数据集（不可变记录、历史数据）

步骤 1：创建超表

CREATE TABLE your_table_name (
    timestamp TIMESTAMPTZ NOT NULL,
    entity_id TEXT NOT NULL,          -- device_id, user_id, symbol, etc.
    category TEXT,                    -- sensor_type, event_type, asset_class, etc.
    value_1 DOUBLE PRECISION,         -- price, temperature, latency, etc.
    value_2 DOUBLE PRECISION,         -- volume, humidity, throughput, etc.
    value_3 INTEGER,                  -- count, status, level, etc.
    metadata JSONB                    -- flexible additional data
) WITH (
    tsdb.hypertable,
    tsdb.partition_column='timestamp',
    tsdb.enable_columnstore=true,     -- Disable if table has vector columns
    tsdb.segmentby='entity_id',       -- See selection guide below
    tsdb.orderby='timestamp DESC',     -- See selection guide below
    tsdb.sparse_index='minmax(value_1),minmax(value_2),minmax(value_3)' -- see selection guide below
);

压缩决策

• 对于以插入为主的场景，默认启用
• 如果表包含 vector 类型的列（pgvector），则禁用——vector 列上的索引与列存储不兼容

分区列选择

必须基于时间（TIMESTAMP/TIMESTAMPTZ/DATE）或整数（INT/BIGINT），且具有良好的时间/顺序分布。

常见模式：

• 时序：timestamp、event_time、measured_at
• 事件日志：event_time、created_at、logged_at
• 交易：created_at、transaction_time、processed_at
• 顺序数据：id（无时间戳时自动递增）、sequence_number
• 仅追加数据：created_at、inserted_at、id

不太理想： ingested_at（数据进入系统的时间——仅当它是主要查询维度时才使用） 避免使用： updated_at（会破坏时间顺序，除非它是主要查询维度）

Segment_By 列选择

优先选择单列——多列很少是最优选择。多列仅适用于高度相关的列（例如，metric_name + metric_type），且行密度足够的情况。

要求：

• 在 WHERE 子句中经常使用（最常见的筛选条件）
• 行密度良好（每个分块中每个值 >100 行）
• 主要逻辑分区/分组依据

示例：

• IoT：device_id
• 金融：symbol
• 指标：service_name、service_name, metric_type（行密度足够时）、metric_name, metric_type（行密度足够时）
• 分析：行密度足够时使用 user_id，否则使用 session_id
• 电商：行密度足够时使用 product_id，否则使用 category_id

行密度指南：

• 目标：每个分块中，每个 segment_by 值 >100 行。
• 较差：每个分块中，每个 segment_by 值 <10 行 → 选择粒度较粗的列
• 低密度列的处理方式：将其放在 order_by 列列表的前面。

由查询模式决定选择：

SELECT * FROM table WHERE entity_id = 'X' AND timestamp > ...
-- ↳ segment_by: entity_id (if >100 rows per chunk)

避免使用： 时间戳、唯一 ID、低密度列（每个分块每个值 <100 行）、筛选时很少使用的列

Order_By 列选择

与 segment_by 配合使用，可创建自然的时序顺序以实现最佳压缩。

最常见： timestamp DESC

示例：

• IoT/金融/电商：timestamp DESC
• 指标：metric_name, timestamp DESC（当 metric_name 的密度过低，不适合用作 segment_by 时）
• 分析：user_id, timestamp DESC（当 user_id 的密度过低，不适合用作 segment_by 时）

替代模式：

• 对于具有序列号的事件流，使用 sequence_id DESC
• 对同一时间戳内的数据进行子排序，使用 timestamp DESC, event_order DESC

低密度列的处理方式： 如果某列每个分块的行数 <100（对于 segment_by 来说过低），则将其放在 order_by 的前面：

• 示例：metric_name 每个分块有 20 行 → 使用 segment_by='service_name'、order_by='metric_name, timestamp DESC'
• 将相似值分组在一起（先是所有温度读数，再是所有压力读数），以提高压缩效果

良好的检验标准： (segment_by_column, order_by_column) 创建的顺序应构成自然的时序顺序。顺序中彼此接近的值应当相似。

避免用于 order_by： 随机列、相邻行之间差异较大的列、与 segment_by 无关的列

压缩稀疏索引选择

稀疏索引可在不解压数据的情况下对压缩数据进行查询筛选。按批次（约 1000 行）存储元数据，以排除不符合查询条件的批次。

类型：

• **minmax：**每批次的最小值／最大值——用于数值或时间列的范围查询（>、<、BETWEEN）

**适用于 minmax 的情况：**价格、温度、测量值、时间戳（范围筛选）

使用场景：

• 使用 minmax 检测异常值（温度 > 90）。
• 用于与 segmentby 和 orderby 列高度相关的字段（例如，如果 orderby 包含 created_at，则对 updated_at 使用 minmax 会很有用）。

**避免使用：**很少用于筛选的列。

重要：绝不要为 segmentby 或 orderby 中的列创建索引。orderby 列始终会自动创建 minmax 索引，无需任何配置。

配置： 格式为以逗号分隔的 type_of_index(column_name) 列表。

ALTER TABLE table_name SET (
    timescaledb.sparse_index = 'minmax(value_1),minmax(value_2)'
);

从 v2.22.0 开始支持显式配置（自 v2.16.0 起自动创建）。

块时间间隔（可选）

默认值：7 天（如果不确定数据量，请使用此值或询问用户）。根据数据量调整：

• 高频：1 小时 - 1 天
• 中频：1 天 - 1 周
• 低频：1 周 - 1 个月

SELECT set_chunk_time_interval('your_table_name', INTERVAL '1 day');

**良好测试标准：**近期块的索引应占用少于 25% 的 RAM。

索引与主键

常见索引模式——在 ID 和时间戳上创建复合索引：

CREATE INDEX idx_entity_timestamp ON your_table_name (entity_id, timestamp DESC);

**重要：**仅创建实际会用到的索引——每个索引都会增加维护开销。

**主键和唯一约束规则：**必须包含分区列。

选项 1：包含分区列的复合主键

ALTER TABLE your_table_name ADD PRIMARY KEY (entity_id, timestamp);

选项 2：单列主键（仅当该列为分区列时）

CREATE TABLE ... (id BIGINT PRIMARY KEY, ...) WITH (tsdb.partition_column='id');

选项 3：不设主键：对于以插入为主的模式，通常不需要严格保证唯一性。

步骤 2：压缩策略（可选）

重要：如果在步骤 1 中使用了 tsdb.enable_columnstore=true，从 TimescaleDB 2.23 版开始，系统会自动创建一个 after => INTERVAL '7 days' 的列存储策略。只有在想要将 after 间隔自定义为非 7 天时，才需要调用 add_columnstore_policy()。

在以下情况下设置 after 间隔：数据基本不再变化（允许少量更新／回填），并且查询不需要 B-tree 索引（较少见的条件）。

-- In TimescaleDB 2.23 and later only needed if you want to override the default 7-day policy created by tsdb.enable_columnstore=true
-- Remove the existing auto-created policy first:
-- CALL remove_columnstore_policy('your_table_name');
-- Then add custom policy:
-- CALL add_columnstore_policy('your_table_name', after => INTERVAL '1 day');

步骤 3：保留策略

重要：不要猜测——请询问用户；如果未知，请将其注释掉。

-- Example - replace with requirements or comment out
SELECT add_retention_policy('your_table_name', INTERVAL '365 days');

步骤 4：创建连续聚合

针对不同用途使用不同的聚合间隔。

短期（分钟／小时）

用于高频数据的实时仪表板。

CREATE MATERIALIZED VIEW your_table_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 hour', timestamp) AS bucket,
    entity_id,
    category,
    COUNT(*) as record_count,
    AVG(value_1) as avg_value_1,
    MIN(value_1) as min_value_1,
    MAX(value_1) as max_value_1,
    SUM(value_2) as sum_value_2
FROM your_table_name
GROUP BY bucket, entity_id, category;

长期（天／周／月）

用于长期报告和分析。

CREATE MATERIALIZED VIEW your_table_daily
WITH (timescaledb.continuous) AS
SELECT
    time_bucket(INTERVAL '1 day', timestamp) AS bucket,
    entity_id,
    category,
    COUNT(*) as record_count,
    AVG(value_1) as avg_value_1,
    MIN(value_1) as min_value_1,
    MAX(value_1) as max_value_1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value_1) as median_value_1,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value_1) as p95_value_1,
    SUM(value_2) as sum_value_2
FROM your_table_name
GROUP BY bucket, entity_id, category;

步骤 5：聚合刷新策略

根据数据新鲜度要求设置刷新策略。

**start_offset：**通常省略（刷新所有数据）。例外情况：如果不需要刷新早于 X 的数据（见下文）。如果原始数据设置了保留策略，则与保留策略保持一致。

**end_offset：**设置为超出活跃更新窗口的值（例如，数据通常在 10 分钟内到达时，设置为 15 分钟）。较 end_offset 更新的数据不会出现在未使用实时聚合的查询中。如果不知道更新窗口，则使用查询中 time_bucket 的大小，但不要低于 5 分钟。

**schedule_interval：**设为与 end_offset 相同的值，但不超过 1 小时。

每小时刷新——适用于需要频繁刷新的仪表板：

SELECT add_continuous_aggregate_policy('your_table_hourly',
    start_offset => NULL,
    end_offset => INTERVAL '15 minutes',
    schedule_interval => INTERVAL '15 minutes');

每日刷新——适用于刷新频率较低的报告：

SELECT add_continuous_aggregate_policy('your_table_daily',
    start_offset => NULL,
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

仅当不需要刷新旧数据时才使用 start_offset 适用于数据量大且不在意旧数据查询准确性的系统：

-- the following aggregate can be stale for data older than 7 days
-- SELECT add_continuous_aggregate_policy('aggregate_for_last_7_days',
--     start_offset => INTERVAL '7 days',    -- only refresh last 7 days (NULL = refresh all)
--     end_offset => INTERVAL '15 minutes',
--     schedule_interval => INTERVAL '15 minutes');

重要：必须将 start_offset 设为小于原始数据保留期限的值。默认情况下，将 start_offset 设为与保留期限相同。 如果保留策略被注释掉，也要将 start_offset 一并注释掉。示例如下：

SELECT add_continuous_aggregate_policy('your_table_daily',
    start_offset => NULL,    -- Use NULL to refresh all data, or set to retention period if enabled on raw data
--  start_offset => INTERVAL '<retention period here>',    -- uncomment if retention policy is enabled on the raw data table
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

步骤 6：实时聚合（可选）

实时聚合会在查询时将物化数据与近期原始数据合并。这样可以获得最新结果，但查询延迟会更高。

与粗粒度聚合（例如每日／每月）相比，实时聚合更适用于细粒度聚合（例如每分钟），因为近期数据通常无法填满较大的时间桶。

在 v2.13+ 中默认禁用，此前默认启用。

适用场景： 需要获取比 end_offset 更新的数据、需要精确到分钟的仪表板，并且可以接受更高的查询延迟 禁用场景： 对性能要求很高、刷新策略已足够、查询量很大，或可以接受近期数据缺失或过时

启用以获取最新结果（查询成本更高）：

ALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = false);

禁用以提升性能（但结果会过时）：

ALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = true);

步骤 7：压缩聚合数据

规则：segment_by = 除 time_bucket 外的所有 GROUP BY 列，order_by = time_bucket DESC

-- Hourly
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
ALTER MATERIALIZED VIEW your_table_daily SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id, category',
    timescaledb.orderby = 'bucket DESC'
);
CALL add_columnstore_policy('your_table_daily', after => INTERVAL '7 days');

步骤 8：聚合数据保留策略

聚合数据通常比原始数据保留更长时间。 重要：不要猜测——询问用户；如果未知，则必须注释掉。

-- Example - replace or comment out
SELECT add_retention_policy('your_table_hourly', INTERVAL '2 years');
SELECT add_retention_policy('your_table_daily', INTERVAL '5 years');

步骤 9：连续聚合的性能索引

索引策略： 分析常见查询中的 WHERE 子句 → 创建与筛选列和时间排序相匹配的索引

模式： (filter_column, bucket DESC) 可支持 WHERE filter_column = X AND bucket >= Y ORDER BY bucket DESC

示例：

CREATE INDEX idx_hourly_entity_bucket ON your_table_hourly (entity_id, bucket DESC);
CREATE INDEX idx_hourly_category_bucket ON your_table_hourly (category, bucket DESC);

多列筛选： 为 WHERE entity_id = X AND category = Y 创建复合索引：

CREATE INDEX idx_hourly_entity_category_bucket ON your_table_hourly (entity_id, category, bucket DESC);

重要： 只创建实际会用到的索引——每个索引都会带来维护开销。

步骤 10：可选增强

空间分区（不推荐）

仅当查询模式始终会按空间分区列进行筛选、你具备专业知识并进行了广泛基准测试时才使用。强烈建议优先使用仅按时间分区。

步骤 11：验证配置

-- Check hypertable
SELECT * FROM timescaledb_information.hypertables
WHERE hypertable_name = 'your_table_name';

-- Check compression settings
SELECT * FROM hypertable_compression_stats('your_table_name');

-- Check aggregates
SELECT * FROM timescaledb_information.continuous_aggregates;

-- Check policies
SELECT * FROM timescaledb_information.jobs ORDER BY job_id;

-- Monitor chunk information
SELECT
    chunk_name,
    range_start,
    range_end,
    is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
ORDER BY range_start DESC;

性能指南

• 分块大小： 近期分块的索引应占用不到 RAM 的 25%
• 压缩： 使用恰当的列存配置，预计可减少 90% 以上（10 倍）
• 查询优化： 使用连续聚合处理历史查询和仪表板
• 内存： 自托管时运行 timescaledb-tune（云端会自动配置）

架构最佳实践

推荐做法与禁忌

• ✅ 使用 TIMESTAMPTZ，不要使用 timestamp
• ✅ 时间戳使用 >= 和 <，不要使用 BETWEEN
• ✅ 使用带约束的 TEXT，不要使用 char(n)／varchar(n)
• ✅ 使用 snake_case，不要使用 CamelCase
• ✅ 使用 BIGINT GENERATED ALWAYS AS IDENTITY，不要使用 SERIAL
• ✅ 默认使用 BIGINT 作为 ID，而不是 INTEGER 或 SMALLINT
• ✅ 默认使用 DOUBLE PRECISION，而不是 REAL／FLOAT
• ✅ 使用 NUMERIC，不要使用 MONEY
• ✅ 使用 NOT EXISTS，不要使用 NOT IN
• ✅ 截断时间时使用 time_bucket() 或 date_trunc()，不要使用 timestamp(0)

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，而非 SELECT）
• decompress_chunk() → convert_to_rowstore()（使用 CALL，而非 SELECT）

压缩统计信息（使用函数，而非视图）：

• 使用函数：hypertable_compression_stats('table_name')
• 使用函数：chunk_compression_stats('_timescaledb_internal._hyper_X_Y_chunk')
• 注意：columnstore_settings 等视图可能并非所有版本都提供；请改用函数

手动压缩示例：

-- Compress a specific chunk
CALL convert_to_columnstore('_timescaledb_internal._hyper_7_1_chunk');

-- Check compression statistics
SELECT
    number_compressed_chunks,
    pg_size_pretty(before_compression_total_bytes) as before_compression,
    pg_size_pretty(after_compression_total_bytes) as after_compression,
    ROUND(100.0 * (1 - after_compression_total_bytes::numeric / NULLIF(before_compression_total_bytes, 0)), 1) as compression_pct
FROM hypertable_compression_stats('your_table_name');

要向用户提出的问题

1. 你将存储哪类数据？
2. 你希望如何使用这些数据？
3. 你会运行哪些查询？
4. 数据要保留多久？
5. 如果列类型不明确，请询问