PostgreSQL Array Contains与JOIN性能对比及业务选型咨询

性能差异 #

数组存储方案 #

• 单值查询性能：如果对数组字段建了GIN索引，activity_id = ANY(activity_ids)或activity_ids @> ARRAY[目标ID]的查询性能在你的场景下表现很好——毕竟大部分数组长度为1，索引扫描效率很高，不需要多表关联，比关联表JOIN的查询延迟更低。
• 短板非常明显：
  • 无法做外键约束，你没法保证存储的活动ID都是合法存在的，容易产生脏数据
  • 复杂查询性能暴跌：比如统计每个活动的关联transaction数量、按活动维度做聚合分析、更新transaction关联的活动列表，这些操作的效率都远低于关联表，数组越长性能越差
  • 很容易写出逻辑错误的查询，比如统计活动关联量的时候忘记拆解数组元素，导致统计结果不准

关联表（transaction_events）方案 #

• 基础查询性能：对transaction_events.activity_id建索引后，简单JOIN查询的性能完全够用，PostgreSQL对这种简单外键关联的优化已经非常成熟，哪怕是千万级数据量，和数组方案的性能差距也可以忽略不计。
• 优势突出：
  • 支持外键约束，从数据库层面保证关联数据的一致性
  • 复杂查询、关联关系更新的效率都远高于数组方案
  • 扩展性极强，后续如果需要给活动关联加额外属性（比如参与时间、参与时的用户渠道等），直接在关联表加字段即可，数组方案完全不支持这类扩展

行业推荐结论 #

除非你100%确定未来不会有任何关联维度的复杂查询、不需要保证活动ID合法性、不会新增关联属性，否则一律推荐用关联表方案，这也是PostgreSQL社区对于一对多/多对多关联场景的通用最佳实践。

针对你现在绝大多数transaction仅关联单活动的场景，还可以做性能优化：在transaction表冗余一个main_activity_id字段，存单活动的ID，日常单活动查询直接查这个冗余字段，连JOIN都不需要，兼顾性能和扩展性。

内容的提问来源于stack exchange，提问作者Ignacio Negro Caino