PostgreSQL同事务内多表删除耗时过长的原因咨询

问题本质 #

你遇到的情况是PostgreSQL外键约束检查的MVCC特性导致的：

• 在同一事务中先删table2再删table1时，执行DELETE FROM table1的每一行，数据库都要验证table2中没有引用该行的记录
• 虽然当前事务内已经删掉了table2的所有行，但事务未提交前，这些被标记删除的行在约束检查逻辑中仍被视为存在——这是因为PostgreSQL要保证如果事务回滚，数据依然符合外键约束，不能提前“认可”未提交的修改
• 由于table2的外键列没有索引，数据库只能对table2做全表扫描来验证，6万行table1就触发了6万次全表扫描，直接导致性能爆炸

独立事务无问题的原因 #

分开执行两个事务时，第一个事务删除table2并提交后，table2的行已经被确认删除，第二个事务删table1时，约束检查能直接看到table2为空，不需要任何扫描，所以瞬间完成。

快速解决 #

给table2的外键列加索引，让数据库能快速确认是否存在引用：

CREATE INDEX idx_table2_table1_fk ON table2 (your_foreign_key_column);

加索引后，即使在同一事务内执行，约束检查也能通过索引快速验证，避免全表扫描。

官方文档依据 #

PostgreSQL官方文档中关于外键约束的一致性规则明确：约束验证必须保证事务提交后的数据状态完全符合约束要求，因此不会利用事务内未提交的修改来简化检查。未提交的删除操作在约束逻辑中不被视为已生效，防止事务回滚后出现数据不一致。

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