Postgres是否需维护可从数组长度推导的冗余state字段

这类可以从其他字段推导得出的冗余列是否值得维护，核心看你业务的性能、可维护性需求，没有绝对的对错，以下是不同场景的选择建议：

优先不维护冗余state列的场景（新手推荐首选方案） #

冗余列最大的风险是数据一致性问题：但凡你更新max_solved_by、修改currently_solved_by数组时漏了同步state值，就会出现业务逻辑错误，哪怕靠业务代码维护也很容易遗漏批量更新、数据修复等场景。

这种情况下你完全可以用更安全的方案替代：

• 如果你觉得每次写查询条件麻烦，可以创建一个视图封装state逻辑，查询时直接查视图即可：

CREATE OR REPLACE VIEW tasks_with_state AS
SELECT 
    *,
    CASE 
        WHEN array_length(currently_solved_by, 1) >= max_solved_by THEN 'SOLVED'
        ELSE 'NOT_SOLVED'
    END AS state
FROM Task;

后续你直接执行SELECT * FROM tasks_with_state WHERE state = 'NOT_SOLVED'就能得到和原查询完全一致的结果，且完全不需要担心数据不一致问题。

• 如果查询性能不够，可以直接建表达式索引优化查询速度，效率和查实体state列没有明显差异：

-- 针对未完成任务查询的局部索引，体积小查询速度快
CREATE INDEX idx_tasks_not_solved ON Task (array_length(currently_solved_by, 1))
WHERE array_length(currently_solved_by, 1) < max_solved_by;

可以考虑添加冗余state列的场景 #

如果符合以下任一特征，添加冗余列的收益会大于风险：

• 后续state的判断逻辑会变得更复杂，不再是单纯对比数组长度和上限，靠视图/表达式计算的成本显著升高
• 该字段的查询频次极高，且需要和其他业务字段联合做复杂过滤，实体列的索引灵活度更高
• 团队SQL熟练度较低，实体state列可以大幅降低写错查询条件的概率

如果确定要加冗余列，必须做以下两个保障避免数据不一致：

1. 用数据库触发器自动维护state值，不要靠业务代码更新：给max_solved_by、currently_solved_by字段加INSERT/UPDATE触发器，每次这两个字段变更时自动计算最新的state赋值。
2. 加CHECK约束做兜底校验，哪怕触发器逻辑出问题也不会产生脏数据：

ALTER TABLE Task ADD CONSTRAINT check_task_state_valid
CHECK (
    state = CASE 
        WHEN array_length(currently_solved_by, 1) >= max_solved_by THEN 'SOLVED'
        ELSE 'NOT_SOLVED'
    END
);

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