PostgreSQL JSONB 索引实践

用户4035096

发布于 2026-07-09 18:56:19

PostgreSQL JSONB 索引实践

JSONB是PostgreSQL很早就支持的文档数据类型, 在后来又增加了jsonpath类型来增强JSONB的搜索语义, 可谓非常灵活, 但是JSONB的值通常硕大无比(相比其他标量类型), 如何加速JSONB的搜索呢?

索引.

由于JSONB的结构相比单值标量类型、多值单类型数组都更加复杂. 为了平衡性能和支持的搜索算子, PG内部甚至提供了2个index ops可供选择: jsonb_ops 和 jsonb_path_ops, 支持的操作符号详见PG文档.

即使创建了GIN索引, JSONB的搜索也仅支持等值、包含查询的加速.

如果要JSONB内某个field的范围检索, 则还需要对这个field使用表达式索引.

在文末给出了一系列倒排索引、表达式索引相关的原理和实践, 有兴趣可继续阅读.

接下来将翻译一篇来自crunchydata的文章, PostgreSQL JSONB 索引实践. 原文: https://www.crunchydata.com/blog/indexing-jsonb-in-postgres

在 Postgres 中索引 JSONB

Postgres 的出色之处有很多。其中一个领域却没有得到足够的重视，那就是数据类型。Postgres 拥有丰富的数据类型，其中一种让开发者特别兴奋的数据类型是 JSONB。

JSONB 是结构化且可索引的。在 JSON 中，B 代表二进制（或者我们更喜欢用 B 来表示“更好”），这意味着数据在存储时会被预先解析。

从检索的角度来看，如何充分利用 JSONB？Postgres 丰富的索引支持就是一个例证。

Postgres 索引类型

大多数数据库都只有一个标准的索引类型：B 树。B 树是一种平衡树结构，也是你从计算机科学学位角度学习到的常见索引类型。当你制定标准时，CREATE INDEX会创建一个 B 树索引。这适用于以该值为目标的标准WHERE子句。

但 Postgres 还有其他索引类型，包括：

GIN —— 广义倒排索引
GiST - 生成的搜索树
Sp-GiST - 空间分区广义搜索树
BRIN —— 区块范围索引
bloom
hash
hnsw
ivfflat 等等

Postgres 数据库常用的 B 树并不适合 JSON 文档，或者至少你可能认为并非如此，因为它具有嵌套结构的特性。

那么，如何索引 JSONB 以便更高效地查询呢？GIN 索引应运而生。

JSONB 的 GIN 索引

GIN 索引不是将整个 JSONB 文档作为一个单元进行索引，而是将其分解，并对其中的键和值进行索引。可以将其想象成在底层创建了一个巨大的查找表。假设有一行如下：

{  
  "status": "active",  
  "plan": "pro"  
}

此行上的 GIN 索引将存储如下条目：

status => active  
  
plan => pro

这使得 GIN 成为回答以下问题的理想选择：

SELECT *  
FROM my_table  
WHERE data @> '{"status": "active"}';

该@>操作符（JSONB 包含）是 GIN 可索引的，并且 GIN 可以快速找到存在该键值对的文档。

创建索引很简单：

CREATE INDEX idx_data_gin  
ON my_table  
USING gin (data);

如果您只需要索引键的子集，那么您还可以使用partial index(建索引时用where子句框定仅对符合条件的tuple构建索引)或表达式索引来更具体地说明：

CREATE INDEX idx_status_gin  
ON my_table  
USING gin ((data->'status'));

哪些查询使用 GIN 索引？

这里的关键是：并非所有 JSONB 查询都能从 GIN 索引中受益。可以使用 GIN 的查询包括：

包含：data @> '{"plan": "pro"}'
key是否存在：data ? 'status'
任意key匹配：data ?| array['plan', 'tier']
所有key匹配：data ?& array['plan', 'status']

这些是基于运算符的查询，可以很好地映射到GIN倒排索引结构。

哪些 JSONB 查询不能使用 GIN？

有时你可能会措手不及。你添加了一个 GIN 索引，在使用SQL时，结果却遇到了两种情况：索引增加了维护成本(insert/update/delete/vacuum)，但查询却因为无法使用索引而变得很慢。

GIN 索引对下面的情况没有任何帮助：

基于路径的查找：data->'user'->>'email' = 'craig@example.com'
JSONB 内的比较(范围查询)：(data->>'age')::int > 30
正则表达式或模式匹配内部值：data->>'name' ILIKE 'craig%'

维护 GIN 和 JSONB

虽然 GIN 索引功能强大，但它们的写入开销比标准 B 树索引更大。如果您频繁更新大型 JSONB 列，这种开销会变得尤为明显(因为一个多值类型(如json, array等)的列值在表中对应一个值, 但是在索引中却对应多个值.)。频繁的大型更新可能会导致索引膨胀，索引会包含过多对dead tuple的引用，从而变得效率低下。

由于存在膨胀的可能性，主动监控 GIN 索引的健康状况至关重要。您可以通过定期运行REINDEX CONCURRENTLY命令来重建索引并回收浪费的空间，从而管理索引的健康状况。我们还建议您使用pgstattuple扩展程序等内部工具来检查索引的状态，并在膨胀成为严重问题之前发现它。

JSONB 的表达式索引

在某些情况下，创建典型的 B 树表达式索引可以帮助处理不符合 GIN 用例的 JSON查找(如范围查询、排序)。创建表达式索引不仅需要定义数组的一部分，还需要定义对该列执行操作的结果的索引。例如：

CREATE INDEX idx_orders_total ON orders (((details->>'order_total')::numeric))

这将在 orders 表上创建一个btree索引。它的工作原理是，首先访问详细信息 JSONB 列，使用 ->> 运算符提取与 order_total 键关联的值作为文本，然后将该文本值转换为数字类型。然后，Postgres 在这些结果数字值上构建一个标准 B 树索引。这对于在这些 JSONB 行中进行范围扫描和排序非常有效。

请记住，使用表达式索引的前提是查询的 WHERE 子句必须与定义索引的表达式完全匹配。对于上面创建的索引，类似的查询WHERE (details->>'order_total')::numeric > 100会使用索引，但稍有不同的查询（例如WHERE (details->>'order_total')::float > 100）则不会。

严格匹配意味着表达式索引非常适合优化嵌入在应用程序代码中的定义明确的静态查询，但不适用于经常变化的查询。