PostgreSQL运行大查询时如何查看临时表空间占用及剩余空间

查看临时表空间剩余可用空间 #

你报错中提到的临时文件存放在OID为16400的表空间下，首先执行以下SQL查询该表空间对应的物理路径：

SELECT spcname, pg_tablespace_location(oid) AS tblspc_path 
FROM pg_tablespace 
WHERE oid = 16400;

拿到路径后：

• 若你有数据库服务器的登录权限，直接执行df -h [对应挂载路径]即可查看该路径所在磁盘的剩余可用空间。
• 若你使用的是云托管PostgreSQL实例，无需登录服务器，直接在云厂商控制台的实例监控页查看存储空间使用率即可。

问题定位方法 #

你之前统计的120GB仅为持久化存储的表和索引大小，未包含查询运行时生成的临时文件、WAL日志、系统日志等占用的空间，这是触发磁盘不足报错的核心原因，可按以下步骤定位具体问题：

1. 查看当前数据库累计临时文件占用
   执行以下SQL查看当前库的临时文件总占用：

SELECT 
  datname,
  temp_files,
  temp_bytes,
  pg_size_pretty(temp_bytes) AS total_temp_size
FROM pg_stat_database 
WHERE datname = current_database();

2. 定位占用临时空间的具体查询
   执行以下SQL可直接看到当前运行中所有占用临时空间的查询，以及各自的临时空间占用量：

SELECT 
  pid,
  usename,
  query,
  state,
  temp_bytes,
  pg_size_pretty(temp_bytes) AS query_temp_usage
FROM pg_stat_activity 
WHERE temp_bytes > 0 
ORDER BY temp_bytes DESC;

3. 检查work_mem配置
   执行show work_mem;查看当前排序内存阈值：

• 若该值设置过小，PostgreSQL在执行排序、哈希连接、大表聚合等操作时，内存无法容纳中间结果，就会将数据写入临时文件，即便是大表的简单排序查询也可能生成GB级别的临时文件。
• 可根据业务场景适当调大work_mem，减少临时文件生成的频率。

4. 检查残留临时文件
   如果数据库曾出现过异常崩溃，可能会遗留未被自动清理的临时文件，可在停库后手动删除对应表空间下pgsql_tmp目录中的过期临时文件，注意不要误删其他业务数据文件。

另外你256GB的存储配额，扣除120GB的业务数据后，剩余空间还需要承载WAL日志、运行日志、临时文件等内容，若同时运行多个大查询，临时文件总和很容易占满剩余空间。

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