关于pg_temp_###和pg_toast_temp_###临时Schema持续堆积的技术排查请求

根本原因确认 #

PostgreSQL的pg_temp_*和pg_toast_temp_*是会话级临时Schema，正常情况下会话终止后会自动清理，堆积的核心原因集中在三类：

• 长时运行/闲置会话：每个活跃会话会生成专属临时Schema，若会话长期保持连接（比如连接池里的闲置连接、未关闭的客户端终端），Schema会持续留存。可通过以下SQL排查：

  SELECT pid, usename, datname, state, query_start FROM pg_stat_activity WHERE state != 'idle';
  

• 未关闭的事务：如果会话开启事务后未提交/回滚，哪怕会话处于idle状态，临时Schema也无法被回收——临时对象绑定事务上下文，未结束的事务会阻止清理。用这条SQL定位问题会话：

  SELECT pid, usename, datname, xact_start, state FROM pg_stat_activity WHERE state IN ('idle in transaction', 'idle in transaction (aborted)');
  

• 应用/工具清理缺陷：部分应用创建临时对象后未正确释放，或第三方工具（ETL、ORM框架）存在连接管理漏洞，导致关联会话无法正常终止；另外PostgreSQL 12+的全局临时表若未配置生命周期，也会残留对应的临时Schema。

针对性解决方案 #

1. 清理异常会话

• 配置自动终止闲置事务会话：修改postgresql.conf中的idle_in_transaction_session_timeout为5min（按需调整），重启生效；或临时生效：

  ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
  SELECT pg_reload_conf();
  

• 连接池优化：如果用PgBouncer等连接池，设置闲置连接超时时间，定期回收长期闲置的连接，避免会话长期占用资源。

2. 优化事务与临时对象管理

• 应用代码强制事务闭环：确保所有事务在逻辑结束后及时提交/回滚，尤其是try-catch块中要处理异常回滚逻辑。
• 全局临时表配置生命周期：创建全局临时表时明确指定清理规则，比如事务结束后自动删除表：

  CREATE GLOBAL TEMPORARY TABLE temp_table (id INT) ON COMMIT DROP;
  

3. 手动清理残留Schema

先确认目标Schema对应的会话已终止（通过pg_stat_activity查不到对应pid），再执行删除：

DROP SCHEMA IF EXISTS pg_temp_123 CASCADE; -- 替换123为实际Schema编号
DROP SCHEMA IF EXISTS pg_toast_temp_123 CASCADE;

4. 监控预警

• 定期统计临时Schema数量：

  SELECT COUNT(*) FROM pg_namespace WHERE nspname LIKE 'pg_temp_%' OR nspname LIKE 'pg_toast_temp_%';
  

• 设置监控告警，当数量超过阈值时触发通知，提前介入排查。

内容的提问来源于stack exchange，提问作者Lakshmi Narayana