Citus分布式PostgreSQL集群的时间序列数据处理示例
在时序数据处理场景中，应用程序通常需要快速访问最新数据并定期清理历史记录。这种模式在监控系统或物联网场景中尤为常见。
PostgreSQL原生通过范围分区技术将大表拆分为多个子表，每个子表存储特定时间区间的记录。这种方式相比单表操作能显著提升数据过期效率，因为分区删除仅需操作文件系统元数据，而非扫描全表。
分区带来的性能优势体现在：
- 索引效率：每个分区的索引规模缩小，提升内存命中率
- 写入性能：索引维护开销随数据量减少
- 查询优化：可精准定位时间范围的分区
当需要处理海量时序数据时，Citus提供分布式扩展方案。通过将表按实体ID分片，再对每个分片进行时间范围分区，可实现水平扩展。
示例展示如何构建分布式时序表：
-- 创建基础表结构
CREATE TABLE time_series_data (
id BIGINT,
type TEXT,
is_public BOOLEAN,
entity_id BIGINT,
payload JSONB,
details JSONB,
user JSONB,
org JSONB,
record_time TIMESTAMP
) PARTITION BY RANGE (record_time);
-- 分片配置
SELECT create_shard_table('time_series_data', 'entity_id');
-- 自动创建时间分区
SELECT generate_partition_ranges(
table_name := 'time_series_data',
interval_size := '1 month',
end_date := NOW() + '12 months'
);
维护任务建议使用pg_cron定时执行：
-- 创建分区任务
SELECT cron.schedule('create-partitions', '0 0 1 * *', $$
SELECT generate_partition_ranges(
table_name := 'time_series_data',
interval_size := '1 month',
end_date := NOW() + '12 months'
)
$$);
-- 清理过期分区
SELECT cron.schedule('cleanup-partitions', '0 0 1 * *', $$
CALL remove_old_partitions(
'time_series_data',
NOW() - INTERVAL '12 months'
)
$$);
对于历史数据归档，可结合列式存储优化：
-- 创建列式存储表
CREATE TABLE columnar_data (LIKE time_series_data)
PARTITION BY RANGE (record_time);
-- 初始化时间分区
SELECT generate_partition_ranges(
table_name := 'columnar_data',
interval_size := '2 hours',
start_date := '2015-01-01 00:00:00',
end_date := '2015-01-01 08:00:00'
);
-- 转换历史分区存储格式
CALL convert_partition_storage(
'columnar_data',
'2015-01-01 06:00:00',
'columnar'
);
通过这种分层存储策略，最新数据保持行存格式支持更新，历史数据转为列存格式实现高效压缩。查询时无需关注存储格式差异，系统会自动处理跨分区查询。
