find-hypertable-candidates

PostgreSQL 超表候选分析

识别适合转换为 TimescaleDB 超表的表。识别完成后，使用配套的 "migrate-postgres-tables-to-hypertables" 技能进行配置和迁移。

TimescaleDB 的优势

性能提升： 压缩率超过 90%，加快基于时间的查询，提升插入性能，优化聚合操作，并通过连续聚合实现物化（仪表板、报告、分析），以及自动化数据管理（保留、压缩）。

最适合插入密集型模式：

• 时间序列数据（传感器、指标、监控）
• 事件日志（用户事件、审计记录、应用日志）
• 交易记录（订单、支付、金融数据）
• 顺序数据（带时间戳的自增 ID）
• 仅追加数据集（不可变记录、历史数据）

要求： 大量数据（100 万行以上）、基于时间的查询、很少更新

第 1 步：数据库架构分析

选项 A：从数据库连接分析

表统计信息和大小

-- Get all tables with row counts and insert/update patterns
WITH table_stats AS (
    SELECT
        schemaname, tablename,
        n_tup_ins as total_inserts,
        n_tup_upd as total_updates,
        n_tup_del as total_deletes,
        n_live_tup as live_rows,
        n_dead_tup as dead_rows
    FROM pg_stat_user_tables
),
table_sizes AS (
    SELECT
        schemaname, tablename,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
        pg_total_relation_size(schemaname||'.'||tablename) as total_size_bytes
    FROM pg_tables
    WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
)
SELECT
    ts.schemaname, ts.tablename, ts.live_rows,
    tsize.total_size, tsize.total_size_bytes,
    ts.total_inserts, ts.total_updates, ts.total_deletes,
    ROUND(CASE WHEN ts.live_rows > 0
          THEN (ts.total_inserts::float / ts.live_rows) * 100
          ELSE 0 END, 2) as insert_ratio_pct
FROM table_stats ts
JOIN table_sizes tsize ON ts.schemaname = tsize.schemaname AND ts.tablename = tsize.tablename
ORDER BY tsize.total_size_bytes DESC;

关注以下情况：

• 主要为插入密集型模式（较少更新／删除）
• 大表（100 万行以上或 100 MB 以上）

索引模式

-- Identify common query dimensions
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY tablename, indexname;

关注以下情况：

• 存在多个包含时间戳／created_at 列的索引 → 基于时间的查询
• 复合 (entity_id, timestamp) 索引 → 良好候选
• 仅包含时间列的索引 → 常见时间范围筛选

查询模式（如果 pg_stat_statements 可用）

-- Check availability
SELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements');

-- Analyze expensive queries for candidate tables
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query ILIKE '%your_table_name%'
ORDER BY total_exec_time DESC LIMIT 20;

✅ 良好模式： 基于时间的 WHERE 条件、结合时间限定条件的实体筛选、GROUP BY time_bucket、时间范围查询
❌ 不佳模式： 同一查询中不含基于时间的限定条件的非时间查找（WHERE email = ...）

约束

-- Check migration compatibility
SELECT conname, contype, pg_get_constraintdef(oid) as definition
FROM pg_constraint
WHERE conrelid = 'your_table_name'::regclass;

兼容性：

• 主键 (p)：必须包含分区列，否则询问用户是否可以修改
• 外键 (f)：普通表→超表和超表→普通表均可，超表→超表不支持
• 唯一约束 (u)：必须包含分区列，否则询问用户是否可以修改
• 检查约束 (c)：通常可用

选项 B：从代码分析

✅ 良好模式

# Append-only logging
INSERT INTO events (user_id, event_time, data) VALUES (...);
# Time-series collection
INSERT INTO metrics (device_id, timestamp, value) VALUES (...);
# Time-based queries
SELECT * FROM metrics WHERE timestamp >= NOW() - INTERVAL '24 hours';
# Time aggregations
SELECT DATE_TRUNC('day', timestamp), COUNT(*) GROUP BY 1;

❌ 不佳模式

# Frequent updates to historical records
UPDATE users SET email = ..., updated_at = NOW() WHERE id = ...;
# Non-time lookups
SELECT * FROM users WHERE email = ...;
# Small reference tables
SELECT * FROM countries ORDER BY name;

架构指标

✅ 良好：

• 有 timestamp／timestamptz 列
• 存在多个包含基于时间列的索引
• 复合 (entity_id, timestamp) 索引

