PostgreSQL时序数据冷热分离实战
digoal约 6 分钟本期播客
PostgreSQL时序数据冷热分离实战
参考: https://www.snowflake.com/en/engineering-blog/postgres-time-series-iceberg/
告别表膨胀，拥抱Iceberg数据湖，DBA必看
作为DBA，你是否面临这样的困境：时序数据表无限增长，查询越来越慢，存储成本飙升？传统分区虽然有用，但维护麻烦，冷数据依然占用宝贵SSD。今天，我们带来一套100%开源方案：pg_partman + pg_lake + pg_incremental，实现自动化冷热分离，让PostgreSQL也能优雅处理海量时序数据。
PostgreSQL 2026 年度大戏来了, 扫海报中的二维码报名, 选择早鸟或通票(都含午餐和周边礼品), 可私信我要优惠码, 数量有限先到先得!
一、三个扩展，各显神通
这套组合拳由Snowflake的Postgres团队维护，兼顾工程质量和企业级需求：
| pg_partman | |
| pg_lake | |
| pg_incremental |
二、实战：IoT传感器数据管理
让我们通过一个完整的物联网温度传感器数据场景，演示这套方案的威力。
1. 创建本地分区表（热数据）
首先创建按时间分区的本地表，存储最近的热数据：
-- 创建父表
CREATETABLE sensor_data (
reading_id BIGSERIAL,
reading_time TIMESTAMPTZ NOTNULL,
sensor_id INT,
temperature DECIMAL(5,2),
PRIMARY KEY (reading_id, reading_time)
) PARTITIONBYRANGE (reading_time);
-- 启用pg_partman并创建初始分区
CREATE EXTENSION pg_partman;
SELECT create_parent(
p_parent_table => 'public.sensor_data',
p_control => 'reading_time',
p_type => 'range',
p_interval => '1 day',
p_premake => 7
);
-- 自动维护分区（可在后台运行）
UPDATE part_config
SET retention_interval = '7 days',
retention_keep_table = false
WHERE parent_table = 'public.sensor_data';
为什么这样做？ 时间分区表让PostgreSQL可以高效地只扫描相关分区，避免全表扫描，同时删除旧分区变得极其快速（秒级DROP）。
2. 生成示例数据
插入10,000行过去60天的传感器数据：
INSERTINTO sensor_data (reading_time, sensor_id, temperature)
SELECT
now() - (random() * interval'60 days'),
(random() * 100)::int + 1,
(random() * 30 + 10)::decimal(5,2)
FROM generate_series(1, 10000);
3. 创建Iceberg目标表（冷数据）
使用pg_lake在S3上创建Iceberg表，存储冷数据：
CREATE EXTENSION pg_lake;
CREATEFOREIGNTABLE sensor_data_iceberg (
reading_id BIGINT,
reading_time TIMESTAMPTZ,
sensor_id INT,
temperature DECIMAL(5,2)
) SERVER pg_lake_server
OPTIONS (
path's3://my-bucket/iceberg/sensor_data',
format'iceberg',
partition_by 'month(reading_time)'-- 关键优化！
);
亮点：通过partition_by = 'month(reading_time)'，后续查询如果带有reading_time过滤条件，pg_lake会自动只请求S3上对应的月度文件夹，避免全桶扫描，大幅降低S3出口成本和提升性能。
4. 配置pg_incremental自动增量同步
CREATE EXTENSION pg_incremental;
-- 创建增量处理任务，每分钟同步一次
SELECT incremental_create(
p_source_table => 'sensor_data',
p_target_table => 'sensor_data_iceberg',
p_interval => '1 minute',
p_initial_checkpoint => now() - interval'60 days'
);
pg_incremental内部通过追踪序列号或时间戳，确保每个批次的数据只处理一次，且与写入事务隔离，保证一致性。
5. 自动删除本地旧分区
pg_partman根据配置自动删除超过7天的本地分区：
-- 查看分区维护状态
SELECT * FROM part_config WHERE parent_table = 'public.sensor_data';
此时，热数据（最近7天）留在本地SSD，冷数据（7天前）已自动迁移到S3上的Iceberg表。
6. 统一查询冷热数据
现在我们可以无缝查询任意时间范围的数据：
-- 查询最近7天的数据（从本地表）
SELECT * FROM sensor_data
WHERE reading_time > now() - interval'7 days'
LIMIT10;
-- 查询历史数据（自动路由到Iceberg）
SELECT * FROM sensor_data_iceberg
WHERE reading_time < now() - interval'30 days'
AND sensor_id = 42;
-- 聚合查询：过去60天每月的平均温度
SELECT date_trunc('month', reading_time) ASmonth,
AVG(temperature) AS avg_temp
FROM (
SELECT * FROM sensor_data WHERE reading_time > now() - interval'7 days'
UNIONALL
SELECT * FROM sensor_data_iceberg WHERE reading_time <= now() - interval'7 days'
) combined
WHERE reading_time > now() - interval'60 days'
GROUPBYmonth
ORDERBYmonth;
性能对比：本地SSD查询最近数据亚毫秒级，S3上历史数据查询也仅需数百毫秒（得益于Iceberg的分区裁剪和列式存储）。
三、为什么这套方案有效？第一性原理分析
从数据库内核视角看，这套组合完美契合时序数据访问的物理规律：
存储层次与成本
热数据：存储在本地NVMe SSD，追求极低延迟（读/写）。 冷数据：卸载到S3对象存储，单位存储成本降低80%以上，且可独立扩展。 这正是分层存储思想在PostgreSQL上的落地。 访问模式优化
近期数据：高频写入和查询，分区表配合B-Tree索引，充分利用PostgreSQL的缓冲池和MVCC机制（参见Storage Management）。 历史数据：低频分析查询，Iceberg的列式布局和分区裁剪减少扫描量，利用S3的并行读取能力。 扩展机制赋能
PostgreSQL的FDW（外部数据封装）和扩展API，使pg_lake能将Iceberg表映射为本地表，查询优化器能下推过滤条件（如分区裁剪）。 pg_incremental基于PostgreSQL的事务日志（WAL）或序列号实现增量抽取，保证Exactly-Once语义。 自动化运维
pg_partman自动管理分区生命周期，避免表无限膨胀导致的性能下降和VACUUM压力。 增量同步与分区删除解耦，确保数据不丢失、不重复。
四、方案的边界与替代选择
没有银弹，这套方案也有其适用场景和局限性：
如果条件崩塌：当数据量突破PB级、查询模式多变且要求亚秒级响应时，可考虑：
专用时序数据库：TimescaleDB（基于PostgreSQL，支持超表、连续聚合）、InfluxDB 数据湖原生方案：Apache Druid、ClickHouse，直接查询Iceberg 云原生服务：Snowflake、BigQuery，无缝集成Iceberg
五、总结与展望
通过pg_partman + pg_lake + pg_incremental，我们构建了一个供应商中立、100%开源、高性能且成本可控的时序数据处理栈。它让PostgreSQL从单纯的OLTP数据库，进化为能优雅处理海量时序数据的统一数据平台。
未来，随着数据湖与数据库界限的模糊，PostgreSQL作为“统一查询引擎”的角色将愈发重要。这套组合正是这一趋势的先行者。
你的时序数据管理有什么痛点？欢迎在评论区留言讨论。如果觉得本文对你有帮助，点个“在看”分享给更多同行！