PostgreSQL多索引表更新优化：一致性与并发吞吐量权衡

1. 行更新时的读取阻塞问题 #

你的部分判断正确，但需明确细节：

• PostgreSQL的行更新会先获取该行的排他锁，在锁释放前，其他事务对该行的写入操作会被阻塞，但普通读取操作默认不会被阻塞——这得益于MVCC（多版本并发控制）机制，读取会访问该行的旧版本快照，无需等待锁释放。
• 索引更新本身也不会阻塞读取，索引同样依赖MVCC提供版本隔离，读取操作可以访问索引的旧版本。只有当你使用SELECT ... FOR UPDATE这类加锁读语句，或设置了SERIALIZABLE隔离级别且出现冲突时，读取才会被阻塞。

2. 多更新操作的并发问题 #

你的理解基本正确：

• PostgreSQL的B-tree索引支持并发更新，索引更新采用逐行、逐页锁定策略，不会锁定整个索引。不同行的更新操作之间不会互相阻塞，因为它们锁定的是不同的行，索引层面仅会短暂锁定涉及的索引页，且锁持有时间极短。
• 只有当多个事务同时更新同一行时，才会因为排他锁的互斥性互相阻塞，需等待前一个事务提交或回滚后才能继续。

3. 非原子索引更新与一致性换取吞吐量 #

PostgreSQL默认不支持“非原子逐个更新索引”的操作——事务的ACID特性要求索引更新与表行更新保持原子性，否则会导致索引与表数据不一致，引发数据错误或索引失效。

但如果你愿意牺牲强一致性来提升并发，可通过以下调优方向实现：

• 精简索引数量：删除冗余、低频查询使用的索引，这是最有效的优化手段——100个带索引的列绝大多数属于过度索引，会大幅增加更新时的IO和CPU开销。
• 使用轻量级索引：对有序数据用BRIN索引替代B-tree，其更新代价远低于B-tree；对特定查询场景使用部分索引，仅索引符合条件的行。
• 调整事务隔离级别：将隔离级别从SERIALIZABLE降至默认的READ COMMITTED，减少锁冲突概率（PostgreSQL的READ UNCOMMITTED实际行为与READ COMMITTED一致，仍依赖MVCC快照）。
• 批量更新操作：将多个单行更新合并为批量更新，减少事务开销和索引更新的总次数。
• 分区表优化：将大表按业务维度分区，每个分区的索引规模更小，更新时涉及的索引操作范围有限，能提升并发能力。
• 异步索引维护：借助逻辑复制，将索引更新放到订阅端异步处理，主库仅处理表行更新，代价是主从数据存在延迟，牺牲部分一致性。

多索引表更新的读写并发影响总结 #

• 写性能：每次行更新需同步更新所有涉及的索引（若更新列是索引列），100个索引会导致大量IO、CPU开销，显著降低写吞吐量，延长事务响应时间。
• 读性能：普通读取不会被写操作阻塞，但过多索引会增加表存储空间、减慢全表扫描速度，同时索引维护占用的IO资源会挤占读操作资源，间接降低读吞吐量。
• 锁冲突：不同行的更新并发度较高，仅同一行更新会产生阻塞；索引页的锁冲突偶尔发生，但影响远小于行锁冲突。

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