超大型数据库Postgres分区方案及十亿级items表查询优化咨询

按user_id分区完全合理，是适配你场景的最优选择，理由如下：

• 你的几乎所有查询都带user_id过滤条件，Postgres分区剪枝机制会直接跳过所有不相关的分区，查询时只扫描目标用户对应的小范围分区，相比单表数十亿行扫全局索引的方案，随机IO成本降低90%以上
• 分区键和后续分片规则完全对齐，后续单节点性能不足要做跨节点分片时，可以直接将对应user_id范围的分区迁移到新节点，无需全表重构数据，扩容成本极低
• 分区后单分区数据量大幅降低，VACUUM、备份、冷数据归档等维护操作的成本也会大幅下降

最优分区布局建议 #

• 分区类型选择按user_id的范围分区：如果user_id是自增数值型，每1000~5000个用户划分为一个分区即可，无需给单个用户建独立分区，避免分区数量过多导致元数据管理成本上升
• 分区表上建立联合索引 (user_id, char_id, item_type)，完全覆盖你的典型查询过滤条件，索引扫描后直接即可拿到目标item数据
• 视图/纯索引方案不适用：视图只是语法糖，不会优化底层执行逻辑，和直接查原表无性能差异；纯索引方案下单表数十亿行的B树索引深度可达4~5层，每次查询需要多次随机IO，性能远不如分区剪枝后的小分区索引扫描

是否冗余完全取决于你要冗余的字段的更新频率：

• 如果char类型、account类型这类字段是几乎不会变更的静态属性，非常建议冗余：冗余后可以直接省去两次表关联，查询单表即可拿到全部结果，性能提升幅度在30%_{100%之间，数十亿行多冗余2}3个字段仅增加几GB到几十GB的存储开销，用存储换性能的性价比极高。冗余时建议在应用层写操作时同步更新冗余字段，或用Postgres触发器做一致性校验，避免数据不一致
• 如果要冗余的字段是高频更新的属性，不建议冗余：否则每次更新char/account的字段，都需要批量更新关联的所有item行，写入成本会高到无法接受。这种方案可以把全量char、account表的数据缓存到Redis，用主键做key，查询出100条item后批量查Redis获取关联属性，延迟也能控制在毫秒级

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