JSONB 对半结构化数据的支持

您可以使用 JSON 和 JSONB 提供半结构化数据。这对于包含用户自定义字段的数据最有用，例如由单个用户定义且因用户而异的字段名。我们建议以半结构化的方式使用它，例如：

CREATE TABLE metrics (
  time TIMESTAMPTZ,
  user_id INT,
  device_id INT,
  data JSONB
);

复制

当您使用 JSON 定义模式时，请确保将常用字段（例如 time、user_id 和 device_id）从 JSONB 结构中提取出来并存储为列。这是因为在表列上访问字段比在 JSONB 结构内部更高效。存储也更高效。

您还应该使用 JSONB 数据类型（即以二进制格式存储的 JSON），而不是 JSON 数据类型。JSONB 数据类型在存储开销和查找性能方面都更高效。

注意

对于用户定义的数据而不是稀疏数据，请使用 JSONB。这适用于大多数数据集。对于稀疏数据，请使用可为空 (NULLable) 字段，如果可能，请在 ZFS 等压缩文件系统上运行。这比 JSONB 数据类型更有效，除非数据极其稀疏，例如某行超过 95% 的字段为空。

当您对 JSONB 数据进行所有字段的索引时，通常最好使用 GIN（通用倒排）索引。在大多数情况下，您可以使用默认的 GIN 运算符，如下所示：

CREATE INDEX idxgin ON metrics USING GIN (data);

复制

有关 GIN 索引的更多信息，请参阅 PostgreSQL 文档。

此索引仅优化 WHERE 子句使用 ?、?&、?| 或 @> 运算符的查询。有关这些运算符的更多信息，请参阅 PostgreSQL 文档。

JSONB 列有时包含的常用字段的值适合单独索引。此类索引可用于对字段值进行排序操作、多列索引，以及对专门类型（如 PostGIS 地理类型）的索引非常有用。对单个字段值进行索引的另一个优点是它们通常比整个 JSONB 字段上的 GIN 索引小。要创建这样的索引，通常最好在访问该字段的表达式上使用部分索引。例如：

CREATE INDEX idxcpu
  ON metrics(((data->>'cpu')::double precision))
  WHERE data ? 'cpu';

复制

在此示例中，被索引的表达式是 data JSONB 对象中转换成双精度浮点数的 cpu 字段。这种转换通过存储小得多的双精度浮点数而非字符串来减小索引大小。WHERE 子句确保索引中只包含那些包含 cpu 字段的行，因为 data ? 'cpu' 返回 true。这也有助于通过不包含没有 cpu 字段的行来减小索引大小。请注意，为了让查询使用该索引，其 WHERE 子句中必须包含 data ? 'cpu'。

此表达式也可以与多列索引一起使用，例如，通过将 time DESC 作为主列添加。但请注意，为了启用仅索引扫描，您需要 data 作为列，而不是完整的表达式 ((data->>'cpu')::double precision)。