PostgreSQL如何实现自动清理旧数据的归档策略？

业务系统跑上两三年，订单表、日志表动辄几亿行，磁盘占用一路上涨，慢查询也跟着变多。最直接的想法是定期DELETE旧数据，但大批量DELETE会带来长事务、锁竞争、表膨胀和WAL暴增等一系列问题， vacuum还跟不上节奏，往往清理完性能反而更差。更好的做法是把旧数据先归档再清理，PostgreSQL的分区表机制配合定时任务，可以做到近乎瞬间的数据下线。这篇文章就来完整讲讲这套方案的落地细节。

为什么大批量DELETE不是好选择

很多人第一反应是写一个定时任务，每天凌晨执行DELETE FROM log_table WHERE create_time < now() - interval '180 days'。在测试环境数据量小的时候没什么问题，但生产环境一旦要删几百万行，问题就来了。DELETE是逐行标记删除，被删的行并不会立刻释放磁盘空间，而是变成死元组等待autovacuum回收。短时间内产生海量死元组，autovacuum可能要跑很久，期间表和索引持续膨胀，查询计划恶化。

其次，一个大的DELETE事务会持有行锁直到事务提交，如果和其他业务事务产生冲突，容易引发锁等待甚至死锁。同时删除操作产生的WAL日志量非常大，如果配置了流复制或者归档备份，网络带宽和归档存储都会承受明显压力。极端情况下，长事务还会阻碍vacuum清理旧版本行，导致整个数据库的膨胀加剧。

即使把DELETE拆成小批量循环执行可以缓解部分问题，但本质上仍然是逐行处理的成本，清理速度和数据量正相关。而分区表方案下，清理一个分区只需要DETACH加DROP，耗时和数据量无关，这是两种方案最本质的区别。

基于分区表的自动归档方案

PostgreSQL 10之后的原生分区表已经相当成熟，推荐按时间范围分区，比如按月。核心思路是：热数据放在主表所在的表空间，旧数据分区先DETACH脱离主表，导出归档后再DROP，整个过程对业务几乎无感。

先看建表语句的写法：

-- 创建按月分区的父表
CREATE TABLE t_order (
    id           bigserial,
    order_no     varchar(64) NOT NULL,
    amount       numeric(12,2),
    status       smallint NOT NULL DEFAULT 0,
    create_time  timestamp NOT NULL
) PARTITION BY RANGE (create_time);

-- 创建索引，会自动应用到所有分区
CREATE INDEX idx_order_create_time ON t_order (create_time);
CREATE INDEX idx_order_order_no ON t_order (order_no);

-- 创建具体分区
CREATE TABLE t_order_2025_01 PARTITION OF t_order
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE t_order_2025_02 PARTITION OF t_order
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

归档清理的核心流程分三步。第一步用ALTER TABLE ... DETACH PARTITION把旧分区从主表摘下来，注意在PostgreSQL 14及以上DETACH会等待所有正在访问该分区的事务结束，建议用CONCURRENTLY模式避免阻塞业务，但CONCURRENTLY不能在事务块中执行。第二步用COPY把分区数据导出成文件并压缩。第三步确认归档完整后DROP掉这个分区，磁盘空间立即释放。整个过程如下：

-- 第一步：脱离分区（PG14+建议使用 CONCURRENTLY）
ALTER TABLE t_order DETACH PARTITION t_order_2025_01;

-- 第二步：导出并压缩归档
-- 命令行执行：psql -c "\copy t_order_2025_01 to program 'gzip > /archive/t_order_2025_01.csv.gz' csv"

-- 第三步：确认归档后删除分区，空间立即回收
DROP TABLE t_order_2025_01;

这套流程可以封装成shell脚本，由crontab或者pg_cron扩展每天调度。DETACH加DROP的总耗时通常在毫秒到秒级，和分区内有多少数据基本无关，这就是分区方案相对DELETE的巨大优势。

分区维护：触发器与pg_partman两种方式

分区建好后，新数据持续写入，必须保证未来的分区提前存在，否则插入会直接报错。维护方式有两种。一种是自建触发器函数，在插入时检查目标分区是否存在，不存在就自动创建：

CREATE OR REPLACE FUNCTION fn_create_partition()
RETURNS trigger AS $$
DECLARE
    partition_name text;
    start_date date;
    end_date date;
BEGIN
    start_date := date_trunc('month', NEW.create_time)::date;
    end_date   := (start_date + interval '1 month')::date;
    partition_name := 't_order_' || to_char(start_date, 'YYYY_MM');
    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = partition_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF t_order FOR VALUES FROM (%L) TO (%L)',
            partition_name, start_date, end_date
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_create_partition
BEFORE INSERT ON t_order
FOR EACH ROW EXECUTE FUNCTION fn_create_partition();

这种方式的优点是不依赖外部扩展，逻辑完全可控；缺点是每行插入都要执行一次函数判断，高写入场景下有一定开销，而且并发插入时可能出现两个会话同时尝试建分区的冲突，需要捕获重复建表的异常再做容错。

更省心的方式是使用社区广泛使用的pg_partman扩展。它不仅能自动预建未来分区，还内置了保留策略，可以按配置自动DETACH并清理过期分区，配合pg_cron定时调度即可实现全自动生命周期管理：

-- 安装后创建父表并配置
SELECT partman.create_parent(
    p_parent_table := 'public.t_order',
    p_control := 'create_time',
    p_interval := '1 month'
);

-- 数据保留12个月，过期分区自动DETACH
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = true
WHERE parent_table = 'public.t_order';

-- 用pg_cron每天凌晨2点执行维护
SELECT cron.schedule('partman-maint', '0 2 * * *',
    $$SELECT partman.run_maintenance('public.t_order')$$);

其中retention_keep_table设为true表示过期分区只DETACH不DROP，正好留给归档脚本先导出数据，导出确认后再手动或由脚本DROP，安全性和自动化可以兼顾。

归档存储与监控保障

归档导出的文件建议按日期命名并用gzip或zstd压缩，zstd在压缩率和速度上的平衡更好。归档文件落盘后不要只存一份，可以同步到对象存储或备份服务器。如果后续还有查询冷数据的需求，可以建一个结构相同的归档库，把gzip文件重新COPY进去，或者在需要时用file_fdw挂载只读查询。

监控方面至少要关注三类指标：一是最老分区的数据时间跨度，确保清理策略按预期执行，没有出现分区积压；二是归档任务的执行日志和退出码，导出失败时绝不能执行DROP，脚本里要做文件行数与分区行数的比对校验；三是磁盘空间趋势，提前预判扩容需求。可以写一个简单的检查SQL放到监控系统中：

-- 查看各分区大小与最早数据时间
SELECT c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       min(p.create_time) AS min_time
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
JOIN pg_parent(p) p ON true
WHERE i.inhparent = 'public.t_order'::regclass
GROUP BY c.relname
ORDER BY min_time;

常见故障也要心里有数。DETACH卡住通常是Concurrently模式在等待长事务，用pg_stat_activity查出阻塞源处理即可；插入报错说分区不存在，多半是预建分区的脚本停跑了；归档后发现数据缺失，重点检查COPY命令的编码和分隔符设置是否与导出一致。另外提醒一点，分区键必须包含在主键和唯一约束里，这是PostgreSQL分区表的硬性限制，设计表结构时就要规划好。

整体来看，分区表加自动归档的方案前期建表设计要多花些心思，但换来的是零膨胀的秒级清理、平滑的冷热分离和可控的存储成本，对于持续增长的业务表来说是最值得投入的一项基础建设。

PostgreSQL归档分区表自动清理旧数据修改时间：2026-09-11 17:40:41