微服务架构下PostgreSQL唯一部分索引的潜在问题与性能咨询

• NULL值处理风险：如果archived字段未设置NOT NULL约束，当该字段为NULL时，这类记录不会被纳入唯一部分索引的约束范围。这意味着可能出现column_b+column_c组合重复且archived IS NULL的记录，若业务要求仅archived=1时允许重复，需先确保archived字段是NOT NULL类型，且仅能取0或1。
• 归档状态变更后的约束失效：当一条archived=0的记录被更新为archived=1后，该记录会从唯一部分索引中移除。此时系统允许插入新的archived=0且column_b+column_c组合相同的记录，若业务要求“一旦存在过未归档的该组合记录，即使归档后也不允许再插入同组合的未归档记录”，当前方案无法满足，需要额外的逻辑或约束。
• 并发更新的覆盖问题：当前ON CONFLICT的更新逻辑直接用EXCLUDED覆盖原有字段，若多个并发请求同时更新同一条记录，后执行的请求会覆盖前一个的更新结果。如果业务需要保留中间更新状态（比如累加操作），需调整更新逻辑，例如使用column_d = column_d + EXCLUDED.column_d这类表达式，而非直接覆盖。
• 索引创建的前置数据校验：若表中已存在archived=0且column_b+column_c重复的记录，创建唯一部分索引时会直接失败。需要先清理历史重复数据，才能成功创建索引。
• 字段更新的索引维护开销：当修改记录的archived字段（从0到1或1到0）时，PostgreSQL会对唯一部分索引执行插入或删除操作。若这类状态变更频繁，会增加索引维护的开销，甚至导致索引碎片积累。

• 插入性能：
  • 对于archived=0的插入操作，需要额外执行唯一部分索引的唯一性校验，相比无约束的插入会有轻微的开销，但相比之前“先查询后插入”的逻辑，原子化的INSERT ... ON CONFLICT避免了并发场景下的重复插入回滚，整体在高并发下性能更稳定。
  • 对于archived=1的插入操作，由于唯一索引的过滤条件不匹配，无需校验唯一性，插入性能与普通无约束插入基本一致。
• 更新性能：
  • 若更新操作未修改column_b或column_c（仅修改column_d），唯一部分索引无需更新，性能与普通UPDATE操作相当。
  • 若更新操作修改了column_b或column_c，则需要同步更新唯一部分索引，会带来额外的索引维护开销，但这类场景在业务中应该是少数。
• 索引存储与查询性能：
  • 唯一部分索引仅包含archived=0的记录，相比全表唯一索引，占用的存储空间更小，针对未归档记录的column_b+column_c组合查询，索引扫描的效率更高。
• 并发场景的性能稳定性：
  • 原逻辑的“先查询后插入”在高并发下会出现竞态条件，导致重复记录和业务错误，甚至需要额外的回滚操作。使用INSERT ... ON CONFLICT结合唯一部分索引后，PostgreSQL会通过内部锁机制保证原子性，避免了竞态问题，高并发下的性能和可靠性显著提升。

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