Postgres中如何为pg_cron的purge清理任务串联执行VACUUM操作

为什么存储函数中无法执行VACUUM相关操作 #

PostgreSQL的存储函数（FUNCTION）默认运行在事务上下文内，而VACUUM、CREATE TABLE AS、CREATE INDEX CONCURRENTLY这类命令无法在事务块中执行，这是你无法在存储函数中实现清理+VACUUM联动的核心原因，不是操作遗漏。

存储过程（PROCEDURE）是否适配该场景 #

存储过程完全适配该场景，PostgreSQL 11及以上版本支持的存储过程（PROCEDURE）没有必须包裹在单事务内的限制，可以在过程中手动控制事务提交，完全可以实现purge操作完成后提交事务，再运行VACUUM ANALYZE的逻辑。

示例代码 #

CREATE OR REPLACE PROCEDURE purge_and_vacuum_logs(keep_days int)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 执行旧数据清理
    DELETE FROM your_log_table WHERE create_time < CURRENT_TIMESTAMP - (keep_days || ' days')::interval;
    -- 提交清理事务，避免VACUUM运行时还存在未提交的删除事务导致无法回收死元组
    COMMIT;
    -- 执行VACUUM ANALYZE
    VACUUM ANALYZE your_log_table;
END;
$$;

你可以直接在pg_cron中配置调用该存储过程：

-- 每天凌晨2点执行，保留90天日志
SELECT cron.schedule('daily-log-purge', '0 2 * * *', 'CALL purge_and_vacuum_logs(90);');

PL/PgSQL例程的操作限制 #

你需要明确两种PL/PgSQL例程的核心差异：

• 存储函数（FUNCTION）：
  • 必须有返回值
  • 运行在调用方的事务上下文内，不允许执行COMMIT/ROLLBACK
  • 不允许执行任何无法在事务块中运行的命令，包括VACUUM、CLUSTER、CREATE DATABASE、CREATE INDEX CONCURRENTLY等
• 存储过程（PROCEDURE）：
  • 没有强制返回值（可以通过INOUT参数返回数据）
  • 允许在过程内部执行COMMIT/ROLLBACK控制事务
  • 支持执行绝大多数非事务类命令，仅少数需要独占会话级上下文的命令无法执行

无需额外任务的VACUUM联动最优方案 #

如果你不想单独创建存储过程，还有两种成熟方案可选：

1. 直接在pg_cron中配置顺序执行的命令
   pg_cron支持在同一个调度任务中写多条用分号分隔的命令，不需要额外创建独立任务：

SELECT cron.schedule('daily-log-purge', '0 2 * * *', 
  'DELETE FROM your_log_table WHERE create_time < CURRENT_TIMESTAMP - INTERVAL ''90 days''; VACUUM ANALYZE your_log_table;'
);

2. 针对性配置autovacuum自动触发
   针对日志表可以单独调优autovacuum参数，让PostgreSQL在大量删除后自动触发VACUUM，不需要手动执行调度：

ALTER TABLE your_log_table SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_vacuum_threshold = 1000
);

上述配置会在该表删除量超过1000行+表总数据量的1%时自动触发VACUUM，完全不需要手动调度VACUUM操作。

内容的提问来源于stack exchange，提问作者Morris de Oryx