PostgreSQL中LOGGED与UNLOGGED表加锁后UPDATE性能一致原因探究

回答 #

这背后的核心原因是独占锁消除了LOGGED表最主要的性能瓶颈——WAL（预写日志）的同步开销和并发竞争，让LOGGED表的性能追上了UNLOGGED表。下面详细拆解：

先明确两者的性能差异根源 #

LOGGED和UNLOGGED表的本质区别完全在于WAL日志的处理逻辑：

• UNLOGGED表：所有DML操作（包括UPDATE）都不会写入WAL日志。这直接跳过了WAL写入带来的磁盘IO同步开销（默认fsync=on下，每次WAL写入都要等待磁盘物理确认），所以在大部分场景下速度都快很多。
• LOGGED表：任何数据修改都必须写入WAL，这是PostgreSQL保证崩溃后数据一致性的核心机制，但也带来了不可避免的性能代价——尤其是在频繁执行小事务、有并发访问时，WAL的同步写入会成为明显的性能瓶颈。

加锁后抹平差异的两个关键变化 #

当你用ACCESS EXCLUSIVE MODE锁定表后，两个关键变化直接消除了两者的性能差距：

1. 消除了WAL的高频同步开销 #

无锁状态下（尤其是有其他应用干扰时），LOGGED表的每个小事务（你的函数每次调用就是一个独立小事务）提交时，都需要触发WAL缓冲区刷盘并等待磁盘确认，这个同步IO的延迟会不断累积，导致UPDATE速度只有1K次/秒。

而当表被独占锁定后，PostgreSQL明确知道没有其他会话会访问这个表，因此可以批量处理WAL写入：将多个UPDATE的WAL日志累积在WAL缓冲区中，等到缓冲区满或者事务批量结束时再一次性写入磁盘。这大幅降低了磁盘IO的同步频率，把LOGGED表的WAL开销降到了接近UNLOGGED表的水平。

2. 消除了锁竞争与事务优化的差异 #

• 无锁时，即使是单会话操作，LOGGED表的事务因为WAL的存在，提交阶段的开销远高于UNLOGGED表；如果有其他并发访问，还会出现行锁、WAL写入锁的竞争，进一步拉低速度。
• 加独占锁后，整个表完全由当前会话控制，PostgreSQL可以启用一系列针对性优化：比如跳过不必要的锁检查、延迟WAL同步到事务批量结束，甚至在某些情况下简化事务的一致性校验。这些优化让LOGGED表的事务开销和UNLOGGED表几乎一致。

额外小提示 #

你的PL/pgSQL函数里有个逻辑颠倒的问题：当useunlogged=true时，你操作的是LOGGED表credittable，而useunlogged=false时操作的是UNLOGGED表credittable_unlogged。这可能会影响你测试结果的准确性，建议修正这个逻辑~

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