借助PostgreSQL分区优化大表读写性能的方案咨询

针对数亿行级别的users表遇到的读写慢、锁竞争、特定客户统计延迟问题，结合你提到的客户数据量差异大、新客户持续增加的场景，提供以下优化方案：

1. 混合分区策略（List + 子分区） #

这是最适配你场景的方案，兼顾大客户的性能和小客户的扩展性：

• 单独拆分超大客户：对数据量超过阈值（比如100万行）的大客户，单独创建LIST分区（例如users_customer_<uuid>）。这类客户的所有读写、统计操作都会直接命中单个分区，避免全表扫描，锁竞争也会被隔离到单个分区内。
• 小客户统一放入默认分区：所有小客户和新客户路由到一个默认LIST分区，再对这个默认分区做HASH子分区（按customer字段）。新客户会自动分配到对应子分区，小客户的读写锁会分散到不同子分区，统计单个小客户时，因为数据量小，扫描单个Hash子分区的代价可以忽略。
• 动态调整分区：定期监控各客户的数据量，当某个小客户增长到阈值时，通过PostgreSQL的分区拆分功能，将其从默认分区迁移到独立的LIST分区。

2. 纯Hash分区的统计性能优化 #

如果倾向于纯Hash分区，解决单个客户统计慢的问题：

• 创建分区本地唯一索引：针对customer字段创建分区索引，PostgreSQL会在每个分区单独维护索引。统计时优化器会根据customer的Hash值定位到对应分区，直接扫描该分区的索引计数，无需遍历所有分区。
• 开启并行查询：确保PostgreSQL的max_parallel_workers_per_gather参数配置合理，即使需要扫描少量分区，并行查询也能显著加速统计。
• 定期更新统计信息：定时执行ANALYZE users;，让优化器能准确判断分区路由规则，避免不必要的全分区扫描。

3. 辅助表彻底解决统计延迟 #

不管采用哪种分区方案，都可以通过辅助表彻底优化特定客户的计数统计：

• 创建customer_user_count辅助表，结构为：

  CREATE TABLE customer_user_count (
      customer uuid PRIMARY KEY,
      user_count bigint NOT NULL DEFAULT 0
  );
  

• 用触发器或异步任务维护计数：
  • 插入用户时，触发UPDATE customer_user_count SET user_count = user_count + 1 WHERE customer = NEW.customer;，不存在则插入。
  • 删除用户时同理递减计数；upsert操作如果不新增用户则无需更新。
• 统计时直接查询该表，速度为O(1)，完全绕过主表扫描。

4. 锁竞争的额外优化措施 #

除了分区，针对插入/upsert的锁竞争问题：

• 使用分区级唯一索引：避免创建全局唯一索引（全局锁会导致所有分区的写入排队），将唯一键（比如(customer, username)）设置为分区键的一部分，让冲突检查仅在单个分区内执行。
• 调整锁模式：如果业务允许，upsert操作可以考虑用FOR NO KEY UPDATE锁替代默认的排他锁，减少锁冲突的概率。
• 开启partitionwise_aggregate：PostgreSQL 12+版本中，开启该参数后，聚合操作（如count）会仅在相关分区执行，进一步提升性能。

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