数据库基础体系 · 第 25/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。
PostgreSQL JSONB 与扩展生态：全文检索、分区、FDW 和扩展边界
在 PostgreSQL 中，jsonb、全文检索、分区表和 FDW（Foreign Data Wrapper，外部数据包装器）经常出现在同一类系统里：
- 业务对象的字段并不完全固定，需要使用
jsonb保存扩展属性； - 需要对标题、正文以及 JSON 中的字段进行全文检索；
- 数据量增长后按时间或租户进行分区；
- 一部分历史数据、异构数据或其他 PostgreSQL 实例需要通过 FDW 访问；
- 需要使用
pg_trgm、unaccent、pgvector等扩展补充能力。
这些功能并不是一个“JSONB 搜索开关”。它们分别位于 PostgreSQL 的数据类型、索引、查询规划、分区路由、事务管理和扩展加载边界上。理解它们之间的关系，首先要区分三个问题：
- 数据以什么类型保存；
- 查询条件如何表示以及如何建立索引；
- 数据实际位于哪个关系、分区或远程服务器上。
一、JSONB 是数据类型，不是全文检索引擎
1. json 与 jsonb
PostgreSQL 同时提供 json 和 jsonb：
json保存输入文本的 JSON 表示；jsonb将 JSON 解析为二进制结构，便于比较、运算和建立索引。
jsonb 不保证保留对象键的输入顺序，也不会保留重复键。对于类似下面的输入：
{"status": "draft", "status": "published"}
解析为 jsonb 后，重复键不会作为两个独立字段保留，通常只有最后一个值有效。因此，如果输入文本本身的格式、空白、键顺序或重复键具有业务意义，应考虑保存原始文本，而不能只保存 jsonb。
jsonb 的优势是可以直接进行结构化操作：
SELECT
payload->>'title' AS title,
payload->'author'->>'name' AS author_name
FROM documents
WHERE payload @> '{"status": "published"}';
这里有三个不同的操作：
->返回 JSON 值；->>返回文本值；@>判断左侧 JSONB 是否包含右侧 JSONB 结构。
例如：
SELECT
'{"a": 1, "b": {"c": 2}}'::jsonb @> '{"b": {"c": 2}}'::jsonb
AS contains_result;
结果为：
contains_result
-----------------
t
@> 是结构包含，不是文本包含。下面两个条件含义不同：
-- JSON 结构中存在 tags 数组，并且包含 "database"
payload @> '{"tags": ["database"]}'::jsonb
-- 从 JSON 中提取 tags 后，再判断数组中是否包含文本
payload->'tags' ? 'database'
后者要求 tags 是 JSON 对象或数组中适合 ? 操作的值；它并不等价于全文检索。
2. SQL NULL、JSON null 和缺失字段
JSONB 查询中最容易混淆的是三种状态：
- SQL
NULL； - JSON 值
null； - JSON 路径不存在。
例如：
SELECT
'{"x": null}'::jsonb->'x' AS json_null,
'{}'::jsonb->'x' AS missing_value;
二者在显示上都可能表现为 NULL，但语义不同。要判断 JSON 对象是否明确包含某个键，应使用：
payload ? 'x'
而不是仅判断：
payload->'x' IS NULL
因为后者无法区分“键存在且值为 JSON null”和“键不存在”。
如果要把文本字段用于搜索，通常使用 coalesce 处理缺失字段：
coalesce(payload->>'title', '')
这样可以避免字符串拼接中的 SQL NULL 传播。
二、JSONB 的索引：结构查询与文本搜索是两条路径
1. GIN 与 JSONB 操作符类
JSONB 最常用的是 GIN（Generalized Inverted Index，广义倒排索引）。它不是为每行保存一个单一排序键，而是把一个 JSON 文档拆成多个可检索项，从而支持“哪些文档包含这个键、值或路径”的反向查找。
默认的 jsonb_ops 操作符类支持范围较广，例如：
@>：包含；?：是否存在键或数组元素；?|、?&：是否存在任意或全部指定项；- 部分 JSONPath 查询。
示例：
CREATE TABLE documents (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
CREATE INDEX documents_payload_gin_idx
ON documents
USING gin (payload);
如果主要查询结构包含，可以考虑 jsonb_path_ops：
CREATE INDEX documents_payload_path_gin_idx
ON documents
USING gin (payload jsonb_path_ops);
两者的取舍不是“一个新、一个旧”：
jsonb_ops索引表达能力更广，适合多种 JSONB 操作；jsonb_path_ops通常针对包含路径生成更紧凑的索引项，适合大量@>查询；- 选择
jsonb_path_ops后，不能假设所有jsonb_ops支持的键存在性查询都仍然可以使用该索引。
应以实际查询和 EXPLAIN 验证，而不是仅凭索引名称判断：
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM documents
WHERE payload @> '{"status": "published"}'::jsonb;
如果查询条件是：
WHERE payload->>'status' = 'published'
优化器不一定能把它转化为适合上述整列 GIN 的条件。对于稳定的标量路径，表达式索引往往更直接：
CREATE INDEX documents_status_idx
ON documents ((payload->>'status'));
这两个索引解决的是不同问题：
- 整列 GIN 适合 JSON 结构包含和多种结构操作；
- B-tree 表达式索引适合某个路径提取后的等值、排序或范围查询。
2. JSONB 不是“包含字符串”的索引
下面的查询是文本模式匹配：
WHERE payload->>'title' ILIKE '%postgres%'
普通 B-tree 索引无法有效支持前导 %。JSONB GIN 也不会自动把任意字符串拆成全文词项。此时可以选择：
- 将字段提取后交给全文检索；
- 使用
pg_trgm支持相似度和%...%模式； - 对结构化字段使用等值或范围索引。
例如，使用三元组扩展：
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX documents_title_trgm_idx
ON documents
USING gin ((payload->>'title') gin_trgm_ops);
这个索引服务于子字符串匹配和相似度匹配，不等同于 PostgreSQL 全文检索。pg_trgm 将文本拆成三元组；全文检索则先进行分词、归一化并生成词位。
三、PostgreSQL 全文检索的实际数据流
1. tsvector 与 tsquery
PostgreSQL 全文检索的核心是两个类型：
tsvector：文档经过分词和归一化后的词项集合，可以包含词位位置和权重；tsquery：查询条件的逻辑表达式。
例如：
SELECT to_tsvector(
'english',
'PostgreSQL provides powerful database features'
) AS document_vector;
结果的具体词形由文本搜索配置决定。english 配置可能进行词干化，使 features、feature 等词形进入可匹配的归一化结果。
查询：
SELECT to_tsquery('english', 'database & feature');
再使用匹配运算符：
SELECT to_tsvector(
'english',
'PostgreSQL provides powerful database features'
)
@@ to_tsquery('english', 'database & feature')
AS matched;
@@ 的左侧通常是文档向量，右侧是查询向量，但 PostgreSQL 也提供适配的重载。实际工程中应显式使用相同配置，避免索引生成和查询解析采用不同的词法规则。
常见查询构造函数有不同语义：
to_tsquery：输入是 PostgreSQL 全文查询语法；plainto_tsquery：把普通文本解析为 AND 关系的词项；phraseto_tsquery：考虑词项相邻关系；websearch_to_tsquery：接受接近搜索引擎风格的输入，并尽量避免因普通用户输入语法错误而失败。
例如，用户输入不能直接无条件拼接进 to_tsquery：
-- 用户输入 "postgres database" 时通常可以工作
to_tsquery('simple', user_input)
但用户输入 postgres & ( 之类不完整表达式时可能报错。面向搜索框的输入通常更适合：
websearch_to_tsquery('simple', user_input)
这不意味着它可以消除所有业务级输入验证；查询长度、权限过滤和资源消耗仍需控制。
2. 从 JSONB 生成全文检索向量
全文检索不会自动读取 JSONB 内部所有字符串。必须明确指定哪些字段参与搜索：
CREATE TABLE articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
可以建立表达式 GIN 索引：
CREATE INDEX articles_search_idx
ON articles
USING gin (
to_tsvector(
'simple',
coalesce(payload->>'title', '') || ' ' ||
coalesce(payload->>'body', '')
)
);
查询时必须写出等价表达式：
SELECT id, payload->>'title' AS title
FROM articles
WHERE to_tsvector(
'simple',
coalesce(payload->>'title', '') || ' ' ||
coalesce(payload->>'body', '')
)
@@ websearch_to_tsquery('simple', 'jsonb index');
表达式索引的关键条件是：查询表达式要与索引表达式在语义和函数配置上保持一致。若索引使用 simple，查询却使用 english，即使文本相同，词项也可能不同，索引无法按预期服务查询。
另一种更容易复用和调试的方式是保存 tsvector 列：
CREATE TABLE searchable_articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL,
search_vector tsvector GENERATED ALWAYS AS (
to_tsvector(
'simple',
coalesce(payload->>'title', '') || ' ' ||
coalesce(payload->>'body', '')
)
) STORED
);
CREATE INDEX searchable_articles_search_idx
ON searchable_articles
USING gin (search_vector);
插入数据：
INSERT INTO searchable_articles (payload)
VALUES (
'{
"title": "PostgreSQL JSONB index",
"body": "GIN can accelerate structural JSONB queries and full text search"
}'::jsonb
);
执行：
SELECT id, payload->>'title'
FROM searchable_articles
WHERE search_vector
@@ websearch_to_tsquery('simple', 'JSONB GIN');
search_vector 是由源数据计算出的存储列。应用不能直接写入它；修改 payload 后，数据库会在写入行时重新计算。代价是每次相关 JSONB 字段变更都需要重新生成向量，并且更新索引。
3. 权重、排名与高亮
标题和正文的重要性通常不同，可以使用权重：
CREATE TABLE weighted_articles (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL,
search_vector tsvector GENERATED ALWAYS AS (
setweight(
to_tsvector('simple', coalesce(payload->>'title', '')),
'A'
)
||
setweight(
to_tsvector('simple', coalesce(payload->>'body', '')),
'D'
)
) STORED
);
其中：
A是较高权重；D是较低权重；||合并两个tsvector。
查询和排名：
WITH q AS (
SELECT websearch_to_tsquery('simple', 'GIN JSONB') AS query
)
SELECT
id,
ts_rank_cd(search_vector, q.query) AS rank,
payload->>'title' AS title
FROM weighted_articles, q
WHERE search_vector @@ q.query
ORDER BY rank DESC;
ts_rank_cd 是一种考虑词项覆盖密度的排名函数。排名不是“是否匹配”的一部分，而是匹配后的排序指标。GIN 索引主要帮助完成 @@ 过滤，不能直接等价为按相关性排序的索引。
如果需要高亮，可使用 ts_headline，但它会重新处理文本，应该在已经过滤出的较小结果集上调用，而不是对全表执行。
四、JSONB、全文检索和约束：灵活不等于无约束
JSONB 常用于承载可变属性，但关系表仍然应保存查询关键且稳定的字段。例如：
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id bigint NOT NULL,
created_at timestamptz NOT NULL,
order_status text NOT NULL,
attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
CHECK (jsonb_typeof(attributes) = 'object'),
CHECK (order_status IN ('pending', 'paid', 'cancelled'))
);
这里：
order_status是核心业务状态，适合使用普通列和CHECK；attributes是扩展属性，适合使用 JSONB；jsonb_typeof确保顶层结构是对象，而不是数组或标量。
对于反复使用的约束，可以使用域（domain）。域是带有约束的基础类型，而不是新的存储结构：
CREATE DOMAIN nonempty_text AS text
CHECK (length(btrim(VALUE)) > 0);
然后：
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name nonempty_text NOT NULL
);
域适合复用“非空文本”“合法代码”等标量约束；它不能替代 JSON Schema，也不会自动递归验证 JSONB 的任意内部结构。JSONB 内部约束必须通过 CHECK、触发器、应用验证或专门扩展实现，并明确验证发生在哪个边界。
五、分区表：先决定数据路由，再谈 JSONB 索引
1. 分区的组件
声明式分区至少包含：
- 分区父表：定义逻辑表结构和分区策略；
- 分区键：决定新行进入哪个分区；
- 分区：实际保存数据的子表；
- 分区约束：说明每个分区允许的键值范围或集合；
- 分区裁剪：规划器根据查询条件排除不可能命中的分区；
- 分区路由：插入或更新时把行送入目标分区。
下面按时间范围创建文档表：
CREATE TABLE partitioned_articles (
id bigint NOT NULL,
tenant_id bigint NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb NOT NULL,
search_vector tsvector GENERATED ALWAYS AS (
setweight(
to_tsvector('simple', coalesce(payload->>'title', '')),
'A'
)
||
setweight(
to_tsvector('simple', coalesce(payload->>'body', '')),
'D'
)
) STORED,
PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);
创建分区：
CREATE TABLE partitioned_articles_2025_01
PARTITION OF partitioned_articles
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE partitioned_articles_2025_02
PARTITION OF partitioned_articles
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
范围分区使用半开区间：
[2025-01-01, 2025-02-01)
因此，2025-02-01 00:00:00 不属于一月分区，而属于二月分区。
2. 分区裁剪与全文检索的关系
查询包含分区键条件时，规划器可以裁剪分区：
EXPLAIN
SELECT id
FROM partitioned_articles
WHERE created_at >= '2025-02-01'
AND created_at < '2025-03-01'
AND search_vector @@ websearch_to_tsquery('simple', 'postgresql');
理想情况下，执行计划只访问二月分区。全文检索条件负责筛选分区内部的行，时间条件负责减少需要访问的分区数量。
如果只写：
WHERE search_vector @@ websearch_to_tsquery('simple', 'postgresql')
数据库无法从全文检索词项推导出时间范围，通常需要检查所有相关分区。此时每个分区上的 GIN 索引仍可能有效，但分区数量越多，规划和执行管理成本越高。
JSONB 条件也是类似的：
SELECT id
FROM partitioned_articles
WHERE created_at >= '2025-02-01'
AND created_at < '2025-03-01'
AND payload @> '{"status": "published"}'::jsonb;
时间条件负责分区裁剪，JSONB GIN 索引负责分区内部的结构查找。二者不是同一个层次的优化。
3. 分区索引的实际边界
在分区父表上创建索引：
CREATE INDEX partitioned_articles_search_idx
ON partitioned_articles
USING gin (search_vector);
这会创建分区级索引结构，使查询可以在各个分区内使用对应索引。新建分区时也应确认索引已建立，已有分区则要检查索引是否完整。
分区表的唯一约束和主键有一个重要限制：要保证跨分区全局唯一，约束通常必须包含分区键。上例使用：
PRIMARY KEY (created_at, id)
而不是单独的：
PRIMARY KEY (id)
原因是不同分区分别维护索引，数据库不能仅依靠各分区本地索引证明 id 在所有分区中全局唯一。如果业务要求 id 全局唯一，可以：
- 把 ID 设计为全局生成的值，并在应用或其他机制中保证；
- 把分区键纳入唯一约束；
- 使用集中式唯一性设计，而不是误以为分区主键自动提供跨分区验证。
4. 分区路由、更新和故障路径
插入时，PostgreSQL 根据分区键选择目标分区：
INSERT INTO partitioned_articles (id, tenant_id, created_at, payload)
VALUES (
1,
10,
'2025-02-10 12:00:00+00',
'{"title": "Partitioned JSONB", "body": "routing"}'
);
这行进入二月分区。如果没有匹配分区，插入会失败：
ERROR: no partition of relation "partitioned_articles" found for row
可以增加默认分区承接未匹配数据，但默认分区会掩盖分区规划遗漏，且后续增加新分区时可能需要先清理或验证默认分区中的冲突数据。
更新分区键可能导致行从一个分区移动到另一个分区。这个过程不是简单地修改当前物理表中的一列，可能表现为从旧分区删除、向新分区插入；如果目标分区不存在、权限不足或存在约束冲突，整个语句会失败。生产中修改时间分区键应特别注意锁、触发器、外键和索引开销。
六、FDW：远程关系访问，不是分布式数据库透明层
1. FDW 的组成
FDW 是 PostgreSQL 的外部数据访问框架。一次典型配置包含：
- 扩展，例如
postgres_fdw； - 外部服务器（foreign server），描述远程连接；
- 用户映射（user mapping），描述本地用户如何认证远程服务；
- 外部表（foreign table），描述远程对象的本地关系定义；
- FDW 执行器，把扫描、过滤或修改转换为远程操作。
在本地数据库创建：
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER remote_pg
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host 'remote-db.example',
port '5432',
dbname 'content'
);
为本地用户配置远程认证：
CREATE USER MAPPING FOR CURRENT_USER
SERVER remote_pg
OPTIONS (
user 'content_reader',
password 'replace-with-secret'
);
导入远程 schema 中的表：
CREATE SCHEMA remote_content;
IMPORT FOREIGN SCHEMA public
LIMIT TO (articles)
FROM SERVER remote_pg
INTO remote_content;
之后可以查询：
SELECT id, payload->>'title'
FROM remote_content.articles
WHERE payload @> '{"status": "published"}'::jsonb;
前提是远程表确实有 payload JSONB 列，并且远程数据库支持相同的 JSONB 运算语义。
2. 查询数据流和下推
本地查询 FDW 外部表时，规划器会尝试把一部分操作下推到远端。例如：
SELECT id
FROM remote_content.articles
WHERE tenant_id = 10
AND payload @> '{"status": "published"}'::jsonb;
可能的数据流是：
本地 SQL
-> 本地规划器判断可下推条件
-> 远程 PostgreSQL 执行 WHERE
-> 仅返回满足条件的行
-> 本地继续执行未下推部分
但“写了 WHERE”不等于“WHERE 一定在远端执行”。应使用：
EXPLAIN (VERBOSE, COSTS)
SELECT id
FROM remote_content.articles
WHERE tenant_id = 10
AND payload @> '{"status": "published"}'::jsonb;
检查计划中的外部扫描条件和远程 SQL 信息。
postgres_fdw 对常见过滤、列裁剪、部分连接、聚合和排序具有下推能力，但是否下推取决于：
- 操作是否能在远端安全地执行；
- 两端类型、函数、排序规则和语义是否兼容；
- 连接条件是否满足下推要求；
- 本地和远端的成本估算；
- 查询结构是否阻止了规划器重写。
尤其要注意全文检索：
WHERE search_vector @@ websearch_to_tsquery('simple', 'postgresql')
若外部表拥有远程 tsvector 列和相容配置，可能在远程执行；若本地先从 JSONB 提取字段、调用本地函数或混合了本地表，全文检索可能部分或全部留在本地。不要根据 SQL 文本推断网络传输量，必须查看执行计划，并结合远程日志验证。
3. FDW 事务和故障边界
一次本地事务访问远程 PostgreSQL 时，postgres_fdw 会在远端建立相应事务上下文。远程扫描、远程写入和提交并不等于本地表的普通本地存储访问。
需要区分：
- 本地事务回滚通常会要求远程事务回滚；
- 网络中断、远程数据库崩溃或本地进程异常会造成连接和事务状态不确定；
- 多个远程服务器之间不自动构成完整的分布式事务；
- 远程提交与本地其他资源的提交不能简单视为一个全局原子操作。
因此，FDW 适合访问和联邦查询，但不能默认提供跨多个数据库的完整两阶段提交、全局锁或全局唯一约束。跨库写入需要显式设计幂等、补偿、重试和一致性验证。
一个常见错误是把外部表当作“本地缓存”：
INSERT INTO remote_content.articles ...
这可能产生远程写入和网络等待；若语句执行到一半连接断开，本地客户端看到的错误不一定能直接说明远程是否已经接受了请求。对非幂等操作，应使用业务唯一键和结果确认机制，不能无条件重试。
七、扩展边界：CREATE EXTENSION 改变了什么，没有改变什么
1. 扩展可以注册数据库对象
PostgreSQL 扩展可以通过控制文件、SQL 脚本和可选的服务器端代码注册或提供：
- 数据类型；
- 函数和操作符；
- 操作符类；
- 索引访问方法；
- 触发器；
- FDW；
- 统计信息或规划器相关能力；
- 需要预加载的后台或共享库能力。
使用扩展：
CREATE EXTENSION IF NOT EXISTS pg_trgm;
这通常会把扩展定义的对象安装到当前数据库，而不是自动安装到同一实例上的所有数据库。另一个数据库仍可能需要单独执行 CREATE EXTENSION。
扩展安装还受以下边界影响：
- 服务器上必须已安装扩展文件；
- 当前用户必须有相应权限；
- 某些扩展需要修改
shared_preload_libraries并重启； - 扩展的版本升级由扩展自身的升级脚本支持；
- 删除扩展可能删除其管理的对象，生产操作前必须确认依赖关系。
因此，“服务器安装了扩展”和“当前数据库已经可以使用扩展”是两个不同条件。
2. 内置能力与扩展能力
JSONB 和 PostgreSQL 全文检索的核心类型、操作符和 GIN 支持属于 PostgreSQL 核心能力，不要求额外安装扩展。
但以下能力通常来自扩展：
pg_trgm：三元组索引、相似度和部分模式匹配；unaccent：去除重音符号的文本搜索字典或函数；postgres_fdw：访问其他 PostgreSQL 数据库；pgvector：向量类型、向量距离运算和对应索引。
扩展不会把 PostgreSQL 变成另一个执行引擎。比如安装 pg_trgm 不会让 JSONB 自动支持全文分词；安装 pgvector 也不会让全文检索自动理解向量相似度。它们通过类型、函数、操作符和索引扩展能力，最终仍受 PostgreSQL 的 MVCC、事务、锁、规划器和资源管理约束。
3. 与 pgvector 的边界
如果文档同时保存全文检索向量和语义向量，可以使用类似结构：
CREATE TABLE article_embeddings (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL,
search_vector tsvector GENERATED ALWAYS AS (
to_tsvector(
'simple',
coalesce(payload->>'title', '') || ' ' ||
coalesce(payload->>'body', '')
)
) STORED
-- embedding vector(...) 由 pgvector 扩展提供
);
全文检索回答的是：
文档是否包含经过词法分析后匹配的词项？
向量检索回答的是：
文档向量在指定距离函数下是否接近查询向量？
混合查询可以先用结构条件和全文检索缩小候选集，再计算向量距离，也可以反过来。具体执行计划取决于过滤选择性、向量索引类型、排序要求和扩展版本。不能因为两者都使用索引，就假设数据库会自动得到全局最优的混合检索计划。
八、一个组合示例：分区 JSONB 文档表与全文检索
下面把前面的机制组合起来：
CREATE TABLE knowledge_docs (
tenant_id bigint NOT NULL,
doc_id bigint NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb NOT NULL,
search_vector tsvector GENERATED ALWAYS AS (
setweight(
to_tsvector('simple', coalesce(payload->>'title', '')),
'A'
)
||
setweight(
to_tsvector('simple', coalesce(payload->>'content', '')),
'D'
)
) STORED,
PRIMARY KEY (created_at, tenant_id, doc_id),
CHECK (jsonb_typeof(payload) = 'object')
) PARTITION BY RANGE (created_at);
创建分区和索引：
CREATE TABLE knowledge_docs_2025_01
PARTITION OF knowledge_docs
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE knowledge_docs_2025_02
PARTITION OF knowledge_docs
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE INDEX knowledge_docs_search_idx
ON knowledge_docs
USING gin (search_vector);
CREATE INDEX knowledge_docs_payload_idx
ON knowledge_docs
USING gin (payload jsonb_path_ops);
写入：
INSERT INTO knowledge_docs
(tenant_id, doc_id, created_at, payload)
VALUES
(
7,
1001,
'2025-02-12 09:30:00+00',
'{
"title": "PostgreSQL JSONB",
"content": "GIN supports structural queries and text search pipelines",
"status": "published",
"tags": ["postgresql", "database"]
}'::jsonb
);
组合查询：
SELECT
tenant_id,
doc_id,
payload->>'title' AS title,
ts_rank_cd(
search_vector,
websearch_to_tsquery('simple', 'GIN JSONB')
) AS rank
FROM knowledge_docs
WHERE created_at >= '2025-02-01'
AND created_at < '2025-03-01'
AND payload @> '{"status": "published"}'::jsonb
AND search_vector @@ websearch_to_tsquery('simple', 'GIN JSONB')
ORDER BY rank DESC;
这个查询按以下顺序理解：
created_at给规划器提供分区裁剪条件；payload @>进行 JSONB 结构过滤；search_vector @@ ...进行全文匹配；ts_rank_cd对已经匹配的结果进行相关性排序。
实际执行时，优化器不保证严格按上述文字顺序执行。上述顺序是语义分解，不是执行计划承诺。最终应通过：
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
...
确认是否发生了分区裁剪、哪些索引被使用，以及是否存在大量回表或排序开销。
九、常见失败表现与诊断路径
1. 搜索结果为空，但 JSONB 中明明有文本
可能原因包括：
search_vector只提取了title，查询词在content；- 索引使用
english，查询使用simple； - JSONB 字段实际是数字、数组或对象，不是文本；
- 文本配置的停用词或词干化规则改变了词项；
- 更新 JSONB 的路径没有真正改变预期字段。
检查生成结果：
SELECT
payload->>'title',
payload->>'content',
search_vector
FROM searchable_articles
LIMIT 10;
再单独检查查询解析结果：
SELECT websearch_to_tsquery('simple', 'GIN JSONB');
这一步可以确认问题是在源文本、配置还是查询构造。
2. 有 GIN 索引但仍然扫描很多数据
GIN 只回答它支持的操作符问题，不会保证每个查询都使用索引。常见原因：
- 查询使用了不匹配的表达式；
- 使用了不适合该操作符类的操作；
- 条件选择性太低，顺序扫描成本更低；
- 分区键条件缺失，必须访问大量分区；
- 统计信息过旧；
- JSONB 文档很大，索引和回表成本较高。
应先看：
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
不要仅凭 CREATE INDEX 是否成功判断查询已经被优化。
3. JSONB 更新成本异常高
JSONB 是一行中的一个值。更新其中一个路径，数据库仍需要写入新的行版本；如果值较大，还可能涉及 TOAST 存储和相关索引更新。对拥有大型 JSONB、GIN 索引和全文向量的表，频繁更新任意字段可能同时带来：
- 新旧行版本；
- JSONB 存储写入；
- GIN 索引维护；
tsvector重新计算；- WAL 增长；
- 后台清理压力。
如果某些字段频繁变化、某些字段用于高频查询，应考虑将它们提升为独立列，而不是把所有内容永久封装在同一个 JSONB 值中。
4. 分区表插入失败
错误：
no partition of relation found for row
优先检查：
SELECT min(created_at), max(created_at)
FROM incoming_source;
然后核对分区边界是否覆盖这些值，尤其注意：
- 范围分区的上界不包含；
- 时区转换可能使应用时间落入不同日期；
- 是否需要默认分区；
- 新月份分区是否已提前创建。
5. FDW 查询很慢
诊断应分成两段：
- 本地是否把过滤、列裁剪或聚合下推；
- 远程数据库是否使用了预期的 JSONB 或全文索引。
本地查看：
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT ...
FROM remote_content.articles
WHERE ...;
远程则应在远程数据库中对等价 SQL 执行 EXPLAIN。如果大量远程行被传回本地后才过滤，瓶颈通常在下推失败、类型不兼容、查询包装方式或连接条件，而不是 JSONB 本身。
十、如何划分职责
可以用下面的边界判断设计：
- 稳定且需要约束、排序、连接的字段：普通列、
CHECK、域和普通索引； - 结构变化频繁但仍需结构查询的属性：JSONB；
- 键和值的存在、包含和 JSONPath 条件：JSONB 操作符与合适的 GIN 或表达式索引；
- 自然语言词项匹配：
tsvector、tsquery和全文检索 GIN； - 任意子串、模糊拼写和相似文本：
pg_trgm； - 按时间、租户或哈希隔离数据访问范围：声明式分区；
- 访问另一个数据库或外部系统：FDW；
- 语义相似度检索：向量扩展及其距离索引。
最重要的不是把这些功能全部安装上，而是让每个条件落在与其语义匹配的层次：
分区键 -> 决定访问哪些物理分区
结构条件 -> 决定 JSONB 文档是否包含某种结构
全文条件 -> 决定词项是否匹配
向量条件 -> 决定距离是否足够接近
FDW -> 决定数据从哪个系统读取以及哪些操作远程执行
当这些边界清晰时，JSONB 可以提供灵活的数据模型，全文检索可以提供可解释的词项匹配，分区可以缩小物理访问范围，FDW 可以连接外部关系，而扩展则在 PostgreSQL 已有事务和规划框架内增加新的类型、操作符和访问能力。反之，如果把 JSONB 当全文索引、把分区当全局唯一性机制、把 FDW 当分布式事务系统，问题通常会在数据量、并发或故障条件下才暴露出来。
系列导航与关联阅读
- 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
- 上一篇：PostgreSQL 复制与高可用：流复制、复制槽、PITR 和故障切换
- 下一篇：Oracle 数据库架构：Instance、SGA、PGA、数据文件与后台进程
- 延伸：PostgreSQL 数据类型与 Schema：数组、范围、JSONB、约束和域
- 延伸：pgvector 实战：PostgreSQL 向量类型、HNSW、IVFFlat 与混合查询
官方资料
本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。
评论
0 条讨论