超表（Hypertable） #

超表架构：

用户视角
│
├── Hypertable（超表）
│   └── 看起来像普通表
│       └── 使用标准SQL操作
│
底层实现
│
├── Chunk 1（分片1）
│   └── 时间范围：2024-01-01 ~ 2024-01-07
│
├── Chunk 2（分片2）
│   └── 时间范围：2024-01-08 ~ 2024-01-14
│
├── Chunk 3（分片3）
│   └── 时间范围：2024-01-15 ~ 2024-01-21
│
└── Chunk N（分片N）
    └── 时间范围：...

超表优势：

性能优势
├── 分区裁剪 - 只扫描相关分区
├── 并行查询 - 多分片并行处理
├── 索引优化 - 每个分片独立索引
└── 写入优化 - 分片并行写入

管理优势
├── 自动分区 - 无需手动管理
├── 数据生命周期 - 自动清理旧数据
├── 压缩策略 - 自动压缩历史数据
└── 备份恢复 - 分片级别操作

兼容性优势
├── SQL兼容 - 标准SQL操作
├── 工具兼容 - PostgreSQL工具
├── 应用兼容 - 无需修改应用
└── 扩展兼容 - PostgreSQL扩展

对比普通表：

特性              普通表          超表
─────────────────────────────────────────
创建方式          CREATE TABLE    CREATE TABLE + create_hypertable
时间分区          手动            自动
空间分区          手动            自动
分区裁剪          需配置          自动
连续聚合          不支持          支持
压缩              不支持          支持
保留策略          手动            自动
SQL操作           标准            标准
索引              标准            标准

-- 第一步：创建普通表
CREATE TABLE sensor_data (
    time        TIMESTAMPTZ NOT NULL,
    sensor_id   INTEGER NOT NULL,
    temperature DOUBLE PRECISION,
    humidity    DOUBLE PRECISION,
    pressure    DOUBLE PRECISION
);

-- 第二步：转换为超表
SELECT create_hypertable(
    'sensor_data',    -- 表名
    'time'            -- 时间列名
);

-- 查看超表信息
SELECT 
    hypertable_name,
    num_chunks,
    compression_state
FROM timescaledb_information.hypertables;

-- 完整参数创建
SELECT create_hypertable(
    'sensor_data',
    'time',
    partitioning_column => 'sensor_id',  -- 空间分区列
    number_partitions => 4,               -- 空间分区数
    chunk_time_interval => INTERVAL '7 days',  -- 时间分区间隔
    if_not_exists => TRUE,                -- 如果已存在不报错
    migrate_data => TRUE                  -- 迁移现有数据
);

-- 设置时间分区间隔
SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 day');

-- 或使用时间戳（微秒）
SELECT set_chunk_time_interval('sensor_data', 86400000000);

-- 常用分区间隔
-- 高频数据：1小时、6小时
-- 中频数据：1天、7天
-- 低频数据：1月、3月

-- 示例：不同场景的分区间隔
SELECT set_chunk_time_interval('high_freq_data', INTERVAL '1 hour');
SELECT set_chunk_time_interval('medium_freq_data', INTERVAL '1 day');
SELECT set_chunk_time_interval('low_freq_data', INTERVAL '1 month');

-- 创建带空间分区的超表
CREATE TABLE metrics (
    time        TIMESTAMPTZ NOT NULL,
    device_id   INTEGER NOT NULL,
    metric_name VARCHAR(100),
    value       DOUBLE PRECISION
);

SELECT create_hypertable(
    'metrics',
    'time',
    partitioning_column => 'device_id',
    number_partitions => 4
);

-- 空间分区优势
-- 1. 提高并行写入性能
-- 2. 优化设备级别查询
-- 3. 分散I/O负载

-- 查看所有超表
SELECT * FROM timescaledb_information.hypertables;

-- 查看超表详细信息
SELECT 
    hypertable_schema,
    hypertable_name,
    num_chunks,
    compression_state,
    is_distributed,
    replication_factor
FROM timescaledb_information.hypertables;

