migrate-postgres-tables-to-hypertables

用途

该技能用于将已经识别为超表候选的 PostgreSQL 表迁移为 Timescale/TimescaleDB 超表，并给出最优配置、迁移规划与验证方法。使用前提是表已被识别为超表候选；如尚未识别，可配合 "find-hypertable-candidates" 技能。

主要能力

1. 最优配置

分区列选择：查询 information_schema.columns 找出 timestamp、timestamptz、bigint、integer、date 类型的候选列。要求为基于时间（TIMESTAMP/TIMESTAMPTZ/DATE）或顺序整数（INT/BIGINT），应表示事件实际发生时间或顺序。常见选择包括 timestamp、created_at、event_time（事件发生时间），id、sequence_number（无时间戳的顺序数据自增列）；ingested_at 不太理想，仅在作为主要查询维度时使用；updated_at 应避免（记录乱序更新会破坏分块分布），除非是主要查询维度。

同时含 ID 与时间戳的特殊情况：当表同时具有顺序 ID（主键）与相关时间戳时，可按 ID 分区并对时间戳启用 minmax 稀疏索引：

SELECT create_hypertable('orders', 'id', chunk_time_interval => 1000000);
ALTER TABLE orders SET (
    timescaledb.sparse_index = 'minmax(created_at),...'
);

时间列上的稀疏索引可跳过查询时间范围之外的压缩块。适用于 ID 与时间相关（较新记录 ID 更大）、需要基于 ID 查找且时间查询也常见的情况。

分块间隔选择：先执行 ANALYZE 确保统计信息最新，再估算每时间单位的索引大小。目标为最近分块的索引小于内存的 25%；不确定时保持默认 7 天；范围为最小 1 小时、最大 30 天。示例：32GB 内存目标为 8GB 最近索引，若 index_size_per_hour = 200MB，则 1 小时分块为 200MB × 40 个近期分块 = 8GB，6 小时分块为 1.2GB × 7 = 8.4GB，1 天分块为 4.8GB × 2 = 9.6GB（偏高）。应选择使 2 个以上近期分块索引保持在目标以下的最大间隔。

主键/唯一约束兼容性：查询 pg_constraint 检查现有主键与唯一约束。规则是主键/唯一约束必须包含分区列。无主键/唯一约束时无需更改；已包含分区列时无需更改；不包含分区列时必须征得用户许可后修改，例如将 PRIMARY KEY (id) 改为 PRIMARY KEY (id, timestamp)，或 UNIQUE (id) 改为 UNIQUE (id, timestamp)，这可能破坏应用代码。用户接受则执行 ALTER TABLE 修改约束；用户不接受则不应迁移该表。重要：未经用户许可不得修改主键/唯一约束。

压缩配置：segment_by 选择每分块每值超过 100 行的最常见 WHERE 过滤列（IoT 用 device_id，金融用 symbol，分析用 user_id 或 session_id）；order_by 通常为 timestamp DESC，(segment_by, order_by) 组合应形成自然的时间序列递进；若某列每分块少于 100 行，则将其前置到 order_by。稀疏索引对 WHERE 子句中使用但不在 segment_by 或 order_by 中的列添加 minmax，用于范围查询。配置示例：

ALTER TABLE your_table_name SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC'
    timescaledb.sparse_index = 'minmax(value_1),...'
);

CALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');

压缩应在数据不太可能变化后执行，after 参数根据更新模式调整。

2. 迁移规划

迁移前检查清单：已选择分区列；已计算分块间隔（或使用默认值）；主键包含分区列或用户已批准修改；无超表到超表的外键；唯一约束包含分区列；已创建压缩配置（segment_by、order_by、稀疏索引、压缩策略）；已安排维护窗口/创建备份。

方案一：原地迁移（表小于 1GB）：启用扩展，转换为超表（会锁表），配置压缩，添加压缩策略。

CREATE EXTENSION IF NOT EXISTS timescaledb;

SELECT create_hypertable(
    'your_table_name',
    'timestamp_column',
    chunk_time_interval => INTERVAL '7 days',
    if_not_exists => TRUE
);

ALTER TABLE your_table_name SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC',
    timescaledb.sparse_index = 'minmax(value_1),...'
);

CALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');

方案二：蓝绿迁移（表大于 1GB）：创建新表并转换为超表，配置压缩，按时间范围分批迁移数据，进入维护窗口后暂停旧表修改、复制最新数据、交换表名，退出维护窗口，验证后再删除旧表。

CREATE TABLE your_table_name_new (LIKE your_table_name INCLUDING ALL);
SELECT create_hypertable('your_table_name_new', 'timestamp_column');
ALTER TABLE your_table_name_new SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC'
);
INSERT INTO your_table_name_new
SELECT * FROM your_table_name
WHERE timestamp_column >= '2024-01-01' AND timestamp_column < '2024-02-01';

BEGIN;
ALTER TABLE your_table_name RENAME TO your_table_name_old;
ALTER TABLE your_table_name_new RENAME TO your_table_name;
COMMIT;

常见问题：外键方面，支持普通表→超表、超表→普通表，不支持超表→超表；超表→超表外键必须删除（在应用中强制），需征得用户许可，若不允许则停止迁移。大表迁移时间粗略估算约 75k 行/秒，超过 1GB/10M 行建议使用蓝绿迁移、在非高峰迁移、先在子集上测试。

3. 性能验证

分块与压缩分析：查询 timescaledb_information.chunks 查看分块名称、大小、压缩大小、压缩百分比与时间范围。关注分块大小一致（2 倍以内）、时序数据压缩率大于 90%、近期分块未压缩、分块索引小于内存 25%。

查询性能测试：对时间范围查询（应显示分块排除）、实体+时间查询（受益于 segment_by）、聚合查询（受益于列存）执行 EXPLAIN (ANALYZE, BUFFERS)。良好迹象包括 EXPLAIN 计划中出现 "Chunks excluded during startup: X"、压缩数据出现 "Custom Scan (ColumnarScan)"、共享读取缓冲区低于迁移前、执行时间更快；不良迹象包括大分块上的 "Seq Scan"、无分块排除消息、比迁移前更慢。

存储指标：查询 timescaledb_information.hypertables 监控压缩效果，关注 compression_ratio_pct 大于 90%、compressed_pct_of_total 随数据老化增长、大小增长显著放缓；compression_ratio_pct 下降说明 segment_by 选择不佳。

4. 故障排查

分块排除不佳时用 EXPLAIN 验证是否出现分块排除消息。压缩不佳时获取最新压缩分块名并分析 segment 分布，每 segment 少于 20 行说明 segment_by 选择不佳（应大于 100）。插入性能差时检查是否有过多索引，通过 pg_stat_user_indexes 的 idx_scan 找出未使用索引并删除（需征得用户许可）。

5. 持续监控

可创建 hypertable_compression_status 视图监控分块压缩状态，关注 compression_coverage_pct 随时间上升、total_chunks 不应增长过快（超过 10000 会成为问题）、total_size 或 compressed_size 不应出现意外峰值。

成功标准

迁移成功：所有查询返回正确结果、查询性能持平或更好、旧数据压缩率大于 90%、时间查询分块排除生效、插入性能可接受。需调查：查询性能下降超过 20%、压缩率低于 80%、无分块排除、插入性能退化、错误率上升。

适用场景

最适合高写入量、基于时间访问模式的时序数据工作负载，以获得最佳投资回报。