如何在PostgreSQL中创建自动删除3个月前记录的表？

不同数据库的自动清理机制有所不同，以下是主流数据库的具体实现方式：

MySQL #

方式1：分区表（推荐） #

通过按时间范围分区，在建表时定义分区规则，配合事件调度器自动清理过期分区，性能比逐条删除更优。

建表语句示例：

CREATE TABLE temp_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p_current VALUES LESS THAN (TO_DAYS(NOW()) + 91), -- 覆盖当前+3个月（约91天）
    PARTITION p_next VALUES LESS THAN MAXVALUE
);

创建自动清理分区的事件调度器：

-- 先开启事件调度器
SET GLOBAL event_scheduler = ON;

CREATE EVENT auto_delete_old_partitions
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DECLARE target_date VARCHAR(64);
    SET target_date = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 3 MONTH), '%Y%m');
    -- 删除3个月前的分区
    SET @drop_sql = CONCAT('ALTER TABLE temp_records DROP PARTITION IF EXISTS p_', target_date);
    PREPARE stmt FROM @drop_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    -- 新增下一个月的分区，维持分区连续性
    SET @next_month = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y%m');
    SET @next_partition_val = TO_DAYS(DATE_ADD(NOW(), INTERVAL 4 MONTH));
    SET @add_sql = CONCAT('ALTER TABLE temp_records ADD PARTITION (PARTITION p_', @next_month, ' VALUES LESS THAN (', @next_partition_val, '))');
    PREPARE stmt FROM @add_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

方式2：定时删除事件 #

如果不想用分区表，可直接建表后创建定时事件，定期删除过期记录：

建表语句：

CREATE TABLE temp_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

创建定时清理事件：

SET GLOBAL event_scheduler = ON;

CREATE EVENT auto_delete_old_records
ON SCHEDULE EVERY 1 DAY
DO
    DELETE FROM temp_records WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);

PostgreSQL #

方式1：pg_cron定时清理 #

借助pg_cron插件实现定时任务，先确保插件已安装：

建表语句：

CREATE TABLE temp_records (
    id SERIAL PRIMARY KEY,
    content TEXT,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

创建定时清理任务：

-- 安装pg_cron（未安装时执行）
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 每天凌晨2点删除3个月前的记录
SELECT cron.schedule(
    'auto-delete-old-records',
    '0 2 * * *',
    $$DELETE FROM temp_records WHERE create_time < NOW() - INTERVAL '3 months'$$
);

方式2：分区表自动维护 #

使用pg_partman插件实现自动分区与过期数据清理：

CREATE EXTENSION IF NOT EXISTS pg_partman;

CREATE TABLE temp_records (
    id SERIAL PRIMARY KEY,
    content TEXT,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (create_time);

-- 初始化分区，设置仅保留3个月数据
SELECT partman.create_parent(
    'public.temp_records',
    'create_time',
    'monthly',
    p_keep_partitions := 3
);

SQL Server #

方式1：分区表+SQL Agent作业 #

先创建分区函数与方案，再建分区表：

-- 创建按月份划分的分区函数
CREATE PARTITION FUNCTION pf_temp_records (DATETIME)
AS RANGE RIGHT FOR VALUES (
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
);

-- 创建分区方案
CREATE PARTITION SCHEME ps_temp_records
AS PARTITION pf_temp_records
ALL TO ([PRIMARY]);

-- 创建分区表
CREATE TABLE temp_records (
    id INT IDENTITY(1,1) PRIMARY KEY,
    content TEXT,
    create_time DATETIME DEFAULT GETDATE()
) ON ps_temp_records(create_time);

随后创建SQL Agent作业定期清理：

1. 打开SQL Server Agent，新建作业
2. 添加执行步骤，运行删除过期分区的T-SQL脚本
3. 设置调度为每日执行

方式2：定时作业直接删除记录 #

若分区表配置复杂，可直接建表后用SQL Agent作业定期删除：
建表语句：

CREATE TABLE temp_records (
    id INT IDENTITY(1,1) PRIMARY KEY,
    content TEXT,
    create_time DATETIME DEFAULT GETDATE()
);

作业执行脚本：

DELETE FROM temp_records WHERE create_time < DATEADD(MONTH, -3, GETDATE());

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