migrate-postgres-tables-to-hypertables

PostgreSQL 迁移到 TimescaleDB 超表

将已确定的 PostgreSQL 表迁移为 TimescaleDB 超表，并采用最优配置、制定迁移计划及进行验证。

前置条件：已确定适合作为超表的候选表（如有需要，请使用配套的「find-hypertable-candidates」技能）。

第 1 步：最优配置

分区列选择

-- Find potential partition columns
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'your_table_name'
  AND data_type IN ('timestamp', 'timestamptz', 'bigint', 'integer', 'date')
ORDER BY ordinal_position;

**要求：**基于时间（TIMESTAMP/TIMESTAMPTZ/DATE）或连续整数（INT/BIGINT）

应表示事件实际发生的时间或连续的顺序。

常见选择：

• timestamp、created_at、event_time——事件发生的时间
• id、sequence_number——自增值（适用于没有时间戳的连续数据）
• ingested_at——不太理想，仅在其是主要查询维度时使用
• updated_at——避免使用（记录更新顺序不固定，会破坏分块分布），除非它是主要查询维度

特殊情况：表同时包含 ID 和时间戳

当表有连续 ID（PK）和与之相关的时间戳时：

-- Partition by ID, enable minmax sparse indexes on timestamp
SELECT create_hypertable('orders', 'id', chunk_time_interval => 1000000);
ALTER TABLE orders SET (
    timescaledb.sparse_index = 'minmax(created_at),...'
);

时间列上的稀疏索引可以跳过查询时间范围之外的压缩块。

适用场景：ID 与时间相关（较新的记录具有较大的 ID）、需要基于 ID 查找、也经常进行时间查询

分块间隔选择

-- Ensure statistics are current
ANALYZE your_table_name;

-- Estimate index size per time unit
WITH time_range AS (
    SELECT
        MIN(timestamp_column) as min_time,
        MAX(timestamp_column) as max_time,
        EXTRACT(EPOCH FROM (MAX(timestamp_column) - MIN(timestamp_column)))/3600 as total_hours
    FROM your_table_name
),
total_index_size AS (
    SELECT SUM(pg_relation_size(indexname::regclass)) as total_index_bytes
    FROM pg_stat_user_indexes
    WHERE schemaname||'.'||tablename = 'your_schema.your_table_name'
)
SELECT
    pg_size_pretty(tis.total_index_bytes / tr.total_hours) as index_size_per_hour
FROM time_range tr, total_index_size tis;

**目标：**最近分块的索引总大小 < RAM 的 25% **默认值：**重要：如不确定，请保留 7 天的默认值 **范围：**最短 1 小时，最长 30 天

**示例：**32GB RAM → 最近索引的目标大小为 8GB。如果每小时的索引大小为 200MB：

• 1 小时分块：每个分块的索引大小为 200MB × 最近 40 个分块 = 8GB ✓
• 6 小时分块：每个分块的索引大小为 1.2GB × 最近 7 个分块 = 8.4GB ✓
• 1 天分块：每个分块的索引大小为 4.8GB × 最近 2 个分块 = 9.6GB ⚠️ 选择能让最近至少 2 个分块的索引总大小低于目标值的最大间隔。

主键/唯一约束兼容性

-- Check existing primary key/ unique constraints
SELECT conname, pg_get_constraintdef(oid) as definition
FROM pg_constraint
WHERE conrelid = 'your_table_name'::regclass AND contype = 'p' OR contype = 'u';

**规则：**PK/UNIQUE 必须包含分区列

操作：

1. **没有 PK/UNIQUE：**无需更改
2. **PK/UNIQUE 包含分区列：**无需更改
3. PK/UNIQUE 不包含分区列：⚠️ 征求用户许可以修改 PK/UNIQUE

示例：需要时向用户提示：

「主键 (id) 不包含分区列 (timestamp)。必须将其修改为 PRIMARY KEY (id, timestamp) 才能转换为超表。这可能会破坏应用程序代码。可以接受吗？」 「唯一约束 (id) 不包含分区列 (timestamp)。必须将其修改为 UNIQUE (id, timestamp) 才能转换为超表。这可能会破坏应用程序代码。可以接受吗？」

如果用户同意，请修改约束：

BEGIN;
ALTER TABLE your_table_name DROP CONSTRAINT existing_pk_name;
ALTER TABLE your_table_name ADD PRIMARY KEY (existing_columns, partition_column);
COMMIT;

如果用户不同意，则不得迁移该表。

重要：未经用户许可，不要修改主键/唯一约束。

压缩配置

有关 segment_by 和 order_by 的详细选择，请参阅「setup-timescaledb-hypertables」技能。快速参考：

