时间分区 #

时间分区原理：

时间轴
─────────────────────────────────────────────►
│         │         │         │         │
│ Chunk 1 │ Chunk 2 │ Chunk 3 │ Chunk 4 │
│ 1月1-7日│ 1月8-14日│1月15-21日│1月22-28日│
│         │         │         │         │

每个分片存储固定时间范围的数据
├── 自动创建
├── 独立管理
└── 高效查询

时间分区优势：

查询性能
├── 时间范围查询优化
├── 分区裁剪
├── 索引效率
└── 并行查询

写入性能
├── 热点分散
├── 索引维护优化
├── 并发写入
└── WAL优化

数据管理
├── 数据生命周期管理
├── 自动清理旧数据
├── 分级存储
└── 备份恢复优化

-- 创建表
CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    temperature DOUBLE PRECISION
);

-- 创建超表并设置分区间隔
SELECT create_hypertable(
    'sensor_data',
    'time',
    chunk_time_interval => INTERVAL '7 days'
);

-- 查看当前分区间隔
SELECT 
    hypertable_name,
    chunk_interval
FROM timescaledb_information.dimensions;

-- 修改分区间隔
SELECT set_chunk_time_interval('sensor_data', INTERVAL '1 day');

-- 使用微秒设置
SELECT set_chunk_time_interval('sensor_data', 86400000000);  -- 1天

-- 修改只影响新创建的分片
-- 已有分片不受影响

分区间隔选择指南：

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

数据量考虑
├── 每个分片1-50GB
├── 活跃分片不超过1000个
└── 考虑压缩和保留策略

查询模式考虑
├── 常用时间范围
├── 聚合时间粒度
└── 分区裁剪效率

-- 高频数据（每秒多条）
CREATE TABLE high_freq_data (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER,
    value DOUBLE PRECISION
);

SELECT create_hypertable(
    'high_freq_data',
    'time',
    chunk_time_interval => INTERVAL '1 hour'
);

-- 中频数据（每分钟多条）
CREATE TABLE medium_freq_data (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER,
    value DOUBLE PRECISION
);

SELECT create_hypertable(
    'medium_freq_data',
    'time',
    chunk_time_interval => INTERVAL '1 day'
);

-- 低频数据（每小时多条）
CREATE TABLE low_freq_data (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER,
    value DOUBLE PRECISION
);

SELECT create_hypertable(
    'low_freq_data',
    'time',
    chunk_time_interval => INTERVAL '7 days'
);

-- 单一时间维度分区
CREATE TABLE simple_data (
    time TIMESTAMPTZ NOT NULL,
    value DOUBLE PRECISION
);

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

-- 时间维度是必需的
-- 所有超表必须有时间列

-- 时间+空间双维度分区
CREATE TABLE multi_dim_data (
    time TIMESTAMPTZ NOT NULL,
    device_id INTEGER NOT NULL,
    value DOUBLE PRECISION
);

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

-- 查看维度信息
SELECT 
    dimension_number,
    column_name,
    column_type,
    time_interval,
    num_slices
FROM timescaledb_information.dimensions
WHERE hypertable_name = 'multi_dim_data';

维度选择指南：

单一时间维度
├── 适用场景
│   ├── 单一数据源
│   ├── 全局聚合查询
│   ├── 数据量小
│   └── 低并发场景
└── 优势
    ├── 管理简单
    ├── 查询优化
    └── 存储效率

时间+空间维度
├── 适用场景
│   ├── 多数据源
│   ├── 设备级别查询
│   ├── 大规模数据
│   └── 高并发场景
└── 优势
    ├── 并发写入
    ├── 查询并行
    └── I/O分散

-- 时间范围查询（自动裁剪）
EXPLAIN ANALYZE
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days';

-- 执行计划显示
-- -> Custom Scan (ChunkAppend)
--    -> Index Scan on _hyper_1_10_chunk  -- 只扫描相关分片
--    -> Index Scan on _hyper_1_11_chunk
--    -> Index Scan on _hyper_1_12_chunk

-- 查看扫描的分片
SELECT 
    chunk_name,
    range_start,
    range_end
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
  AND range_end > NOW() - INTERVAL '7 days'
  AND range_start < NOW();

