JSON 有毒能污染你的PG数据库

本期播客

JSON 有毒, 肯能污染你的PostgreSQL数据库

参考: https://www.enterprisedb.com/blog/validating-shape-your-json-data

别再让垃圾JSON污染你的PostgreSQL数据库！这个扩展让你在数据库层面彻底终结数据混乱！

数据完整性是数据库的底线，而JSONB的灵活性正在悄悄瓦解它。

一、JSONB的“甜蜜陷阱”

PostgreSQL的JSONB类型无疑是个伟大的发明——你可以在不预先定义列的情况下存储任意结构的数据，这种灵活性让开发人员欢呼雀跃。但亲爱的DBA，你有没有想过：当每个应用、每个脚本、每个手动INSERT都能往JSONB里塞数据时，你的数据库还剩下多少尊严？

应用层验证？呵呵。当你有三个微服务、两个遗留系统、一个定时脚本同时写入同一张表时，总有一个会“忘记”验证。更别提那些直接连上数据库执行UPDATE的“紧急修复”——他们可不会关心你的JSON Schema长什么样。

数据库是数据的最后一道防线。如果防线失守，下游的数据分析、报表、甚至核心业务逻辑都会建立在流沙之上。

二、第一性原理：为什么必须在数据库层面验证？

让我们回到第一性原理思考：数据库的核心职责是什么？是保证数据的完整性、一致性和持久性。

对于结构化数据，我们有类型系统、NOT NULL约束、外键。但对于JSONB，我们有什么？几乎什么都没有——最多加个CHECK约束写几行丑陋的PL/pgSQL，验证一下某个字段是否存在。但你要验证email格式？验证对象必须包含至少一个属性？验证数组元素唯一？SQL不是为JSON验证设计的，强行写出来的代码比意大利面条还难维护。

所以，当JSON Schema成为业界的验证标准时，把JSON Schema引入数据库，就是逻辑的必然。

三、重磅武器：json_schema_validate 扩展

现在，一个名为json_schema_validate的PostgreSQL扩展（开源，PostgreSQL许可证）解决了这个痛点。它让你能在数据库内直接使用JSON Schema规范验证JSON/JSONB数据，而且性能惊人。

3.1 用法简单到爆

CREATETABLEevents (  
idserial PRIMARY KEY,  
data jsonb NOTNULLCHECK (  
        jsonschema_is_valid(data, '{  
            "type": "object",  
            "required": ["event_type", "timestamp"],  
            "properties": {  
                "event_type": {"type": "string", "enum": ["click", "view", "purchase"]},  
                "timestamp": {"type": "string", "format": "date-time"},  
                "user_id": {"type": "integer", "minimum": 1},  
                "metadata": {"type": "object"}  
            },  
            "additionalProperties": false  
        }'::jsonschema_compiled)  
    )  
);  

关键点：::jsonschema_compiled强制转换让PostgreSQL只解析一次schema并缓存，而不是每行都解析。这才是性能的关键。

3.2 支持哪些JSON Schema功能？

支持Draft 7的核心关键字，足够覆盖99%的日常场景：

• 基础验证：type, required, enum, const
• 字符串：minLength, maxLength, pattern, format（日期、邮箱、IP、URI、UUID等）
• 数字：minimum, exclusiveMinimum, maximum, exclusiveMaximum, multipleOf
• 数组：items, minItems, maxItems, uniqueItems, contains
• 对象：properties, additionalProperties, patternProperties, propertyNames, minProperties, maxProperties
• 组合：allOf, anyOf, oneOf, not
• 条件：if/then/else
• 引用：$ref, $defs（本地引用）

不支持（目前）的如prefixItems、unevaluatedProperties等高级特性，但对大多数业务来说，现有的功能已经能让你构建滴水不漏的验证规则。

3.3 两种模式：布尔判断 vs 错误诊断

• jsonschema_is_valid()：用于CHECK约束，返回true/false。
• jsonschema_validate()：返回错误数组，告诉你哪里错了。这在应用逻辑或触发器里调试时非常有用。

