如何列出PostgreSQL模式中近X天无新增或更新记录的闲置表？

PostgreSQL本身没有内置元数据直接记录表的最后插入/更新时间，但可以通过以下几种高效方法实现需求，无需手动逐个遍历表：

方法1：利用事务提交时间（需开启参数） #

PostgreSQL提供pg_xact_commit_timestamp()函数，可通过事务ID查询对应提交时间。但需要先在postgresql.conf中开启track_commit_timestamp = on，重启服务后生效。

通过查询表中所有行的xmin（插入事务ID）和xmax（更新/删除事务ID）的最大值，结合该函数得到最后写入时间，再筛选符合条件的表：

WITH table_last_activity AS (
    SELECT
        n.nspname AS schemaname,
        c.relname AS tablename,
        -- 最后插入时间
        (SELECT pg_xact_commit_timestamp(max(xmin)) FROM ONLY "{n.nspname}"."{c.relname}") AS last_insert,
        -- 最后更新/删除时间（排除未被修改的行xmax=0）
        (SELECT pg_xact_commit_timestamp(max(xmax)) FROM ONLY "{n.nspname}"."{c.relname}" WHERE xmax <> 0) AS last_update_delete
    FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
    WHERE c.relkind = 'r' -- 仅普通表
      AND n.nspname = 'your_schema' -- 替换为目标模式名
)
SELECT schemaname, tablename
FROM table_last_activity
WHERE
    -- 无插入记录 或 插入时间早于X天前
    (last_insert IS NULL OR last_insert < NOW() - INTERVAL 'X days')
    AND
    -- 无更新/删除记录 或 更新/删除时间早于X天前
    (last_update_delete IS NULL OR last_update_delete < NOW() - INTERVAL 'X days');

方法2：利用统计视图（近似值） #

如果无法开启track_commit_timestamp，可以用pg_stat_user_tables统计视图中的写入统计字段，判断表是否有近期活动：

SELECT schemaname, tablename
FROM pg_catalog.pg_stat_user_tables
WHERE schemaname = 'your_schema'
  -- 自上次统计重置后无插入、更新、删除
  AND n_ins_since_vacuum = 0
  AND n_upd_since_vacuum = 0
  AND n_del_since_vacuum = 0
  -- 统计重置时间早于X天前（确保统计覆盖X天周期）
  AND last_stat_reset < NOW() - INTERVAL 'X days';

注意：该方法依赖统计信息的更新频率，结果为近似值，若统计信息未更新可能不准确。

方法3：自定义触发器记录精确时间 #

如果可以修改表结构，推荐添加触发器自动记录最后修改时间，这是最可靠的方案：

1. 创建触发器函数：

CREATE OR REPLACE FUNCTION update_last_modified()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 为目标模式下的所有表批量添加字段和触发器：

-- 生成添加字段和触发器的SQL
SELECT
    format('ALTER TABLE %I.%I ADD COLUMN IF NOT EXISTS last_modified TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP;', n.nspname, c.relname)
    || format('CREATE TRIGGER trg_%I_last_modified BEFORE INSERT OR UPDATE ON %I.%I FOR EACH ROW EXECUTE FUNCTION update_last_modified();', c.relname, n.nspname, c.relname)
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
  AND n.nspname = 'your_schema';

执行生成的SQL即可完成批量配置。

3. 查询无近期活动的表：

SELECT n.nspname AS schemaname, c.relname AS tablename
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
  AND n.nspname = 'your_schema'
  AND (
      -- 空表
      (SELECT COUNT(*) FROM ONLY "{n.nspname}"."{c.relname}") = 0
      OR
      -- 最后修改时间早于X天前
      (SELECT MAX(last_modified) FROM ONLY "{n.nspname}"."{c.relname}") < NOW() - INTERVAL 'X days'
  );

关于系统函数与information_schema #

• information_schema中没有直接记录表最后写入时间的元数据字段。
• 系统函数pg_xact_commit_timestamp()可获取事务提交时间，但需开启track_commit_timestamp参数。
• pg_stat_user_tables统计视图提供近似的活动统计，但无法给出精确时间戳。

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