怎样在PostgreSQL中编写SQL触发器实现自动化日志清理？-SQL-PHP中文网

触发器不能直接用于日志清理，必须用定时任务配合函数；因其仅响应数据变更、不感知时间，真正可行的是将清理逻辑封装为函数，再通过pg_cron或系统cron定期调用。

怎样在postgresql中编写sql触发器实现自动化日志清理？

触发器不能直接用于日志清理，必须用定时任务配合函数

PostgreSQL 的 TRIGGER 只能在数据变更（INSERT/UPDATE/DELETE）时响应，无法按时间自动执行。想“自动化清理过期日志”，靠触发器本身做不到——它不感知时间，也不主动运行。

真正可行的路径是：把清理逻辑封装成 FUNCTION，再用外部调度（如 pg_cron 扩展或系统 cron）定期调用它。

常见错误现象：ERROR: cannot use clock_timestamp() in a trigger function that is not declared VOLATILE 或清理根本没发生，就是因为误以为加个 AFTER INSERT 触发器就能“每天清一次”。

触发器适合做“每次删用户，就同步删其日志”的强关联操作，不是时间驱动任务
日志表通常写多读少、体积大，清理动作应避开业务高峰，且需考虑锁表现象
若硬要在触发器里塞时间判断（比如 CURRENT_DATE > created_at + INTERVAL '30 days'），只会让每次写入都变慢，还可能漏删

用 pg_cron 实现真正的定时清理（推荐）

pg_cron 是 PostgreSQL 官方推荐的定时作业扩展，以插件形式运行在数据库内，语法贴近标准 cron，无需依赖外部脚本。

前提：确保已启用扩展（CREATE EXTENSION IF NOT EXISTS pg_cron;），且 postgresql.conf 中配置了 shared_preload_libraries = 'pg_cron' 并重启。

实操步骤：

先写一个安全清理函数，带事务控制和行数限制，避免单次锁表太久：

CREATE OR REPLACE FUNCTION cleanup_old_logs()
RETURNS void AS $$
BEGIN
  DELETE FROM log_table
  WHERE created_at

注册定时任务（每天凌晨2点执行）：

SELECT cron.schedule('0 2 * * *', $$CALL cleanup_old_logs()$$);

查看任务状态：SELECT * FROM cron.job;；手动运行测试：CALL cleanup_old_logs();

注意：如果日志表有上亿行，LIMIT 和 WHERE 字段必须有索引（如 CREATE INDEX CONCURRENTLY ON log_table(created_at)），否则全表扫描会拖垮性能。

不用 pg_cron 时的替代方案：系统 cron + psql 命令

某些生产环境禁用数据库内定时扩展，这时可用操作系统级 cron 调用 psql 执行 SQL。

示例（Linux）：

# 每天 3:15 清理
15 3 * * * psql -U myuser -d mydb -c "DELETE FROM log_table WHERE created_at  /dev/null 2>&1

关键细节：

务必加 LIMIT，防止某次执行卡住或锁表过久
使用 -c 直接传 SQL，避免写 shell 脚本引入额外变量解析风险
连接参数（如 PGPASSWORD）不要硬编码在 crontab 里，改用 ~/.pgpass 文件更安全
日志表名、时间阈值等参数不宜写死在命令中，否则改策略要改 crontab —— 更稳妥的是把 SQL 封装进函数，cron 只调 CALL cleanup_old_logs()

为什么不能只靠 VACUUM 或 AUTOVACUUM？

VACUUM 不删除数据，只回收已标记为“死亡”的行所占空间；AUTOVACUUM 更不会按时间条件删记录。如果你看到磁盘没释放，大概率是因为清理 SQL 没跑，而不是 vacuum 没配好。

典型误判场景：SELECT pg_size_pretty(pg_total_relation_size('log_table')); 显示大小不变，就去调大 autovacuum_vacuum_scale_factor —— 这毫无意义，因为旧日志行还没被 DELETE，vacuum 根本无事可做。

记住：物理空间回收的前提，是先用 DELETE（或分区 DROP）把数据逻辑删掉。自动化的难点从来不在“怎么删”，而在于“什么时候删、删多少、会不会影响线上”。