PostgreSQL JSONB 与规范化表：决策与迁移

真正的问题：快速迭代而不把自己逼入死角

当你在构建新产品时，需求每周变化是很常见的。客户要求增加字段，销售想要不同的工作流，支持需要审计记录。结果你的数据库承担了这些变化的负担。

快速迭代不仅仅是更快发布界面。它意味着你可以添加、重命名和删除字段而不会破坏报表、集成或旧记录。也意味着你能回答新问题（“上个月有多少订单缺少交付单？”）而不必把每个查询变成一次性脚本。

这就是为什么在早期就要在 JSONB 与规范化表之间做出选择。两者都可以工作，但如果用错地方都会带来痛苦。JSONB 给人自由感，因为今天几乎可以存任何东西；规范化表让人觉得更安全，因为它强制结构。真正的目标是让存储模型匹配你当前数据的不确定性，以及它需要多快变得可靠。

当团队选错模型时，症状通常很明显：

• 简单问题变成了缓慢、混乱的查询或自定义代码。
• 两条记录表示同一事物却用不同的字段名。
• 可选字段后来变成必需，旧数据不匹配。
• 无法在不做变通的情况下强制规则（唯一值、必需关系）。
• 小改动后报表和导出不断出错。

实用的决策是：哪些地方你需要灵活性（并且可以容忍一段时间的不一致），哪些地方你需要结构（因为数据驱动收入、运营或合规）。

简单解释 JSONB 和规范化表

PostgreSQL 可以把数据存为经典列（text，number，date），也可以把整个 JSON 文档存到一个列里，使用 JSONB。区别不是“新与旧”，而是你希望数据库保证什么。

JSONB 存储键、值、数组和嵌套对象。它不会自动强制每行都有相同的键、值总是相同类型，或被引用项存在于另一张表中。你可以添加校验，但必须自己决定并实现它们。

规范化表是把数据按实体拆开到不同表并用 ID 连接。一个客户在一张表，一个订单在另一张表，每个订单指向一个客户。这能更强地防止矛盾。

日常权衡很简单：

• JSONB：默认灵活，易于更改，但更容易偏离一致性。
• 规范化表：更改需更慎重，但更易校验，查询更统一。

一个简单例子是支持工单的自定义字段。用 JSONB，你可以在明天添加新字段而无需迁移。用规范化表，添加字段更有意图，但报表和规则更清晰。

何时 JSONB 是快速迭代的合适工具

当你最大风险是构建出错误的数据形状，而不是要强制执行严格规则时，JSONB 是强有力的选择。如果你的产品还在摸索工作流，把一切强行塞进固定表会因不断迁移而拖慢速度。

一个好迹象是字段每周都在变化。想象一个入职表单，市场不断添加问题、重命名标签、删除步骤。JSONB 允许你按原样存储每次提交，即便明天的版本长得不一样。

JSONB 也适合“不明之物”：你还不了解的数据，或你无法控制的数据。如果你接收合作方的 webhook 负载，把原始载荷存为 JSONB 能让你立即支持新字段，之后再决定哪些字段应该成为一等列。

常见的早期场景包括快速变化的表单、事件捕获与审计日志、每客户设置、功能开关和实验。当你主要是写入数据、整块读取，并且数据形状还在变化时，JSONB 特别有用。

一个比人们预期更有效的护栏是：保留一份简短、共享的键列表，避免同一字段出现五种拼写方式。

何时规范化表是更安全的长期选择

当数据不再只是“某个功能用的”而是被共享、查询和信任时，规范化表占优。如果人们会从多个维度切分和筛选记录（状态、负责人、区域、时间段），列和关系让行为可预测且更易优化。

当规则必须由数据库而非“尽力而为”的应用代码来强制时，规范化也很重要。JSONB 可以存任何东西，这正是当你需要强保证时的问题所在。

需要现在就规范化的迹象

通常当以下多项为真时，应从以 JSON 为先的模型转向规范化：

• 你需要一致的报表和仪表板。
• 你需要像必填字段、唯一值或与其他记录的关系这样约束。
• 多个服务或团队读写相同数据。
• 查询开始扫描大量行，因为无法很好地利用简单索引。
• 你处在受监管或需审计的环境，必须证明规则被遵守。

性能是常见的拐点。用 JSONB 时，过滤常常意味着反复提取值。你可以为 JSON 路径建索引，但需求往往会膨胀成一堆难以维护的索引拼凑。

