PostgreSQL单条file记录删除耗时超2小时原因排查

背景环境 #

• 使用PostgreSQL 16.6搭建代码回归数据库，包含git_commit表、file表，以及两张回归任务表：一张规模约600GB、40亿行，另一张约600MB、300万行
• 提交Pull Request时，回归任务会向git_commit插入提交记录，并向某张回归表批量插入数据；回归表以git_commit和file_id作为复合主键的一部分，且与file表建立外键关联
• 需删除file表中部分不应纳入回归的记录，外键未设置级联删除或其他关联操作，但单条记录删除耗时超2小时
• 数据库并发量极低，仅少数人员及回归程序使用；查询对应file记录的select操作耗时不足50毫秒
• 补充说明：待删除的文件虽已入库，但回归任务从未成功执行，回归表中无对应关联数据

执行计划对比 #

select操作执行计划：

db=> explain select * from file where file_id = 11802;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Scan using file_pkey on eng_file  (cost=0.28..8.30 rows=1 width=150)
   Index Cond: (file_id = 11802)
(2 rows)

Time: 42.335 ms

delete操作执行计划：

db=> explain delete from file where file_id = 11802;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Delete on eng_file  (cost=0.28..8.30 rows=0 width=0)
   ->  Index Scan using file_pkey on eng_file  (cost=0.28..8.30 rows=1 width=6)
         Index Cond: (file_id = 11802)
(3 rows)

Time: 42.130 ms

问题核心 #

明明回归表中无待删除file的关联数据，外键也未配置级联操作，为何单条file记录的删除耗时远超预期？

可能原因及排查方向 #

1. 外键约束的隐式全表检查 #

PostgreSQL删除父表（file）记录时，必须强制检查所有引用该表的子表（两张回归表）是否存在关联数据，即使未设置级联操作：

• 小回归表（300万行）的检查速度较快，但大回归表（40亿行）若没有针对file_id的独立索引，PostgreSQL会执行全表扫描来验证关联关系——这是耗时的核心原因
• 注意：回归表的复合主键索引(git_commit, file_id)无法单独用于file_id的查询，因为索引前缀是git_commit，无法直接定位file_id的匹配项

2. 锁等待或事务阻塞 #

即使并发量低，仍可能存在长时间运行的事务持有file表或回归表的锁，导致删除操作一直处于等待状态：

• 执行SELECT * FROM pg_locks WHERE relation = 'file'::regclass;查看锁状态
• 执行SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';排查未及时提交的长事务

3. 表或索引膨胀 #

file表或其主键索引存在严重膨胀，导致删除操作需要更新大量索引页：

• 执行SELECT relname, n_live_tup, n_dead_tup, idx_scan FROM pg_stat_user_tables WHERE relname = 'file';查看表的死元组情况
• 尝试重建主键索引后测试：REINDEX INDEX file_pkey;

4. 数据库配置参数限制 #

例如maintenance_work_mem设置过小，导致删除操作时无法高效处理索引更新：

• 查看当前配置：SHOW maintenance_work_mem;
• 临时调整参数后测试：SET maintenance_work_mem = '1GB';（根据服务器内存实际情况调整）

快速验证方案 #

为大回归表创建file_id的独立索引，消除外键检查时的全表扫描：

CREATE INDEX idx_regression_large_file_id ON 大回归表名 (file_id);

创建完成后重新执行删除操作，若耗时大幅降低，则可确认是外键检查全表扫描导致的问题。

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