SELECT jsonschema_validate(  
'{"name": 123, "tags": "not-an-array"}',  
'{"properties": {"name": {"type": "string"}, "tags": {"type": "array"}}}'
);  
-- [{"path": "name", "message": "Expected type string but got number"},  
--  {"path": "tags", "message": "Expected type array but got string"}]  

四、硬核性能：把对手按在地上摩擦

光说功能不够，DBA最关心性能。作者拿这个扩展和目前已知的另一个JSON Schema验证扩展pg_jsonschema（Supabase出品的Rust扩展）做了对比测试。

测试环境：PostgreSQL 17.2，aarch64 Linux，10万行数据，验证每个行是否符合schema。

为什么能快这么多？

• C语言实现：直接操作PostgreSQL内部数据结构，没有Rust/serde序列化开销。
• 正则缓存：每个session只编译一次正则，之后直接匹配。pg_jsonschema看起来每行都重编译。
• 预编译schema：::jsonschema_compiled避免重复解析JSON schema。

特别提醒：pg_jsonschema使用Rust的jsonschema crate，功能更全（支持更多草案特性），但性能上被这个C扩展碾压。在正则密集的场景下，73倍的速度差距意味着你的查询可能从几分钟变成几秒钟。

五、前提崩塌：什么时候不要用它？

第一性原理要求我们考虑条件变化。如果以下条件崩塌，你的选择可能需要调整：

5.1 你需要完整的JSON Schema 2019-09/2020-12支持

json_schema_validate目前主要支持Draft 7，高级特性如unevaluatedProperties、prefixItems等暂不支持。如果你必须使用这些特性（例如从外部系统导入严格的2020-12 schema），那pg_jsonschema可能是更好的选择，尽管慢一点。

5.2 你的schema包含外部$ref引用

目前只支持本地引用（#/...）。如果你需要从网络或文件系统加载schema，这个扩展帮不了你。不过，你可以在应用层预取并内联。

5.3 你完全不能容忍任何编译开销

虽然预编译schema已经极大优化，但如果你的schema本身巨大且变化频繁，每次session重新编译可能会有额外开销。但这种情况很少见——通常schema是稳定的。

5.4 性能瓶颈不在数据库而在网络或应用

如果你的JSON验证操作本身很少，或者应用层CPU已经100%，那么数据库验证的性能优势可能不是首要考虑。但DBA的职责是为未来预留容量，优化从每一处做起。

六、DBA行动指南：如何立即保护你的数据库？

1. 评估你的JSONB字段：找出那些被多个应用写入、或者缺乏严格验证的JSONB列。
2. 设计JSON Schema：根据业务需求，定义清晰的schema。参考Draft 7规范，用上required、pattern、format等关键字。
3. 部署扩展：

   git clone https://github.com/supabase/json_schema_validate.git  
   cd json_schema_validate  
   make PG_CONFIG=/path/to/pg_config  
   make install PG_CONFIG=/path/to/pg_config  
   psql -d yourdb -c "CREATE EXTENSION json_schema_validate;"
   

4. 添加CHECK约束：先测试jsonschema_is_valid，确认无误后添加约束。建议在业务低峰期操作，并使用NOT VALID选项先验证新数据，再逐步修复旧数据。
5. 监控错误：利用jsonschema_validate编写触发器，记录验证失败的插入/更新，及时发现数据质量问题。

七、结语：让数据库重新硬起来

JSONB给了我们灵活性，但DBA不能容忍数据变成垃圾场。在数据库层面强制JSON Schema验证，不是过度设计，而是对数据最基本的尊重。

json_schema_validate扩展用C语言实现，性能卓越，功能实用，完全开源。它让我们能在最底层守住数据完整性的防线。虽然它不完美，但对绝大多数业务场景，它就是那把“一刀斩断脏数据”的利剑。

别再心存侥幸了。今天你不验证数据，明天数据就会给你颜色看。

延伸思考：如果你的团队还在争论“应用层验证就够了”，把这篇文章甩给他们。告诉他们：数据库是最后一道防线，而这道防线，现在有了重武器。

附：本文性能数据基于作者公开的benchmark，实际环境可能略有差异。建议在你的生产环境硬件上进行测试验证。