PostgreSQL SQL Error [53100]求助：磁盘有剩余空间仍报无空间

问题背景 #

使用PGAdmin 4 6.9 + PostgreSQL 12执行INSERT...SELECT插入2400万行数据，耗时超4小时后触发SQL Error [53100]，提示无法写入临时文件base/pgsql_tmp/pgsql_tmp8524.111：设备无剩余空间，但本地磁盘仍有150GB空闲。相同语句在其他机器仅需11分钟完成，已做常规性能优化但问题依旧。

报错原因分析 #

1. 临时文件存储分区空间不足 #

PostgreSQL默认将临时文件写入data_directory下的pgsql_tmp目录，该目录所在的磁盘分区未必是你看到有150GB空闲的分区（比如可能是系统盘空间耗尽，而数据盘有剩余）。

2. 查询执行计划低效，临时文件暴涨 #

你的SQL包含多表LEFT JOIN，且JOIN条件中使用了SUBSTR函数（如SUBSTR(ENTR."CD_CAT_NIV4_ENTR",1,2)），这类函数会导致PostgreSQL无法使用字段原有索引，触发全表扫描；同时多次关联同一张参考表，可能导致查询过程中生成远超预期的临时中间表，快速耗尽临时分区空间。

3. 配置参数不合理 #

• work_mem设置过小：PostgreSQL处理排序、JOIN等操作时，内存不足就会写入临时文件，过小的work_mem会导致频繁生成临时文件。
• temp_file_limit设置过低：该参数限制了单个会话可使用的临时文件总大小，若阈值低于查询所需，会提前触发空间不足报错。

解决办法 #

1. 检查并调整临时文件存储路径 #

• 执行SHOW temp_tablespaces;查看是否指定了独立的临时表空间，若未指定，执行SHOW data_directory;确认数据目录所在分区，用系统工具（Linux用df -h，Windows用资源管理器）检查该分区的实际空闲空间。
• 若临时分区空间不足，可：
  • 清理该分区的无用文件释放空间；
  • 在有空闲空间的磁盘创建新表空间，然后执行ALTER SYSTEM SET temp_tablespaces = '新表空间名';，重启PostgreSQL生效。

2. 优化查询执行计划 #

• 添加适配JOIN条件的索引：
  • 对于SUBSTR(ENTR."CD_CAT_NIV4_ENTR",1,2)这类条件，给ENTR表添加生成列并创建索引：

    ALTER TABLE "SIRENE"."SIRENE_ODS"."SR_ENTR" 
    ADD COLUMN CD_CAT_NIV2_ENTR VARCHAR(2) GENERATED ALWAYS AS (SUBSTR("CD_CAT_NIV4_ENTR",1,2)) STORED;
    
    CREATE INDEX idx_sr_entr_cat_niv2 ON "SIRENE"."SIRENE_ODS"."SR_ENTR"(CD_CAT_NIV2_ENTR);
    

    同理处理其他带SUBSTR的JOIN条件，然后修改SQL中的JOIN条件为生成列，避免函数运算。
  • 给ENTR."CD_NAF4_ENTR"、ENTR."CD_NAF_NIV1"以及关联表的对应字段（如CJUR1."CD_CAT_NIV4"、RSE_ENTR1."CD_DIVISION_NAF"）创建普通索引。
• 简化查询逻辑：多次关联同一参考表（如REF_ACT_NAF_ENTR）可通过子查询合并数据，减少JOIN次数，例如：

  WITH ref_naf AS (
      SELECT CD_ACT_PRIN_ENTR, DS_ACT_PRIN_ENTR, CD_DIVISION_NAF, DS_DIVISION_NAF, CD_SECTION_NAF, DS_SECTION_NAF
      FROM "SIRENE"."SIRENE_REF"."REF_ACT_NAF_ENTR"
  )
  SELECT ...
  FROM ...
  LEFT JOIN ref_naf RSE_ENTR1 ON SUBSTR(ENTR."CD_NAF4_ENTR",1,2) = RSE_ENTR1.CD_DIVISION_NAF
  LEFT JOIN ref_naf RSE_ENTR2 ON ENTR."CD_NAF4_ENTR" = RSE_ENTR2.CD_ACT_PRIN_ENTR
  ...
  

• 查看执行计划：执行EXPLAIN ANALYZE + 你的SELECT语句，定位是否存在全表扫描、外部磁盘排序等低效操作，针对性优化。

3. 调整PostgreSQL配置参数 #

• 临时调高work_mem：在执行INSERT前执行SET work_mem = '64MB';（根据服务器内存调整，16GB内存可设为128MB），让PostgreSQL尽量在内存中完成运算，减少临时文件生成。
• 解除临时文件大小限制：执行SET temp_file_limit = -1;（无限制，需确保磁盘空间足够），或设置为更大值（如100GB）。
• 若重启后需永久生效，修改postgresql.conf文件中的对应参数，然后重启服务。

4. 分批插入 #

若上述优化仍无法解决，将大INSERT拆分为多批执行，例如每次插入100万行：

INSERT INTO ...
SELECT ...
FROM ...
LIMIT 1000000 OFFSET 0;