**segment_by：**每个分块中每个值对应超过 100 行的最常见 WHERE 过滤条件

• IoT：device_id
• 金融：symbol
• 分析：user_id 或 session_id

-- Analyze cardinality for segment_by selection
SELECT column_name, COUNT(DISTINCT column_name) as unique_values,
       ROUND(COUNT(*)::float / COUNT(DISTINCT column_name), 2) as avg_rows_per_value
FROM your_table_name GROUP BY column_name;

**order_by：**通常为 timestamp DESC。(segment_by, order_by) 组合应形成自然的时间序列。

• 如果某列每个分块的行数少于 100（不适合作为 segment_by），则将其放在 order_by 前面：order_by='low_density_col, timestamp DESC'

**稀疏索引：**对 WHERE 子句中使用、但未包含在 segment_by 或 order_by 中的列添加 minmax。对范围查询中使用的列使用 minmax。

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

-- Compress after data unlikely to change (adjust `after` parameter based on update patterns)
CALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');

第 2 步：迁移规划

迁移前检查清单

• 已选择分区列
• 已计算分块间隔（或使用默认值）
• PK 包含分区列，或用户已批准修改
• 不存在 Hypertable→Hypertable 外键
• 唯一约束包含分区列
• 已创建压缩配置（segment_by、order_by、稀疏索引、压缩策略）
• 已安排维护时段 / 已创建备份。

迁移选项

选项 1：原地迁移（表小于 1GB）

-- Enable extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Convert to hypertable (locks table)
SELECT create_hypertable(
    'your_table_name',
    'timestamp_column',
    chunk_time_interval => INTERVAL '7 days',
    if_not_exists => TRUE
);

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

-- Adjust `after` parameter based on update patterns
CALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');

选项 2：蓝绿迁移（表 > 1GB）

-- 1. Create new hypertable
CREATE TABLE your_table_name_new (LIKE your_table_name INCLUDING ALL);

-- 2. Convert to hypertable
SELECT create_hypertable('your_table_name_new', 'timestamp_column');

-- 3. Configure compression
ALTER TABLE your_table_name_new SET (
    timescaledb.enable_columnstore,
    timescaledb.segmentby = 'entity_id',
    timescaledb.orderby = 'timestamp DESC'
);

-- 4. Migrate data in batches
INSERT INTO your_table_name_new
SELECT * FROM your_table_name
WHERE timestamp_column >= '2024-01-01' AND timestamp_column < '2024-02-01';
-- Repeat for each time range

-- 4. Enter maintenance window and do the following:

-- 5. Pause modification of the old table.

-- 6. Copy over the most recent data from the old table to the new table.

-- 7. Swap tables
BEGIN;
ALTER TABLE your_table_name RENAME TO your_table_name_old;
ALTER TABLE your_table_name_new RENAME TO your_table_name;
COMMIT;

-- 8. Exit maintenance window.

-- 9. (sometime much later) Drop old table after validation
-- DROP TABLE your_table_name_old;

常见问题

外键

-- Check foreign keys
SELECT conname, confrelid::regclass as referenced_table
FROM pg_constraint
WHERE (conrelid = 'your_table_name'::regclass
    OR confrelid = 'your_table_name'::regclass)
  AND contype = 'f';

支持： 普通表→超表、超表→普通表 不支持： 超表→超表

⚠️ 重要： 必须删除超表→超表外键（在应用中强制执行）。征求用户许可。如果用户不同意，停止迁移。

大表迁移时间

-- Rough estimate: ~75k rows/second
SELECT
    pg_size_pretty(pg_total_relation_size(tablename)) as size,
    n_live_tup as rows,
    ROUND(n_live_tup / 75000.0 / 60, 1) as estimated_minutes
FROM pg_stat_user_tables
WHERE tablename = 'your_table_name';

大型表（>1GB/1000 万行）的解决方案： 使用蓝绿迁移，在非高峰时段迁移，先在数据子集上测试

第 3 步：性能验证

分块与压缩分析

-- View chunks and compression
SELECT
    chunk_name,
    pg_size_pretty(total_bytes) as size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    ROUND((total_bytes - compressed_total_bytes::numeric) / total_bytes * 100, 1) as compression_pct,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
ORDER BY range_start DESC;

关注以下内容：

• 分块大小一致（误差在 2 倍以内）
• 时序数据的压缩率 >90%
• 最近的分块未压缩
• 分块索引 < 25% RAM

查询性能测试

-- 1. Time-range query (should show chunk exclusion)
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), AVG(value)
FROM your_table_name
WHERE timestamp >= NOW() - INTERVAL '1 day';