❌ 不佳：

• 大多数索引都包含非时间列（例如 email、name、status 等列）
• 预期会随时间更新的列（updated_at、updated_by、status 等）
• 非时间字段上的唯一约束
• 经常修改 updated_at
• 小型静态表

特殊情况：基于 ID 的表

顺序 ID 表在以下情况下可能适合作为候选：

• 以插入为主／更新不频繁，或只更新近期记录。
• 如果确实会更新，则更新发生在近期记录上（例如订单状态从 orderered→processing→delivered。请注意，订单交付后，很可能不会再更新。）
• ID 与时间相关（如 serial／自增 ID／GENERATED ALWAYS AS IDENTITY）
• ID 是主要查询维度
• 近期数据访问更频繁（电子商务、金融等场景中常见）
• 常见基于时间的报告需求（例如按月、按日汇总／分析）

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,           -- Can partition by ID
    user_id BIGINT,
    created_at TIMESTAMPTZ DEFAULT NOW() -- For sparse indexes
);

注意：对于同时包含时间列（created_at、ordered_at 等）的基于 ID 的表， 可以按 ID 分区，并在时间列上使用稀疏索引。 详情请参阅 migrate-postgres-tables-to-hypertables 技能。

第 2 步：候选评分（8 分以上 = 良好候选）

时间序列特征（需要 5 分以上）

• 有 timestamp／timestamptz 列：3 分
• 按时间顺序插入数据：2 分
• 查询按时间筛选：2 分
• 常见时间聚合：2 分

规模与性能（建议得分 3 分以上）

• 大型表（1M+ 行或 100MB+）：2 分
• 高插入量：1 分
• 历史数据更新不频繁：1 分
• 常见范围查询：1 分
• 聚合查询：2 分

数据模式（加分项）

• 包含用于分段的实体 ID（device_id、user_id、product_id、symbol 等）：1 分
• 数值测量数据：1 分
• 日志/事件结构：1 分

常见模式

✅ 合适的候选表

✅ 事件/日志表（user_events、audit_logs）

CREATE TABLE user_events (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    event_type TEXT,
    event_time TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB
);
-- Partition by id, segment by user_id, enable minmax sparse_index on event_time

✅ 传感器/IoT 数据（sensor_readings、telemetry）

CREATE TABLE sensor_readings (
    device_id TEXT,
    timestamp TIMESTAMPTZ,
    temperature DOUBLE PRECISION,
    humidity DOUBLE PRECISION
);
-- Partition by timestamp, segment by device_id, minmax sparse indexes on temperature and humidity

✅ 金融/交易数据（stock_prices、transactions）

CREATE TABLE stock_prices (
    symbol VARCHAR(10),
    price_time TIMESTAMPTZ,
    open_price DECIMAL,
    close_price DECIMAL,
    volume BIGINT
);
-- Partition by price_time, segment by symbol, minmax sparse indexes on open_price and close_price and volume

✅ 系统指标（monitoring_data）

CREATE TABLE system_metrics (
    hostname TEXT,
    metric_time TIMESTAMPTZ,
    cpu_usage DOUBLE PRECISION,
    memory_usage BIGINT
);
-- Partition by metric_time, segment by hostname, minmax sparse indexes on cpu_usage and memory_usage

❌ 不合适的候选表

❌ 参考表（countries、categories）

CREATE TABLE countries (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    code CHAR(2)
);
-- Static data, no time component

❌ 用户资料（users、accounts）

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255),
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ
);
-- Accessed by ID, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is id or email)

❌ 设置/配置（user_settings）

CREATE TABLE user_settings (
    user_id BIGINT PRIMARY KEY,
    theme VARCHAR(20),       -- Changes: light -> dark -> auto
    language VARCHAR(10),    -- Changes: en -> es -> fr
    notifications JSONB,     -- Frequent preference updates
    updated_at TIMESTAMPTZ
);
-- Accessed by user_id, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is user_id)

分析输出要求

请为每个候选表提供：

• 评分： 根据标准评分（8 分以上 = 强候选表）
• 模式： 插入与更新的比例
• 访问方式： 按时间查询还是按实体查询
• 大小： 当前大小和增长率
• 查询： 时间范围查询、聚合查询、点查找

重点关注以插入为主、按时间或顺序访问的模式。得分 8 分以上的表是转换的理想候选表。