-- 有效的时间条件（触发裁剪）
SELECT * FROM sensor_data
WHERE time > '2024-01-01';

SELECT * FROM sensor_data
WHERE time BETWEEN '2024-01-01' AND '2024-01-31';

SELECT * FROM sensor_data
WHERE time >= '2024-01-01' AND time < '2024-02-01';

-- 无效的时间条件（不触发裁剪）
SELECT * FROM sensor_data
WHERE EXTRACT(MONTH FROM time) = 1;  -- 函数阻止裁剪

SELECT * FROM sensor_data
WHERE to_char(time, 'YYYY-MM') = '2024-01';  -- 函数阻止裁剪

-- 不推荐：使用函数
SELECT * FROM sensor_data
WHERE EXTRACT(YEAR FROM time) = 2024;

-- 推荐：使用范围
SELECT * FROM sensor_data
WHERE time >= '2024-01-01' AND time < '2025-01-01';

-- 不推荐：OR条件
SELECT * FROM sensor_data
WHERE time > '2024-01-01' OR sensor_id = 1;

-- 推荐：AND条件
SELECT * FROM sensor_data
WHERE time > '2024-01-01' AND sensor_id = 1;

-- 使用time_bucket优化
SELECT 
    time_bucket('1 day', time) AS day,
    AVG(value)
FROM sensor_data
WHERE time > NOW() - INTERVAL '30 days'
GROUP BY day;

-- 查看所有分区
SELECT 
    hypertable_name,
    chunk_name,
    range_start,
    range_end,
    is_compressed
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data'
ORDER BY range_start;

-- 查看分区间隔
SELECT 
    hypertable_name,
    column_name,
    time_interval
FROM timescaledb_information.dimensions;

-- 查看分区大小
SELECT 
    chunk_name,
    pg_size_pretty(total_bytes) as size,
    range_start,
    range_end
FROM chunk_relation_size('sensor_data') c
JOIN timescaledb_information.chunks ch
    ON c.chunk_name = ch.chunk_name
ORDER BY range_start DESC;

-- 按时间统计分区
SELECT 
    date_trunc('month', range_start) as month,
    count(*) as chunk_count,
    pg_size_pretty(sum(total_bytes)) as total_size
FROM chunk_relation_size('sensor_data') c
JOIN timescaledb_information.chunks ch
    ON c.chunk_name = ch.chunk_name
GROUP BY date_trunc('month', range_start)
ORDER BY month;

-- 分区大小分布
SELECT 
    CASE 
        WHEN total_bytes < 1073741824 THEN '< 1GB'
        WHEN total_bytes < 10737418240 THEN '1-10GB'
        WHEN total_bytes < 53687091200 THEN '10-50GB'
        ELSE '> 50GB'
    END as size_range,
    count(*) as chunk_count
FROM chunk_relation_size('sensor_data')
GROUP BY size_range
ORDER BY size_range;

-- 调整分区间隔
SELECT set_chunk_time_interval('sensor_data', INTERVAL '14 days');

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

-- 分割大分区
SELECT split_chunk('_hyper_1_10_chunk');

-- 添加保留策略
SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '90 days'
);

-- 查看保留策略
SELECT 
    job_id,
    schedule_interval,
    config
FROM timescaledb_information.jobs
WHERE proc_name = 'policy_retention';

-- 手动删除旧分区
SELECT drop_chunks(
    'sensor_data',
    older_than => INTERVAL '90 days'
);

-- 热数据（最近7天）
-- 存储在SSD上，不压缩

-- 温数据（7-30天）
-- 压缩存储
SELECT add_compression_policy(
    'sensor_data',
    INTERVAL '7 days'
);

-- 冷数据（30-90天）
-- 压缩+低频访问
SELECT add_retention_policy(
    'sensor_data',
    INTERVAL '90 days'
);

-- 创建连续聚合
CREATE MATERIALIZED VIEW daily_stats
WITH (timescaledb.continuous) AS
SELECT 
    time_bucket('1 day', time) AS day,
    sensor_id,
    AVG(temperature) AS avg_temp,
    MAX(temperature) AS max_temp,
    MIN(temperature) AS min_temp
