PostgreSQL删除含大量关联外键的租户时报共享内存不足的问题咨询

问题根因解释 #

1. 外键约束的锁占用逻辑和预期不符 #

PostgreSQL在处理主表行删除的外键完整性校验时，并不会先判断子表是否存在关联数据再决定是否加锁：只要是建立了指向主表外键约束的子表，在校验过程中都会先获取表级共享锁，防止校验过程中有新的关联数据写入。你合计有3267个指向租户表的外键，意味着删除租户的事务至少需要先持有3267个表锁，远超PostgreSQL默认max_locks_per_transaction的默认值（通常为64）。

2. DEFERRABLE外键会放大锁占用 #

你所有外键都设置了DEFERRABLE属性，这类约束不会在单条SQL执行时即时校验，会推迟到事务提交或执行SET CONSTRAINTS ALL IMMEDIATE时统一校验。统一校验阶段会一次性持有所有关联子表的锁，不会在校验完单个表后提前释放锁，进一步拉高了单事务的锁持有峰值。

3. 触发器带来额外锁开销 #

你关联在租户表上的6000个触发器，在删除租户时会全部触发执行，触发器逻辑执行过程中访问的表也会产生新的锁占用，进一步推高了锁的总数量，直接触发共享内存不足的报错。

可选优化方案 #

除了你已经知道的调高max_locks_per_transaction、删除外键、减少schema数量之外，还有几个不影响现有架构的优化方向：

• 调高max_locks_per_transaction是成本最低的方案，该参数每个锁槽仅占用几十字节内存，你这个场景调到10000也仅会额外占用几百KB的共享内存，对性能无负面影响，完全适配你的业务规模。
• 把外键的DEFERRABLE属性去掉，改成即时校验的外键，校验完单个子表无关联数据后就可以提前释放锁，降低锁持有峰值。
• 删除租户前先遍历所有子表主动删除对应租户的所有数据，删除后主动禁用所有指向租户表的外键约束，完成租户删除后再恢复约束，避免删除主表时触发全量外键校验。

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