Postgres无timestamp/date列的表如何统计删除1年以上旧数据及性能问题

一、1年以上旧记录统计方法 #

你当前没有显式时间列的场景下，可通过两种方式判定记录写入时间，再完成统计：

方式1：通过Postgres内置系统列判定（推荐） #

Postgres所有表默认带有隐藏系统列xmin，存储插入该行的事务ID，结合事务提交时间可以反推记录写入时间：

• 先开启事务提交时间跟踪（需要超级用户权限，修改后重启数据库生效）

-- 安装依赖扩展
CREATE EXTENSION IF NOT EXISTS pg_xact_commit_timestamp;
-- 开启事务提交时间记录
ALTER SYSTEM SET track_commit_timestamp = on;

• 统计1年以上的记录数量

-- 替换your_table为实际表名
SELECT COUNT(*) AS old_record_count
FROM your_table
WHERE pg_xact_commit_timestamp(xmin) < NOW() - INTERVAL '1 year';

注意：如果之前没有开启track_commit_timestamp，则无法回溯历史事务的提交时间，只能用方式2判定。

方式2：通过业务关联逻辑判定 #

• 如果表有自增主键ID，可以先按业务日常写入速率，反推1年时间大概写入的记录数，再通过ID阈值统计：比如日均写入1万条，1年约写入365万条，那么ID小于「当前最大ID-365万」的就是1年以上的旧记录

SELECT COUNT(*) AS old_record_count
FROM your_table
WHERE id < (SELECT MAX(id) - 3650000 FROM your_table);

• 如果有和其他带时间列表关联的外键，可直接关联查询，用关联表的时间字段作为当前表记录的写入时间参考。

二、旧数据删除方案 #

首先明确：单条SQL直接删除大量记录必然会引发严重性能问题 #

• 会生成巨量WAL日志，存在短时间占满磁盘的风险
• 会长时间持有行锁甚至表锁，阻塞正常业务的读写请求
• 如果删除过程中断，会触发长事务回滚，数据库恢复时间不可控
• 删除后会产生大量死元组，后续大表VACUUM操作也会占用大量IO资源，影响业务稳定性

推荐分批删除方案 #

按主键范围分批次删除，每次删1000~10000条，每批删除完成后自动提交，还可以在脚本中增加批次间延迟，避免打满IO：

-- 单批删除1000条1年以上旧记录，可循环执行直到返回删除行数为0
WITH deleted_rows AS (
    DELETE FROM your_table
    WHERE id IN (
        SELECT id FROM your_table
        WHERE pg_xact_commit_timestamp(xmin) < NOW() - INTERVAL '1 year'
        LIMIT 1000
    )
    RETURNING 1
)
SELECT COUNT(*) AS current_deleted_count FROM deleted_rows;

极端场景加速方案 #

如果要删除的旧记录占总表数据量的70%以上，推荐用保留新数据换表的方式，操作更快、对业务影响更小：

-- 1. 新建和原表结构完全一致的新表
CREATE TABLE your_table_new (LIKE your_table INCLUDING ALL);
-- 2. 把1年以内的新数据导入新表
INSERT INTO your_table_new
SELECT * FROM your_table
WHERE pg_xact_commit_timestamp(xmin) >= NOW() - INTERVAL '1 year';
-- 3. 原子替换原表，仅这个步骤会有毫秒级锁表，基本不影响业务
BEGIN;
ALTER TABLE your_table RENAME TO your_table_old;
ALTER TABLE your_table_new RENAME TO your_table;
COMMIT;
-- 4. 确认业务运行正常后，删除旧表释放空间
DROP TABLE your_table_old;

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