一个具体示例

原型把“客户请求”存在 JSONB，因为每种请求类型字段不同。后来，运营需要按优先级和 SLA 过滤的队列，财务需要按部门统计，支持需要保证每个请求有客户 ID 和状态。这是规范化表的擅长领域：为常见字段设定清晰列、为客户与团队设外键、用约束阻止坏数据进入。

一个 30 分钟可用的简单决策框架

你不需要一场关于数据库理论的大辩论。你需要和构建与使用系统的人（开发、运营、支持，也许还有财务）一起，写下一个快速答案：哪里需要灵活性比严格结构更重要？

目标不是选一个唯一的赢家，而是为产品的不同部分选择合适的模型。

五步检查清单

1. 列出你最重要的 10 个界面和它们背后的明确问题。例如：“打开客户记录”、“查找逾期订单”、“导出上月的支付”。如果你不能说出问题，就无法为其设计。

2. 标出必须每次都正确的字段。这些是硬性规则：状态、金额、日期、归属、权限。如果错误会造成金钱损失或引发支持危机，通常应放在带约束的普通列里。

3. 标记哪些字段经常变更、哪些很少变更。每周变更（新表单问题、合作方特定细节）是强烈的 JSONB 候选；很少变更的“核心”字段更适合规范化。

4. 决定哪些字段必须在 UI 中可搜索、可筛选或可排序。用户经常按它们过滤的字段通常更适合做为一等列（或对 JSONB 的路径做精心的索引）。

5. 为每个区域选择模型。常见的分配是：对核心实体和工作流用规范化表，对附加和快速变化的元数据用 JSONB。

性能基础——不必迷失于细节

速度通常来自一件事：让最常见的问题变得廉价可答。比起意识形态，这点更重要。

如果你用 JSONB，尽量保持它小且可预测。少量附加字段没问题，但巨大的、不断变化的 blob 很难索引也很容易被滥用。如果你知道某个键会存在（比如 “priority” 或 “source”），保持键名和类型一致。

索引不是魔法。它们以更慢的写入和更多磁盘换取更快的读取。只为你经常过滤或联表的字段建索引，并且要按你实际查询的形态来建。

建索引经验法则

• 对常见过滤字段（status、owner_id、created_at、updated_at）使用普通 btree 索引。
• 当你经常在 JSONB 内搜索时，为该列使用 GIN 索引。
• 对一两个热点 JSON 字段（比如 (meta->>'priority')）优先使用表达式索引，而不是索引整个 JSONB。
• 当只有一部分行有意义时，使用部分索引（例如只对 status = 'open' 的行建索引）。

避免在 JSONB 中把数字和日期存为字符串。"10" 排在 "2" 之前，日期计算也会变得麻烦。最好把数字和时间戳放在真实的数值或时间列，或至少在 JSON 中以数字形式存储。

混合模型常常获胜：把核心字段放在列中，把灵活的附加项放在 JSONB。例如：一张运维表有 id、status、owner_id、created_at 作为列，再有一个 meta JSONB 存可选答案。

导致日后痛苦的常见错误

JSONB 在早期会给人自由感。痛苦通常在几个月后显现，当更多人触及数据且“能用就行”变成“我们无法在不破坏东西的情况下变更”时。

这些模式造成大部分清理工作：

• 把 JSONB 当垃圾桶。若每个团队存略微不同的形状，会在各处写出自定义解析逻辑。制定基本约定：一致的键名、明确的日期格式，以及 JSON 内的小版本字段。
• 把核心实体藏在 JSONB 里。把客户、订单或权限只存为 blob 起初看似简单，但随后联表变得笨拙、约束难以强制、重复数据出现。把 who/what/when 放在列里，选项细节放到 JSONB。
• 等到紧急时才考虑迁移。如果你不追踪有哪些键、它们如何变化、哪些是“正式”的，第一次真正的迁移就会很冒险。
• 以为 JSONB 天生既灵活又快。没有规则的灵活性只是混乱。速度取决于访问模式和索引。
• 通过长期更改键来破坏分析。重命名 status 为 state、把数字换成字符串或混用时区都能悄悄毁掉报表。

一个具体例子：团队用 tickets 表加一个 details JSONB 存表单答案。后来财务要按类别做周统计，运营要做 SLA 跟踪，支持要看“按团队打开的工单”。如果类别和时间戳在键名和格式上漂移，每个报表都会变成一次性查询。

当原型变成关键业务时的迁移计划

