Postgres 10按订阅周期分区自动清理过期数据方案咨询

原有方案合理性评估 #

可取之处 #

核心思路是对的：通过分区DROP操作实现数据清理确实是O(1)效率，远高于批量DELETE，不会产生表膨胀，存储空间释放也更及时，完全符合你快速清理过期数据的需求。

存在的明显缺陷 #

• 核心逻辑bug：按订阅周期划分分区后，整体删除重建的操作会误删该分区内未到期的有效数据。比如7天周期的分区里同时存了1天前和6天前写入的7天有效期数据，你每7天删一次整个分区的话，1天前写入的还有6天有效期的数据会被一同清理，不符合数据保留要求。
• 维护成本高：22个分区虽然不会带来显著性能问题，但后续只要新增订阅周期，就要同步新增分区、调整定时任务，迭代成本高。

更优方案推荐 #

推荐使用按数据过期时间的范围分区方案，逻辑如下：

• 给devices表新增expire_at timestamp字段，写入数据时直接根据用户订阅周期计算好该条数据的过期时间写入该字段
• 父表按expire_at做范围分区，按周/月粒度拆分分区，比如分区命名为devices_20240601，对应存储过期时间在2024-06-01到2024-06-07之间的所有数据
• 清理时只需要判断分区的最大过期时间早于当前时间，就可以直接DROP整个分区，不会误删任何有效数据，且不需要重建同名分区，只需要提前预创建未来1-2个周期的新分区即可，维护逻辑更简单

这个方案同时兼容所有订阅周期的设备数据，不需要为不同订阅周期单独建分区，分区数量稳定在个位数级别，路由效率更高。

Postgres 10 自动分区管理实现方式 #

Postgres 10 没有内置的自动分区管理能力，需要手动实现定时任务完成操作：

如果你坚持使用原有按订阅周期分区的方案 #

1. 先编写PL/pgSQL存储过程实现指定周期分区的删建逻辑，示例代码：

CREATE OR REPLACE FUNCTION rebuild_devices_partition(period text) RETURNS void AS $$
BEGIN
    -- 删除旧分区
    EXECUTE format('DROP TABLE IF EXISTS devices_%I', period);
    -- 创建新分区，注意替换对应的列表值、约束逻辑和父表对齐
    EXECUTE format('CREATE TABLE devices_%I PARTITION OF devices FOR VALUES IN (%L)', period, period);
    -- Postgres 10 不支持父表索引自动继承，需要单独创建子表索引，按你实际需要的索引调整
    EXECUTE format('CREATE INDEX idx_devices_%I_device_id ON devices_%I (device_id)', period, period);
END;
$$ LANGUAGE plpgsql;

2. 配置定时任务触发执行：

• 可以安装pg_cron插件，给每个订阅周期配置对应间隔的定时任务，比如7天周期的任务配置为SELECT cron.schedule('rebuild-7d-partition', '0 0 * * 0', $$SELECT rebuild_devices_partition('7d')$$);
• 无法安装插件的场景可以用系统crontab定时执行psql命令调用存储过程，比如每周日零点执行：0 0 * * 0 postgres psql -d 你的数据库名 -c "SELECT rebuild_devices_partition('7d')"

如果你使用推荐的按过期时间范围分区的方案 #

存储过程逻辑调整为：

1. 每次执行时预创建未来2周的新分区
2. 扫描所有现有分区，删除分区最大过期时间早于当前时间的旧分区
3. 每周执行一次该存储过程即可，不需要为不同订阅周期单独配置任务

注意：Postgres 10 的声明式分区功能还不完善，不支持自动兜底的默认分区，写入数据如果找不到对应分区会直接报错，需要确保分区覆盖所有可能写入的数据范围。

内容的提问来源于stack exchange，提问作者user1583803