PostgreSQL高并发UPDATE计数查询性能问题与优化咨询

PostgreSQL 100+并发执行该UPDATE的实际行为 #

因为所有更新操作都命中theater_visitor表中同一个movie_id对应的行，PostgreSQL会自动为目标行加排他行锁：

• 同一时间只有1个事务能拿到锁完成更新操作，其余99+请求会进入阻塞状态等待锁释放，前序事务提交/回滚释放锁后，下一个等待的事务才能拿到锁执行
• 数据不会出现不一致：该UPDATE语句本身是原子操作，PostgreSQL的行锁机制会保证所有+1操作都按顺序生效，不会出现更新丢失的问题
• 负面影响是：该类请求的平均响应时间会明显变长，如果你的业务设置了事务/语句超时，可能会出现部分请求执行失败；如果这类请求挂载在长事务中，锁持有时间进一步拉长，还可能引发死锁，甚至打满数据库连接池，影响其他业务的正常执行

除队列之外的优化及注意事项 #

• 必须为movie_id列建索引：如果没有索引，UPDATE语句会走全表扫描，甚至触发表级锁，100并发下性能会直接雪崩，建议直接将movie_id设为该表的主键/唯一索引
• 尽量缩小事务粒度：不要把该UPDATE语句放在包含多步操作的长事务中执行，尽量让该更新操作单独在短事务内运行，执行完成后立刻提交释放锁，降低锁等待时长
• 用批量更新替代单条更新：如果业务允许计数有秒级延迟，不需要实时强一致，可以在应用层做请求攒批，比如每隔1s/攒满20个请求就执行一次批量更新，语句改为UPDATE theater_visitor SET viewer_total_count = viewer_total_count + $1 WHERE movie_id = $2，直接将100次更新压缩到个位数，完全消除锁竞争
• 用计数器分片分散锁压力：如果需要实时精准计数，可以把单个movie_id的计数拆成多行存储，比如同个id拆10条子记录，每次更新随机选一条做+1，查询计数时sum所有子记录的数值即可，锁竞争会直接分散到10行，100并发场景下基本不会出现等待
• 合理设置锁相关参数：调整PostgreSQL的lock_timeout参数（比如设为1s），避免长时间的锁等待堆积占用连接；调整deadlock_timeout到合理值，快速检测并终止死锁事务
• 避免不必要的返回逻辑：如果业务不需要拿到更新后的计数结果，不要在UPDATE后加RETURNING子句，减少额外的IO开销
• 引入缓存层分摊压力：如果对计数的一致性要求不是强一致（允许少量延迟），可以把计数放到Redis中做累加，定时批量同步到PostgreSQL，完全把更新压力转移到缓存层，数据库层面只需要定期写入即可

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