当原型开始处理工资、库存或客户支持时，“我们之后会修数据”就不再可接受。最安全的路径是小步迁移，让旧的 JSONB 数据在新结构稳定前仍能工作。

分阶段方法避免一次性改动的高风险：

• 先设计目标。写出目标表、主键和命名规则。决定什么是真正的实体（Customer、Ticket、Order），什么保持灵活（备注、可选属性）。
• 在旧数据旁边建立新表。保留 JSONB 列，同时并行添加规范化表和索引。
• 分批回填并验证。从最近的记录开始，把 JSONB 字段拷贝到新表，按批次验证行数、必填字段非空以及抽查数据。
• 先切换读再切写。先把查询和报表改为从新表读取，当输出一致后再把新的变更写入规范化表。
• 最后锁定。停止写入 JSONB，然后删除或冻结旧字段。添加约束（外键、唯一规则），防止坏数据回流。

在最终切换前：

• 并行运行新旧两套路径一周并比较输出。
• 监控慢查询并按需添加索引。
• 准备回滚计划（功能开关或配置切换）。
• 向团队明确写入切换的精确时间。

在你下定决心前的快速检查

在锁定方法前，做个现实检查。在变更还便宜时，这些问题能捕捉到大多数未来的麻烦。

决定大部分结果的五个问题

• 我们现在（或下个版本）是否需要唯一性、必填字段或严格类型？
• 哪些字段必须在 UI 中可过滤和排序（搜索、状态、负责人、日期）？
• 我们是否很快需要仪表板、导出或发给财务/运维的报表？
• 我们能在 10 分钟内把数据模型解释给新同事，而不用含糊其辞吗？
• 如果迁移破坏了工作流，我们的回滚计划是什么？

如果前三项的回答是“是”，你已经倾向于规范化表（或至少混合：核心字段规范化，长尾属性放 JSONB）。如果只有最后一项是“是”，那更可能是流程问题而非模式问题。

简单的经验法则

当数据形状还不清楚但你能列出一小组稳定字段（如 id、owner、status、created_at）时，使用 JSONB。只要人们依赖一致的过滤、可靠的导出或严格校验，灵活性的成本会迅速上升。

示例：从灵活表单到可靠的运维系统

想象一个每周变化的客户支持录入表。某周你添加 “device model”，下周添加 “refund reason”，然后把 “priority” 重命名为 “urgency”。早期把表单载荷放在一个 JSONB 列里看上去很完美：你可以在不迁移的情况下发布变更。

三个月后，管理者想要像 “urgency = high 且 device model 以 iPhone 开头” 这样的过滤，想要基于客户等级的 SLA，和必须与上周数据一致的周报。

失败模式是可预测的：有人会问 “这个字段去哪了？” 旧记录用了不同的键名、值的类型改变了（"3" vs 3），或一半工单根本没有这个字段。报表变成了由特殊处理拼凑而成。

一个实用的中间方案是混合设计：把稳定的、对业务关键的字段做成真实列（created_at、customer_id、status、urgency、sla_due_at），把新或罕见字段放在 JSONB 的扩展区。

一个低干扰的时间线示例：

• 第 1 周：选出 5 到 10 个必须可过滤和可报告的字段，添加列。
• 第 2 周：先从最近的记录回填这些列，然后回填旧记录。
• 第 3 周：把写入更新为同时写入列和 JSONB（临时双写）。
• 第 4 周：把读取和报表切换到列。JSONB 只保留作为附加项。

下一步：决定、记录并持续交付

如果你什么都不做，决定会自己被做出。原型会膨胀、边界固化，每次改动都会变得风险更高。更好的做法是现在做一个小而明确的书面决定，然后继续构建。

列出你应用必须快速回答的 5 到 10 个问题（“显示该客户的所有未完成订单”、“按邮箱查找用户”、“按月报告收入”）。在每个问题旁写下不能破坏的约束（唯一邮箱、必填状态、有效总额）。然后画出清晰边界：把那些经常变化且很少被筛选或联表的字段留在 JSONB，把所有需要搜索、排序、联表或必须每次校验的字段提升为列和表。

如果你使用能生成真实应用的无代码平台，这个拆分随时间管理起来会更容易。例如，AppMaster (appmaster.io) 允许你以可视化方式建模 PostgreSQL 表，并在需求变化时重新生成底层后端和应用，这会让迭代式的模式变更和有计划的迁移痛苦更小。