PostgreSQL中JSON/JSONB列的适用场景与选择依据

核心差异先搞懂 #

• JSON：以原始文本存储，查询时需实时解析，不支持原生索引（仅能建函数索引），写入速度快，占用空间略小，会严格保留原始JSON的键顺序、重复键。
• JSONB：以二进制格式存储，写入时已完成解析，支持GIN/GIST索引，查询速度快，占用空间略大，会自动去重重复键（保留最后一个），不保留键的原始顺序。

JSON类型的适用场景 #

• 存储仅用于归档或传输、极少查询内部字段的数据：比如系统日志、临时中转的JSON结构，只存不用查内容。
• 需要严格保留原始JSON格式细节的场景：比如要保留键的排列顺序、重复键（例如某些第三方接口返回的特殊JSON）。
• 写入频率极高，查询频率极低的业务场景：比如高并发的用户行为上报，只做存储不做分析。

JSON类型的不适用场景 #

• 需要频繁查询JSON内部字段的场景：每次查询都要解析文本，性能损耗大。
• 需要为JSON内容建索引加速查询的场景：原生不支持，函数索引的效率远不如JSONB。
• 需要对JSON进行复杂修改、合并等操作的场景：操作耗时高。

JSONB类型的适用场景 #

• 需要频繁查询或过滤JSON内部字段的场景：比如经常用->>提取字段、用字段做WHERE条件过滤。
• 需要为JSON内容创建索引的场景：可以给整个JSONB字段建GIN索引，也能给单个字段建B-tree索引。
• 需要进行复杂JSON数据操作的场景：比如用jsonb_set修改字段、jsonb_array_elements展开数组，JSONB支持更多高效的内置操作符。
• 存储结构化但字段不固定的数据：比如电商商品的可变属性（不同品类的商品属性差异大），又需要按属性做查询。

JSONB类型的不适用场景 #

• 对写入速度要求极致，且几乎不查询内容的场景：写入时的解析、二进制转换会带来额外开销。
• 需要严格保留原始JSON文本细节（键顺序、重复键）的场景：JSONB会自动处理重复键，调整存储顺序。

快速选择口诀 #

读多写少、要查内容选JSONB；写多读少、只存不查/要留原始格式选JSON。

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