INSERT INTO ...
SELECT ...
FROM ...
LIMIT 1000000 OFFSET 1000000;
-- 以此类推，直到完成全部数据插入

或按ID_SIREN等字段的范围拆分，避免OFFSET导致的性能损耗。

附执行的SQL语句 #

INSERT INTO  "SIRENE"."SIRENE_ODS"."DIM_CAT_NAF_ENTR_TMP" (
    "ID_SIREN", 
    "ID_NIC",
    "CD_DS_CAT_JURI_NIV1_ENTR",
    "CD_DS_CAT_JURI_NIV2_ENTR",
    "CD_DS_CAT_JURI_NIV4_ENTR",
    "CD_DS_SEC_NAF_ENTR",
    "CD_DS_DIVISION_NAF_ENTR",
    "CD_DS_ACT_PRIN_ENTR"
    )
SELECT
ENTR."ID_SIREN" AS ID_SIREN,
ETAB."ID_NIC" AS ID_NIC,
CASE WHEN CJUR3."CD_CAT_NIV1"='' OR CJUR3."CD_CAT_NIV1" IS NULL THEN 'ZZ - Non renseignée' ELSE CONCAT(replace(CJUR3."CD_CAT_NIV1", ' ', ''), ' - ', CJUR3."DS_CAT_NIV1") END AS CD_DS_CAT_JURI_NIV1_ENTR,
CASE WHEN CJUR2."CD_CAT_NIV2"='' OR CJUR2."CD_CAT_NIV2" IS NULL THEN 'ZZ - Non renseignée' ELSE CONCAT(replace(CJUR2."CD_CAT_NIV2", ' ', ''), ' - ', CJUR2."DS_CAT_NIV2") END AS CD_DS_CAT_JURI_NIV2_ENTR,
CASE WHEN CJUR1."CD_CAT_NIV4"='' OR CJUR1."CD_CAT_NIV4" IS NULL THEN 'ZZ - Non renseignée' ELSE CONCAT(replace(CJUR1."CD_CAT_NIV4", ' ', ''), ' - ', CJUR1."DS_CAT_NIV4") END AS CD_DS_CAT_JURI_NIV4_ENTR,
CASE WHEN RSE_ENTR3."CD_SECTION_NAF"='' OR RSE_ENTR3."CD_SECTION_NAF" IS NULL THEN 'ZZ - Non renseignée' ELSE CONCAT(RSE_ENTR3."CD_SECTION_NAF", ' - ', RSE_ENTR3."DS_SECTION_NAF") END AS CD_DS_NAF1_ENTR,
CASE WHEN RSE_ENTR1."CD_DIVISION_NAF"='' OR RSE_ENTR1."CD_DIVISION_NAF" IS NULL THEN 'ZZ - Non renseignée' ELSE CONCAT(RSE_ENTR1."CD_DIVISION_NAF", ' - ',  RSE_ENTR1."DS_DIVISION_NAF") END AS CD_DS_NAF2_ENTR,
CASE WHEN RSE_ENTR2."CD_ACT_PRIN_ENTR"='' OR RSE_ENTR2."CD_ACT_PRIN_ENTR" IS NULL THEN 'ZZ - Non renseignée' ELSE CONCAT(RSE_ENTR2."CD_ACT_PRIN_ENTR", ' - ',  RSE_ENTR2."DS_ACT_PRIN_ENTR") END AS CD_DS_NAF4_ENTR

FROM  "SIRENE"."SIRENE_ODS"."SR_ETAB" ETAB
INNER JOIN "SIRENE"."SIRENE_ODS"."SR_ENTR" ENTR ON (ETAB."ID_SIREN" = ENTR."ID_SIREN")
LEFT JOIN  "SIRENE"."SIRENE_REF"."REF_ACT_NAF_ENTR" RSE_ENTR1 ON (SUBSTR(ENTR."CD_NAF4_ENTR",1,2) = RSE_ENTR1."CD_DIVISION_NAF")
LEFT JOIN  "SIRENE"."SIRENE_REF"."REF_CAT_JUR_ENTR" CJUR1 ON (ENTR."CD_CAT_NIV4_ENTR" = CJUR1."CD_CAT_NIV4")
LEFT JOIN  "SIRENE"."SIRENE_REF"."REF_ACT_NAF_ENTR" RSE_ENTR2 ON (ENTR."CD_NAF4_ENTR" = RSE_ENTR2."CD_ACT_PRIN_ENTR")
LEFT JOIN  "SIRENE"."SIRENE_REF"."REF_CAT_JUR_ENTR" CJUR2 ON (SUBSTR(ENTR."CD_CAT_NIV4_ENTR",1,2) = CJUR2."CD_CAT_NIV2")
LEFT JOIN  "SIRENE"."SIRENE_REF"."REF_ACT_NAF_ENTR" RSE_ENTR3 ON (ENTR."CD_NAF_NIV1" = RSE_ENTR3."CD_SECTION_NAF")
LEFT JOIN  "SIRENE"."SIRENE_REF"."REF_CAT_JUR_ENTR" CJUR3 ON (SUBSTR(ENTR."CD_CAT_NIV4_ENTR",1,1) = CJUR3."CD_CAT_NIV1")

内容的提问来源于stack exchange，提问作者AAJ-Dev77