JSONB、全文检索与事务PostgreSQL 适合哪些 AI 业务数据码海寻道 · 大模型、智能体与 RAG 工程组件系列第 12 篇PostgreSQL 在 AI 项目中的价值往往不只来自“它是一个关系数据库”还来自三个很实用的能力组合JSONB保存结构变化较快的半结构化数据全文检索在不接入向量模型时完成词项检索事务与并发控制保证业务状态可靠变化。这三项能力刚好对应大模型应用中大量真实问题工具参数和模型输出结构变化、知识库关键词检索、文档发布和任务状态的一致性。实际使用时三种能力应分别看待JSONB 解决结构变化全文检索解决词项匹配事务解决状态变化。它们可以出现在同一张表中但不意味着所有数据都应该塞进一个 JSONB 字段也不意味着全文检索可以替代语义检索。一、JSONB 适合保存什么JSONB 是 PostgreSQL 中以二进制形式保存 JSON 数据的类型适合保存结构不完全固定、需要查询或建立索引的扩展信息。AI 应用中的典型 JSONB 数据包括模型调用的请求参数工具调用参数和返回摘要RAG 检索详情文档解析出的表格或版面信息不同模型返回的可变评测结果Agent 节点的扩展状态。例如CREATETABLEllm_runs(id BIGSERIALPRIMARYKEY,request_idTEXTNOTNULL,model_nameTEXTNOTNULL,input_tokensINTEGER,output_tokensINTEGER,metadata JSONBNOTNULLDEFAULT{}::jsonb,created_at TIMESTAMPTZNOTNULLDEFAULTnow());INSERTINTOllm_runs(request_id,model_name,metadata)VALUES(req_001,example-model,{ route: knowledge_qa, retrieval: {top_k: 20, rerank_top_n: 5}, tools: [query_order] }::jsonb);二、JSONB 不应该替代稳定关系字段如果tenant_id、user_id、status和created_at是每次查询都会用到的核心字段就不应该全部藏在 JSONB 里。推荐的方式是稳定、常查询、需要约束的字段 → 普通列 结构变化快、可选、扩展性的字段 → JSONB例如CREATETABLEtool_calls(id BIGSERIALPRIMARYKEY,tenant_id UUIDNOTNULL,user_id UUIDNOTNULL,tool_nameTEXTNOTNULL,statusTEXTNOTNULL,arguments JSONBNOTNULL,result JSONB,created_at TIMESTAMPTZNOTNULLDEFAULTnow());这里的租户、用户、工具名和状态是关系列具体参数和结果则适合用 JSONB 保存。三、JSONB 如何查询PostgreSQL 支持多种 JSON/JSONB 操作符-- 获取字段SELECTmetadata-retrievalASretrievalFROMllm_runs;-- 获取文本值SELECTmetadata-routeASrouteFROMllm_runs;-- 判断 JSON 是否包含某个结构SELECT*FROMllm_runsWHEREmetadata {route: knowledge_qa}::jsonb;对于经常使用包含查询的 JSONB 字段可以考虑 GIN 索引CREATEINDEXidx_llm_runs_metadataONllm_runsUSINGGIN(metadata);索引不是越多越好。JSONB 数据结构复杂、字段分布不稳定时应该用EXPLAIN (ANALYZE, BUFFERS)验证索引是否真的被使用。四、全文检索解决什么问题全文检索适合在文档中查找词项、词组和文本相关性不需要先调用 Embedding 模型。它适合错误码产品型号订单号前缀规章制度关键词代码符号精确词语和短语。PostgreSQL 使用tsvector表示规范化后的文本搜索数据使用tsquery表示搜索条件。一个最小示例CREATETABLEknowledge_chunks(id BIGSERIALPRIMARYKEY,contentTEXTNOTNULL,search_vector TSVECTOR);UPDATEknowledge_chunksSETsearch_vectorto_tsvector(simple,content);CREATEINDEXidx_knowledge_chunks_searchONknowledge_chunksUSINGGIN(search_vector);SELECTid,contentFROMknowledge_chunksWHEREsearch_vector plainto_tsquery(simple,年休假 申请);实际中文分词和词法归一化要结合配置和扩展评估。不要把英文配置直接当成中文检索方案。五、用生成列保持全文检索字段同步如果每次更新内容都手动更新search_vector容易出现遗漏。可以使用生成列或触发器让全文检索字段随原文变化。需要注意生成列、触发器和异步索引任务的边界原文更新后数据库内的tsvector可以同步更新但 Embedding、Milvus/pgvector 向量和 Reranker 评测仍然需要异步处理。不要把耗时的模型调用放进数据库触发器否则一次普通 UPDATE 可能变成长时间阻塞。示意CREATETABLEarticles(id BIGSERIALPRIMARYKEY,titleTEXTNOTNULL,bodyTEXTNOTNULL,search_vector TSVECTOR GENERATED ALWAYSAS(to_tsvector(simple,coalesce(title,)|| ||coalesce(body,)))STORED);CREATEINDEXidx_articles_searchONarticlesUSINGGIN(search_vector);是否使用生成列要根据 PostgreSQL 版本、配置和文本处理需求验证。复杂的中文分词、清洗和多字段权重可能需要在应用层预处理或使用专门方案。六、全文检索和向量检索不是二选一两者关注的信号不同全文检索关键词、词项、短语、编号 向量检索语义、概念、表达差异例如问题PostgreSQL 16 中 JSONB 索引失效怎么办向量检索可以理解“JSONB 索引失效”的整体问题全文检索则能准确匹配“PostgreSQL 16”和“JSONB”。实际 RAG 往往采用全文召回 向量召回 ↓ 合并、去重、重排序 ↓ 交给大模型七、事务为什么对 AI 应用重要很多人以为模型调用都是“读数据”不需要事务。实际上知识库发布、文档删除、任务状态、用户配额、反馈记录和工具操作都可能需要一致性。例如发布文档时可能要同时完成更新文档版本 创建索引任务 标记旧版本归档 记录审计事件如果更新文档成功但索引任务创建失败系统可能显示“已发布”检索却仍然使用旧数据。可以用事务保护数据库内部的状态变化事务只适合保护 PostgreSQL 内部的多个状态变化不会自动把 PostgreSQL、对象存储、向量库和消息队列变成一个分布式事务。跨系统流程应使用状态机、Outbox、幂等键和补偿任务。事务保存文档版本 写入 outbox 事件 提交 Worker读取事件更新 Embedding/向量索引 失败记录重试次数和错误继续补偿 成功更新索引状态为 readyBEGIN;UPDATEdocumentsSETcurrent_version3,statusindexingWHEREid00000000-0000-0000-0000-000000000001;INSERTINTOingestion_jobs(id,document_id,job_type,status)VALUES(gen_random_uuid(),00000000-0000-0000-0000-000000000001,rebuild_embedding,pending);COMMIT;注意事务只能保证同一个数据库内的原子性。PostgreSQL 成功提交并不代表 Milvus、对象存储和消息队列已经同时成功。跨系统一致性需要事件、补偿、幂等和状态机设计。八、事务隔离与并发更新PostgreSQL 使用多版本并发控制等机制处理多个会话同时读写数据的情况。AI 应用中的典型并发问题包括两个 Worker 同时处理同一个文档用户重复提交同一个任务两个 Agent 请求同时修改同一业务对象文档发布和删除同时发生。解决方法可能包括唯一约束和幂等键行级锁SELECT ... FOR UPDATE乐观锁版本号SERIALIZABLE或适当的事务隔离级别失败重试和补偿任务。具体方案要根据冲突概率和业务代价选择。事务隔离级别越严格不一定越适合所有高并发任务。九、AI 数据表的一个组合示例CREATETABLEknowledge_documents(id UUIDPRIMARYKEY,tenant_id UUIDNOTNULL,titleTEXTNOTNULL,contentTEXTNOTNULL,statusTEXTNOTNULL,metadata JSONBNOTNULLDEFAULT{}::jsonb,search_vector TSVECTOR GENERATED ALWAYSAS(to_tsvector(simple,coalesce(title,)|| ||coalesce(content,)))STORED,created_at TIMESTAMPTZNOTNULLDEFAULTnow());CREATEINDEXidx_docs_tenant_statusONknowledge_documents(tenant_id,status);CREATEINDEXidx_docs_metadataONknowledge_documentsUSINGGIN(metadata);CREATEINDEXidx_docs_searchONknowledge_documentsUSINGGIN(search_vector);这个表可以用于轻量级文档管理和全文检索。向量检索是否放在同一张表中要等到 pgvector 文章中结合规模和查询模式讨论。十、哪些 AI 业务数据适合 PostgreSQL非常适合用户、组织、租户和权限文档、版本、来源和发布状态会话、消息、反馈和引用任务、重试、错误和审计模型调用统计和预算工具定义与调用记录需要事务的订单、审批和业务对象。适合但需要设计JSONB 形式的模型输出文档解析结构大量聊天历史全文索引pgvector 向量。通常不建议直接承担大量原始图片、视频和大型附件需要独立水平扩展的大规模向量检索高吞吐消息分发不经权限控制的任意 Agent SQL。十一、JSONB、全文检索和事务的边界三种能力不能解决所有问题JSONB → 解决结构扩展不解决业务建模 全文检索 → 解决词项检索不等于语义检索 事务 → 解决数据库内一致性不自动解决跨系统一致性把边界理解清楚才能避免“一个功能解决全部问题”的误用。十二、上线前检查清单稳定业务字段没有全部塞进 JSONBJSONB 查询字段有实际执行计划验证全文检索配置与语言、分词需求匹配文本更新时搜索字段保持同步文档发布和任务创建有事务保护跨 PostgreSQL、向量库、对象存储的流程可补偿Worker 具备幂等和并发控制会话和调用日志有归档与脱敏策略权限过滤不依赖自然语言提示高风险写操作有审计和人工确认。没有在数据库事务或触发器中同步调用模型 API跨 PostgreSQL、对象存储和向量库的流程有 Outbox、幂等和补偿JSONB、全文检索和向量字段分别有更新与重建策略结语PostgreSQL 的强项是把变化纳入秩序JSONB 给了 AI 应用必要的灵活性全文检索提供了不依赖向量模型的词项检索事务和并发控制则让文档、任务和业务状态能够可靠变化。它们组合起来正好适合承载 AI 应用中“既有结构化事实又有半结构化模型数据”的部分。下一篇进入向量能力《pgvector 入门用 PostgreSQL 直接实现向量检索》参考资料PostgreSQL DocumentationJSON Functions and OperatorsPostgreSQL DocumentationText Search Functions and OperatorsPostgreSQL DocumentationConcurrency ControlPostgreSQL DocumentationRow Security PoliciesPostgreSQL DocumentationTransactions and Identifiers本文为“码海寻道”原创技术文章。SQL、索引和事务行为应以实际 PostgreSQL 版本、配置和执行计划为准。
JSONB、全文检索与事务PostgreSQL 适合哪些 AI 业务数据码海寻道 · 大模型、智能体与 RAG 工程组件系列第 12 篇PostgreSQL 在 AI 项目中的价值往往不只来自“它是一个关系数据库”还来自三个很实用的能力组合JSONB保存结构变化较快的半结构化数据全文检索在不接入向量模型时完成词项检索事务与并发控制保证业务状态可靠变化。这三项能力刚好对应大模型应用中大量真实问题工具参数和模型输出结构变化、知识库关键词检索、文档发布和任务状态的一致性。实际使用时三种能力应分别看待JSONB 解决结构变化全文检索解决词项匹配事务解决状态变化。它们可以出现在同一张表中但不意味着所有数据都应该塞进一个 JSONB 字段也不意味着全文检索可以替代语义检索。一、JSONB 适合保存什么JSONB 是 PostgreSQL 中以二进制形式保存 JSON 数据的类型适合保存结构不完全固定、需要查询或建立索引的扩展信息。AI 应用中的典型 JSONB 数据包括模型调用的请求参数工具调用参数和返回摘要RAG 检索详情文档解析出的表格或版面信息不同模型返回的可变评测结果Agent 节点的扩展状态。例如CREATETABLEllm_runs(id BIGSERIALPRIMARYKEY,request_idTEXTNOTNULL,model_nameTEXTNOTNULL,input_tokensINTEGER,output_tokensINTEGER,metadata JSONBNOTNULLDEFAULT{}::jsonb,created_at TIMESTAMPTZNOTNULLDEFAULTnow());INSERTINTOllm_runs(request_id,model_name,metadata)VALUES(req_001,example-model,{ route: knowledge_qa, retrieval: {top_k: 20, rerank_top_n: 5}, tools: [query_order] }::jsonb);二、JSONB 不应该替代稳定关系字段如果tenant_id、user_id、status和created_at是每次查询都会用到的核心字段就不应该全部藏在 JSONB 里。推荐的方式是稳定、常查询、需要约束的字段 → 普通列 结构变化快、可选、扩展性的字段 → JSONB例如CREATETABLEtool_calls(id BIGSERIALPRIMARYKEY,tenant_id UUIDNOTNULL,user_id UUIDNOTNULL,tool_nameTEXTNOTNULL,statusTEXTNOTNULL,arguments JSONBNOTNULL,result JSONB,created_at TIMESTAMPTZNOTNULLDEFAULTnow());这里的租户、用户、工具名和状态是关系列具体参数和结果则适合用 JSONB 保存。三、JSONB 如何查询PostgreSQL 支持多种 JSON/JSONB 操作符-- 获取字段SELECTmetadata-retrievalASretrievalFROMllm_runs;-- 获取文本值SELECTmetadata-routeASrouteFROMllm_runs;-- 判断 JSON 是否包含某个结构SELECT*FROMllm_runsWHEREmetadata {route: knowledge_qa}::jsonb;对于经常使用包含查询的 JSONB 字段可以考虑 GIN 索引CREATEINDEXidx_llm_runs_metadataONllm_runsUSINGGIN(metadata);索引不是越多越好。JSONB 数据结构复杂、字段分布不稳定时应该用EXPLAIN (ANALYZE, BUFFERS)验证索引是否真的被使用。四、全文检索解决什么问题全文检索适合在文档中查找词项、词组和文本相关性不需要先调用 Embedding 模型。它适合错误码产品型号订单号前缀规章制度关键词代码符号精确词语和短语。PostgreSQL 使用tsvector表示规范化后的文本搜索数据使用tsquery表示搜索条件。一个最小示例CREATETABLEknowledge_chunks(id BIGSERIALPRIMARYKEY,contentTEXTNOTNULL,search_vector TSVECTOR);UPDATEknowledge_chunksSETsearch_vectorto_tsvector(simple,content);CREATEINDEXidx_knowledge_chunks_searchONknowledge_chunksUSINGGIN(search_vector);SELECTid,contentFROMknowledge_chunksWHEREsearch_vector plainto_tsquery(simple,年休假 申请);实际中文分词和词法归一化要结合配置和扩展评估。不要把英文配置直接当成中文检索方案。五、用生成列保持全文检索字段同步如果每次更新内容都手动更新search_vector容易出现遗漏。可以使用生成列或触发器让全文检索字段随原文变化。需要注意生成列、触发器和异步索引任务的边界原文更新后数据库内的tsvector可以同步更新但 Embedding、Milvus/pgvector 向量和 Reranker 评测仍然需要异步处理。不要把耗时的模型调用放进数据库触发器否则一次普通 UPDATE 可能变成长时间阻塞。示意CREATETABLEarticles(id BIGSERIALPRIMARYKEY,titleTEXTNOTNULL,bodyTEXTNOTNULL,search_vector TSVECTOR GENERATED ALWAYSAS(to_tsvector(simple,coalesce(title,)|| ||coalesce(body,)))STORED);CREATEINDEXidx_articles_searchONarticlesUSINGGIN(search_vector);是否使用生成列要根据 PostgreSQL 版本、配置和文本处理需求验证。复杂的中文分词、清洗和多字段权重可能需要在应用层预处理或使用专门方案。六、全文检索和向量检索不是二选一两者关注的信号不同全文检索关键词、词项、短语、编号 向量检索语义、概念、表达差异例如问题PostgreSQL 16 中 JSONB 索引失效怎么办向量检索可以理解“JSONB 索引失效”的整体问题全文检索则能准确匹配“PostgreSQL 16”和“JSONB”。实际 RAG 往往采用全文召回 向量召回 ↓ 合并、去重、重排序 ↓ 交给大模型七、事务为什么对 AI 应用重要很多人以为模型调用都是“读数据”不需要事务。实际上知识库发布、文档删除、任务状态、用户配额、反馈记录和工具操作都可能需要一致性。例如发布文档时可能要同时完成更新文档版本 创建索引任务 标记旧版本归档 记录审计事件如果更新文档成功但索引任务创建失败系统可能显示“已发布”检索却仍然使用旧数据。可以用事务保护数据库内部的状态变化事务只适合保护 PostgreSQL 内部的多个状态变化不会自动把 PostgreSQL、对象存储、向量库和消息队列变成一个分布式事务。跨系统流程应使用状态机、Outbox、幂等键和补偿任务。事务保存文档版本 写入 outbox 事件 提交 Worker读取事件更新 Embedding/向量索引 失败记录重试次数和错误继续补偿 成功更新索引状态为 readyBEGIN;UPDATEdocumentsSETcurrent_version3,statusindexingWHEREid00000000-0000-0000-0000-000000000001;INSERTINTOingestion_jobs(id,document_id,job_type,status)VALUES(gen_random_uuid(),00000000-0000-0000-0000-000000000001,rebuild_embedding,pending);COMMIT;注意事务只能保证同一个数据库内的原子性。PostgreSQL 成功提交并不代表 Milvus、对象存储和消息队列已经同时成功。跨系统一致性需要事件、补偿、幂等和状态机设计。八、事务隔离与并发更新PostgreSQL 使用多版本并发控制等机制处理多个会话同时读写数据的情况。AI 应用中的典型并发问题包括两个 Worker 同时处理同一个文档用户重复提交同一个任务两个 Agent 请求同时修改同一业务对象文档发布和删除同时发生。解决方法可能包括唯一约束和幂等键行级锁SELECT ... FOR UPDATE乐观锁版本号SERIALIZABLE或适当的事务隔离级别失败重试和补偿任务。具体方案要根据冲突概率和业务代价选择。事务隔离级别越严格不一定越适合所有高并发任务。九、AI 数据表的一个组合示例CREATETABLEknowledge_documents(id UUIDPRIMARYKEY,tenant_id UUIDNOTNULL,titleTEXTNOTNULL,contentTEXTNOTNULL,statusTEXTNOTNULL,metadata JSONBNOTNULLDEFAULT{}::jsonb,search_vector TSVECTOR GENERATED ALWAYSAS(to_tsvector(simple,coalesce(title,)|| ||coalesce(content,)))STORED,created_at TIMESTAMPTZNOTNULLDEFAULTnow());CREATEINDEXidx_docs_tenant_statusONknowledge_documents(tenant_id,status);CREATEINDEXidx_docs_metadataONknowledge_documentsUSINGGIN(metadata);CREATEINDEXidx_docs_searchONknowledge_documentsUSINGGIN(search_vector);这个表可以用于轻量级文档管理和全文检索。向量检索是否放在同一张表中要等到 pgvector 文章中结合规模和查询模式讨论。十、哪些 AI 业务数据适合 PostgreSQL非常适合用户、组织、租户和权限文档、版本、来源和发布状态会话、消息、反馈和引用任务、重试、错误和审计模型调用统计和预算工具定义与调用记录需要事务的订单、审批和业务对象。适合但需要设计JSONB 形式的模型输出文档解析结构大量聊天历史全文索引pgvector 向量。通常不建议直接承担大量原始图片、视频和大型附件需要独立水平扩展的大规模向量检索高吞吐消息分发不经权限控制的任意 Agent SQL。十一、JSONB、全文检索和事务的边界三种能力不能解决所有问题JSONB → 解决结构扩展不解决业务建模 全文检索 → 解决词项检索不等于语义检索 事务 → 解决数据库内一致性不自动解决跨系统一致性把边界理解清楚才能避免“一个功能解决全部问题”的误用。十二、上线前检查清单稳定业务字段没有全部塞进 JSONBJSONB 查询字段有实际执行计划验证全文检索配置与语言、分词需求匹配文本更新时搜索字段保持同步文档发布和任务创建有事务保护跨 PostgreSQL、向量库、对象存储的流程可补偿Worker 具备幂等和并发控制会话和调用日志有归档与脱敏策略权限过滤不依赖自然语言提示高风险写操作有审计和人工确认。没有在数据库事务或触发器中同步调用模型 API跨 PostgreSQL、对象存储和向量库的流程有 Outbox、幂等和补偿JSONB、全文检索和向量字段分别有更新与重建策略结语PostgreSQL 的强项是把变化纳入秩序JSONB 给了 AI 应用必要的灵活性全文检索提供了不依赖向量模型的词项检索事务和并发控制则让文档、任务和业务状态能够可靠变化。它们组合起来正好适合承载 AI 应用中“既有结构化事实又有半结构化模型数据”的部分。下一篇进入向量能力《pgvector 入门用 PostgreSQL 直接实现向量检索》参考资料PostgreSQL DocumentationJSON Functions and OperatorsPostgreSQL DocumentationText Search Functions and OperatorsPostgreSQL DocumentationConcurrency ControlPostgreSQL DocumentationRow Security PoliciesPostgreSQL DocumentationTransactions and Identifiers本文为“码海寻道”原创技术文章。SQL、索引和事务行为应以实际 PostgreSQL 版本、配置和执行计划为准。
