面向复合值的 GIN 索引

概念概述

面向复合值的 GIN 索引（GIN Indexes for Composite Values）指的是：GIN 索引把每行的多个键映射到包含它们的行，是数组、JSON 文档与全文检索的正确结构，更新成本高于 B-tree。

从更严格的定义看，A GIN index, or inverted index, stores an entry for each element inside a composite value such as an array or document, so a query for a single element can find all rows that contain it. 该概念在工程实践中的价值，在于它能帮助团队用统一的语言描述一个具体的质量属性或设计约束，从而在评审与协作中减少歧义。

为什么需要它

It is the standard index for containment and existence queries over arrays, JSONB columns, and text search vectors. 它与 Trigram Indexes for Fuzzy Matching、Full-Text Search Vectors、Advisory Locks 等概念经常一起出现，用于解决相近的工程问题。理解它的适用场景，有助于在多种备选方案之间做出有依据的取舍，而不是凭直觉选择技术方案。

常见误解

• a GIN index is a drop-in replacement for a B-tree index
• GIN makes document writes as cheap as a plain column update

需要避免的反模式

• indexing a frequently updated JSONB column without measuring write cost
• assuming a GIN index speeds up ordering or range comparisons

实践要点

1. Because one row can map to many keys, GIN supports operators such as containment and overlap that a B-tree cannot, but it is slower to update since every element must be indexed.
2. Fast update and pending list settings trade write speed against query latency, and periodic vacuum merges pending entries so readers see a consistent index.
3. GIN answers element queries, not sorted scans; ordering still needs a B-tree or a separate sort, so choose the index per query shape rather than per table.

参考来源

https://www.postgresql.org/docs/current/gin.html

代码示例

-- PostgreSQL 时序表按写入时间自然有序（append-only）
CREATE TABLE metrics (
  observed_at timestamptz NOT NULL,
  sensor_id int NOT NULL,
  value double precision NOT NULL
) WITH (fillfactor = 100);

-- BRIN 索引：按块范围记录最小最大值
CREATE INDEX idx_metrics_observed_brin
  ON metrics USING brin (observed_at)
  WITH (pages_per_range = 32);

-- 范围扫描：块范围过滤掉绝大多数数据块
SELECT sensor_id, avg(value)
FROM metrics
WHERE observed_at >= '2026-10-21 00:00'
  AND observed_at < '2026-10-22 00:00'
GROUP BY sensor_id;

-- 尺寸对照：B 树版本用于体积感知
CREATE INDEX idx_metrics_observed_btree
  ON metrics (observed_at);

-- 验证计划选择
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM metrics
WHERE observed_at >= '2026-10-21'
  AND observed_at < '2026-10-22';

-- 老数据分区后 BRIN 更精准：每分区独立建
CREATE TABLE metrics_old PARTITION OF metrics
  FOR VALUES FROM ('2026-01-01') TO ('2026-10-01');

-- PostgreSQL 订单与其条目一次查询聚合成嵌套 JSON
SELECT
  jsonb_build_object(
    'order_id', o.id,
    'status', o.status,
    'total_cents', o.total_cents,
    'placed_at', to_char(o.placed_at, 'YYYY-MM-DD"T"HH24:MI:SS'),
    'items', COALESCE(
      jsonb_agg(
        jsonb_build_object(
          'sku', i.sku,
          'quantity', i.quantity,
          'unit_cents', i.unit_cents
        ) ORDER BY i.line_no
      ) FILTER (WHERE i.id IS NOT NULL),
      '[]'::jsonb
    )
  ) AS order_document
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.id IN ('ord-1', 'ord-2')
GROUP BY o.id, o.status, o.total_cents, o.placed_at
ORDER BY o.id;

-- 数组打包键值对：标签表转对象
SELECT
  t.order_id,
  jsonb_object_agg(t.tag_key, t.tag_value) AS tags_object
FROM order_tags t
GROUP BY t.order_id;