-- 2. Entity + time query (benefits from segment_by)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table_name
WHERE entity_id = 'X' AND timestamp >= NOW() - INTERVAL '1 week';

-- 3. Aggregation (benefits from columnstore)
EXPLAIN (ANALYZE, BUFFERS)
SELECT DATE_TRUNC('hour', timestamp), entity_id, COUNT(*), AVG(value)
FROM your_table_name
WHERE timestamp >= NOW() - INTERVAL '1 month'
GROUP BY 1, 2;

✅ 良好迹象：

• EXPLAIN 计划中出现 “Chunks excluded during startup: X”
• 压缩数据使用 “Custom Scan (ColumnarScan)”
• EXPLAIN ANALYZE 计划中的 “Buffers: shared read” 低于迁移前
• 执行时间更短

❌ 不良迹象：

• 大型分块上出现 “Seq Scan”
• 没有分块排除消息
• 比迁移前更慢

存储指标

-- Monitor compression effectiveness
SELECT
    hypertable_name,
    pg_size_pretty(total_bytes) as total_size,
    pg_size_pretty(compressed_total_bytes) as compressed_size,
    ROUND(compressed_total_bytes::numeric / total_bytes * 100, 1) as compressed_pct_of_total,
    ROUND((uncompressed_total_bytes - compressed_total_bytes::numeric) /
          uncompressed_total_bytes * 100, 1) as compression_ratio_pct
FROM timescaledb_information.hypertables
WHERE hypertable_name = 'your_table_name';

监控以下内容：

• compression_ratio_pct >90%（典型时序数据）
• 随着数据变旧，compressed_pct_of_total 持续增长
• 与超表转换前相比，大小增长显著放缓
• compression_ratio_pct 下降 = segment_by 设置不佳

故障排除

分块排除效果不佳

-- Verify chunks are being excluded
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM your_table_name
WHERE timestamp >= '2024-01-01' AND timestamp < '2024-01-02';
-- Look for "Chunks excluded during startup: X"

压缩效果不佳

-- Get newest compressed chunk name
SELECT chunk_name FROM timescaledb_information.chunks
WHERE hypertable_name = 'your_table_name'
  AND compressed_total_bytes IS NOT NULL
ORDER BY range_start DESC LIMIT 1;

-- Analyze segment distribution
SELECT segment_by_column, COUNT(*) as rows_per_segment
FROM _timescaledb_internal._hyper_X_Y_chunk  -- Use actual chunk name
GROUP BY 1 ORDER BY 2 DESC;

关注以下情况： 每个段少于 20 行：segment_by 选择不佳（应 >100）=> 压缩潜力低。

插入性能不佳

检查索引是否过多。未使用的索引会影响插入性能，应予以删除。

SELECT
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM pg_stat_user_indexes
WHERE tablename LIKE '%your_table_name%'
ORDER BY idx_scan DESC;

关注以下情况： idx_scan 值较低表示存在未使用的索引。删除此类索引（但需征求用户许可）。

持续监控

-- Monitor chunk compression status
CREATE OR REPLACE VIEW hypertable_compression_status AS
SELECT
    h.hypertable_name,
    COUNT(c.chunk_name) as total_chunks,
    COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL) as compressed_chunks,
    ROUND(
        COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL)::numeric /
        COUNT(c.chunk_name) * 100, 1
    ) as compression_coverage_pct,
    pg_size_pretty(SUM(c.total_bytes)) as total_size,
    pg_size_pretty(SUM(c.compressed_total_bytes)) as compressed_size
FROM timescaledb_information.hypertables h
LEFT JOIN timescaledb_information.chunks c ON h.hypertable_name = c.hypertable_name
GROUP BY h.hypertable_name;

-- Query this view regularly to monitor compression progress
SELECT * FROM hypertable_compression_status
WHERE hypertable_name = 'your_table_name';

关注以下内容：

• 随着数据变旧并经过压缩，compression_coverage_pct 应逐渐提高。
• total_chunks 不应增长过快（超过 10000 就会成为问题）。
• 不应看到 total_size 或 compressed_size 出现意外峰值。

成功标准

✅ 满足以下条件即表示迁移成功：

• 所有查询均返回正确结果
• 查询性能持平或更好
• 较旧数据的压缩率 >90%
• 时间查询的分块排除正常工作
• 插入性能可接受

❌ 出现以下情况时应进行排查：

• 查询性能下降 >20%
• 压缩率 <80%
• 未进行分块排除
• 插入性能下降
• 错误率升高

为获得最佳投资回报，重点关注高吞吐量、插入密集型且采用基于时间的访问模式的工作负载。