-- 查看超表维度信息
SELECT 
    hypertable_name,
    column_name,
    column_type,
    is_dimension
FROM timescaledb_information.dimensions;

-- 查看分片信息
SELECT 
    hypertable_name,
    chunk_name,
    range_start,
    range_end,
    is_compressed
FROM timescaledb_information.chunks
ORDER BY range_start DESC
LIMIT 10;

-- 修改时间分区间隔
SELECT set_chunk_time_interval('sensor_data', INTERVAL '14 days');

-- 添加空间分区（需要重建）
-- 注意：不能直接添加空间分区，需要重建超表

-- 修改表结构
ALTER TABLE sensor_data ADD COLUMN battery_level DOUBLE PRECISION;

-- 添加索引
CREATE INDEX idx_sensor_id ON sensor_data (sensor_id, time DESC);

-- 添加约束
ALTER TABLE sensor_data
ADD CONSTRAINT chk_temperature CHECK (temperature BETWEEN -50 AND 100);

-- 删除超表（保留数据）
DROP TABLE sensor_data;

-- 删除超表及其所有分片
SELECT drop_hypertable('sensor_data');

-- 级联删除
DROP TABLE sensor_data CASCADE;

-- 插入单行
INSERT INTO sensor_data (time, sensor_id, temperature, humidity)
VALUES (NOW(), 1, 25.5, 60.0);

-- 批量插入
INSERT INTO sensor_data (time, sensor_id, temperature, humidity) VALUES
    (NOW() - INTERVAL '1 hour', 1, 24.8, 58.5),
    (NOW() - INTERVAL '2 hours', 1, 24.5, 57.2),
    (NOW() - INTERVAL '3 hours', 2, 26.1, 61.0);

-- COPY导入
COPY sensor_data FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER true);

-- 标准查询
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
ORDER BY time DESC;

-- 时间桶聚合
SELECT 
    time_bucket('1 hour', time) AS bucket,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY bucket, sensor_id
ORDER BY bucket DESC;

-- 设备级别查询
SELECT * FROM sensor_data
WHERE sensor_id = 1
  AND time > NOW() - INTERVAL '7 days';

-- 分区裁剪示例
EXPLAIN ANALYZE
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days';

-- 查看执行计划中的分片过滤
-- Chunk Filter: (time > NOW() - INTERVAL '7 days')
-- 只扫描相关分片，不扫描全表

-- 强制分区裁剪
SET timescaledb.enable_chunk_append = on;

-- 查看裁剪效果
SELECT 
    chunk_name,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE range_end > NOW() - INTERVAL '7 days';

-- 时间索引（自动创建）
-- create_hypertable会自动在时间列创建索引

-- 设备ID索引
CREATE INDEX idx_sensor_id ON sensor_data (sensor_id);

-- 复合索引
CREATE INDEX idx_sensor_time ON sensor_data (sensor_id, time DESC);

-- 条件索引
CREATE INDEX idx_high_temp ON sensor_data (time)
WHERE temperature > 30;

-- GIN索引（JSON列）
CREATE TABLE events (
    time TIMESTAMPTZ NOT NULL,
    data JSONB
);

SELECT create_hypertable('events', 'time');
CREATE INDEX idx_data_gin ON events USING GIN (data);

索引策略建议：

时间列
├── 自动创建
├── 默认B-tree
└── 适合范围查询

设备ID列
├── 建议创建
├── 配合时间列
└── 适合设备查询

复合索引
├── (device_id, time DESC)
├── 覆盖常见查询
└── 注意顺序

特殊索引
├── 条件索引 - 特定条件
├── GIN索引 - JSON/数组
└── 部分索引 - 减少大小

分区间隔选择指南：

写入频率          推荐间隔
─────────────────────────────
每秒多条          1-6小时
每分钟多条        6小时-1天
每小时多条        1-7天
每天多条          7-30天

考虑因素
├── 数据量 - 每个分片建议1-50GB
├── 查询模式 - 时间范围查询
├── 保留策略 - 数据生命周期
└── 压缩策略 - 压缩时机

