PostgreSQL 14/15是否支持自动删除长期未访问的旧表？

PostgreSQL 14、15 版本没有内置类似S3自动老化删除长期未访问旧表的便捷功能，但可以通过组合内置特性和扩展来实现这个需求，具体步骤如下：

• 追踪表的访问时间
  PostgreSQL默认不会持久化记录表的最后访问时间，需要手动实现追踪机制：

  1. 先确认track_activities和track_counts参数处于开启状态（默认开启），可通过以下命令验证：

     SHOW track_activities;
     SHOW track_counts;
     

  2. 创建自定义表来存储表的访问时间：

     CREATE TABLE table_access_log (
         table_name regclass PRIMARY KEY,
         last_accessed TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
     );
     

  3. 编写触发器函数，在表被访问时更新访问时间记录：

     CREATE OR REPLACE FUNCTION update_table_access()
     RETURNS TRIGGER AS $$
     BEGIN
         INSERT INTO table_access_log (table_name, last_accessed)
         VALUES (TG_RELID, CURRENT_TIMESTAMP)
         ON CONFLICT (table_name) DO UPDATE SET last_accessed = CURRENT_TIMESTAMP;
         RETURN NULL;
     END;
     $$ LANGUAGE plpgsql;
     

  4. 为需要追踪的目标表绑定触发器（以public.some_table为例）：

     CREATE TRIGGER trigger_track_access
     AFTER SELECT ON public.some_table
     FOR EACH STATEMENT EXECUTE FUNCTION update_table_access();
     

• 定时清理未访问的表
  借助pg_cron扩展实现定时自动删除：

  1. 安装pg_cron扩展：

     CREATE EXTENSION pg_cron;
     

  2. 创建定时任务，比如每天凌晨2点删除90天未被访问的表：

     SELECT cron.schedule(
         'delete-unused-tables',
         '0 2 * * *',
         $$
         DO $$
         DECLARE
             rec RECORD;
         BEGIN
             FOR rec IN SELECT table_name FROM table_access_log WHERE last_accessed < NOW() - INTERVAL '90 days' LOOP
                 EXECUTE 'DROP TABLE ' || rec.table_name || ';';
                 DELETE FROM table_access_log WHERE table_name = rec.table_name;
             END LOOP;
         END $$;
         $$
     );
     

     注意：DROP TABLE操作不可逆，执行前务必确认权限和数据备份，建议先在测试环境验证逻辑。

• 替代方案：系统级定时工具
  如果不想使用pg_cron，可以用Linux的cron或Windows任务计划程序，通过psql命令行连接数据库执行清理脚本，示例命令：

  psql -U username -d dbname -f /path/to/cleanup_script.sql
  

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