Postgres中JSONB与规范化列选用规则及字段频率阈值指南

不存在所谓固定的“出现频率阈值”——键值的出现占比从来都不是拆不拆列的核心判断依据，优先级远低于实际业务的访问模式，不要拿静态比例做决策。

先直接回应40%有效值的场景问题：要不要拆列和这个40%的比例没有直接关系，核心看你怎么用这个键。

核心判断优先级（按权重从高到低） #

• 第一优先级：键的访问与约束需求
  这是最核心的判断标准，和值出现频率完全无关：
  • 如果你需要对这个键做高频的WHERE过滤、JOIN关联、聚合计算、范围排序，或者需要给这个键加类型校验、非空/唯一/外键约束，哪怕它只有20%~30%的场景有有效值，都值得拆成独立列。
    实际收益非常明确：JSONB虽然支持GIN索引、支持通过->>操作符取字段，但针对单个键的查询效率、查询优化器的统计信息准确度，独立列都比JSONB内部键高一个量级，能大幅降低执行计划选错的概率；同时独立列的类型是强绑定的，不会出现JSONB里同一键混存数字/字符串/布尔值多种类型导致查询报错的问题；写SQL的时候也不用每次写冗长的JSON提取语法，减少低级错误。
    很多人担心稀疏列占空间，其实完全没必要：Postgres行里的NULL值只在元组头的null位图里占1个bit，几乎没有额外存储成本，稀疏列的存储开销可以忽略不计。
  • 反过来，如果你几乎不会单独访问这个键，永远是把整个JSON结构读出来交给业务层处理，不需要针对这个键做任何数据库层面的计算、过滤、约束，哪怕它90%以上的场景都有值，也完全没必要拆成独立列。拆了反而要额外维护列和JSONB字段的数据一致性，平白增加写入、更新的开销，还损失了JSONB的schema灵活性。
• 第二优先级：值出现频率的参考作用
  只有当你对这个键的访问频率介于“高频查询”和“几乎不用”之间的时候，出现频率才有参考价值：
  • 如果键的出现率低于10%，且你只是每个月偶尔跑一次离线统计用到它，完全没必要拆，留在JSONB里临时查就行，真遇到性能问题再给JSONB加个针对这个键的表达式索引就够了。
  • 如果键的出现率高于60%，且你每周都会有几次针对它的查询需求，可以提前拆成独立列，省得后续加字段做全量数据回填的麻烦。

实操避坑提醒
不要做过度设计：Postgres 11+版本加非默认值的列都是秒级的元数据操作，不会锁表，完全没必要在项目初期就把所有可能用到的JSON键都拆成列。先把有明确强需求的键拆出来，剩下的留在JSONB里，等后续真的出现查询瓶颈、或者有明确约束需求的时候再拆就行，试错成本极低。

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