空间分区选择指南：

适合空间分区
├── 多设备并发写入
├── 设备级别查询
├── 数据量大（TB级）
└── 高并发场景

不适合空间分区
├── 单一数据源
├── 全局聚合查询
├── 数据量小（GB级）
└── 低并发场景

分区数量建议
├── 2-4倍CPU核心数
├── 不超过设备数量
└── 根据写入并发调整

-- 好的超表设计
CREATE TABLE well_designed (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    metric_type SMALLINT,
    value DOUBLE PRECISION,
    tags JSONB
);

SELECT create_hypertable(
    'well_designed',
    'time',
    partitioning_column => 'device_id',
    number_partitions => 4,
    chunk_time_interval => INTERVAL '1 day'
);

-- 创建合适的索引
CREATE INDEX idx_device_time ON well_designed (device_id, time DESC);
CREATE INDEX idx_metric ON well_designed (metric_type, time DESC);

-- 设计原则
-- 1. 时间列使用TIMESTAMPTZ
-- 2. 标签列使用INTEGER或VARCHAR
-- 3. 数值列使用DOUBLE PRECISION
-- 4. 元数据使用JSONB
-- 5. 避免过多列

-- 创建自定义时间分区
SELECT create_hypertable(
    'sensor_data',
    'time',
    chunk_time_interval => INTERVAL '1 month'
);

-- 手动创建分片
SELECT create_chunk(
    'sensor_data',
    ARRAY['2024-01-01 00:00:00+00'::timestamptz, '2024-02-01 00:00:00+00'::timestamptz]
);

-- 查看分片大小
SELECT 
    hypertable_name,
    chunk_name,
    pg_size_pretty(total_bytes) as size
FROM chunk_relation_size('sensor_data')
ORDER BY total_bytes DESC;

-- 合并小分片
SELECT merge_chunks(
    'sensor_data',
    older_than => INTERVAL '30 days'
);

-- 分割大分片
SELECT split_chunk('sensor_data_chunk_1');

-- 创建超表副本
CREATE TABLE sensor_data_copy (LIKE sensor_data INCLUDING ALL);

SELECT create_hypertable(
    'sensor_data_copy',
    'time'
);

-- 复制数据
INSERT INTO sensor_data_copy
SELECT * FROM sensor_data;

-- 超表统计
SELECT * FROM hypertable_detailed_size('sensor_data');

-- 分片统计
SELECT 
    hypertable_name,
    count(*) as chunk_count,
    sum(total_bytes) as total_size
FROM chunk_relation_size('sensor_data')
GROUP BY hypertable_name;

-- 查看压缩统计
SELECT 
    hypertable_name,
    chunk_name,
    before_compression_total_bytes,
    after_compression_total_bytes,
    100.0 * (before_compression_total_bytes - after_compression_total_bytes) / before_compression_total_bytes as compression_ratio
FROM chunk_compression_stats('sensor_data');

-- 查看查询计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days';

-- 查看索引使用
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'sensor_data';

-- 查看缓存命中率
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables
WHERE relname = 'sensor_data';

-- 错误：表已有数据
-- 解决：使用migrate_data参数
SELECT create_hypertable(
    'sensor_data',
    'time',
    migrate_data => TRUE
);

-- 错误：时间列没有NOT NULL约束
-- 解决：添加约束
ALTER TABLE sensor_data ALTER COLUMN time SET NOT NULL;
SELECT create_hypertable('sensor_data', 'time');

-- 查看分片数量
SELECT count(*) FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data';

-- 解决：增大分区间隔
SELECT set_chunk_time_interval('sensor_data', INTERVAL '7 days');

-- 合并旧分片
SELECT merge_chunks(
    'sensor_data',
    older_than => INTERVAL '30 days'
);

-- 检查索引
\d sensor_data

-- 添加合适的索引
CREATE INDEX idx_device_time ON sensor_data (device_id, time DESC);

-- 更新统计信息
ANALYZE sensor_data;

-- 检查执行计划
EXPLAIN ANALYZE SELECT ...;