FROM sensor_data
GROUP BY day, sensor_id;

-- 连续聚合自动维护
-- 当原始分区更新时自动刷新

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

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

-- 复合索引
CREATE INDEX idx_sensor_time ON sensor_data (sensor_id, time DESC)
WHERE time > NOW() - INTERVAL '30 days';

-- 部分索引（减少大小）
CREATE INDEX idx_recent ON sensor_data (time DESC)
WHERE time > NOW() - INTERVAL '7 days';

-- 使用时间范围
SELECT * FROM sensor_data
WHERE time BETWEEN '2024-01-01' AND '2024-01-31';

-- 使用time_bucket
SELECT 
    time_bucket('1 hour', time) AS hour,
    AVG(temperature)
FROM sensor_data
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour;

-- 使用LIMIT
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour'
ORDER BY time DESC
LIMIT 100;

-- 批量插入
INSERT INTO sensor_data (time, sensor_id, temperature) VALUES
    (NOW(), 1, 25.5),
    (NOW(), 2, 26.0),
    (NOW(), 3, 24.8);

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

-- 临时关闭自动压缩（大批量导入）
SELECT remove_compression_policy('sensor_data');
-- 导入数据
-- 重新添加压缩策略
SELECT add_compression_policy('sensor_data', INTERVAL '7 days');

-- 检查分区大小分布
SELECT 
    hypertable_name,
    count(*) as chunk_count,
    pg_size_pretty(avg(total_bytes)::bigint) as avg_size,
    pg_size_pretty(min(total_bytes)::bigint) as min_size,
    pg_size_pretty(max(total_bytes)::bigint) as max_size
FROM chunk_relation_size('sensor_data')
GROUP BY hypertable_name;

-- 检查分区裁剪效率
EXPLAIN ANALYZE
SELECT * FROM sensor_data
WHERE time > NOW() - INTERVAL '7 days';

-- 检查压缩比例
SELECT 
    count(*) as total,
    sum(CASE WHEN is_compressed THEN 1 ELSE 0 END) as compressed,
    round(100.0 * sum(CASE WHEN is_compressed THEN 1 ELSE 0 END) / count(*), 2) as rate
FROM timescaledb_information.chunks
WHERE hypertable_name = 'sensor_data';

-- 分片数量告警
SELECT 
    hypertable_name,
    count(*) as chunk_count,
    CASE 
        WHEN count(*) > 1000 THEN 'WARNING: Too many chunks'
        WHEN count(*) > 500 THEN 'CAUTION: Many chunks'
        ELSE 'OK'
    END as status
FROM timescaledb_information.chunks
GROUP BY hypertable_name;

-- 分片大小告警
SELECT 
    chunk_name,
    pg_size_pretty(total_bytes) as size,
    CASE 
        WHEN total_bytes > 53687091200 THEN 'WARNING: Chunk too large'
        WHEN total_bytes < 1073741824 THEN 'WARNING: Chunk too small'
        ELSE 'OK'
    END as status
FROM chunk_relation_size('sensor_data')
WHERE total_bytes > 53687091200 OR total_bytes < 1073741824;

分区设计原则：

时间列选择
├── 使用TIMESTAMPTZ
├── 添加NOT NULL约束
├── 考虑时区问题
└── 选择合适的时间精度

分区间隔选择
├── 根据数据量
├── 根据查询模式
├── 考虑保留策略
└── 考虑压缩策略

空间分区选择
├── 根据并发需求
├── 根据查询模式
├── 考虑设备数量
└── 考虑写入频率

-- 问题1：分片过多
-- 解决：增大分区间隔
SELECT set_chunk_time_interval('sensor_data', INTERVAL '14 days');

-- 问题2：分片大小不均
-- 解决：调整分区间隔或合并分片
SELECT merge_chunks('sensor_data', older_than => INTERVAL '30 days');

-- 问题3：查询性能差
-- 解决：确保时间条件、添加索引
CREATE INDEX idx_time ON sensor_data (time DESC);
ANALYZE sensor_data;