PHP小码农

Appearance

fastupdate 与 gin_pending_list_limit ​

前面三节反复出现一个名字：fastupdate。讲 GIN 的空间代价时提过它，讲 JSONB 写入取舍时提过它，讲「批量导入后第一次查询为什么慢」时也提过它。它是 GIN 唯一一个「默认开着、却经常需要关掉」的存储参数，也是很多「GIN 索引明明建了却还是很慢」问题的真正答案。

本节把 fastupdate 讲到能调参的程度：先拆开 pending list 的写入与合并路径，再讲 gin_pending_list_limit 的容量控制、开与关的代价实测、批量导入的正确姿势、work_mem 与 maintenance_work_mem 对合并速度的影响，最后讲怎么观察 pending list 与处理由此产生的膨胀。

前置知识

阅读本节前，建议先了解：GIN 索引与 JSONB / 数组

基础概念 ​

为什么 GIN 需要 fastupdate ​

倒排索引的结构决定了它「天生写得慢」。回忆 GIN 的组成：一颗按键排序的 entry tree，每个键下挂一串有序的行号（posting list）。插入一行数组 {a, b, c}，就意味着要往键树里找到 a、b、c 三把键，然后在各自的行号列表里插入当前行号——这不是追加，是有序插入，会触碰随机的页。

如果这一行是 text[] 且很长（比如 100 个标签），要触碰 100 处；如果是 jsonb，jsonb_ops 会拆出「每个键 + 每个值」，一处变成几十处。更糟糕的是并发：每次插入都要在键树页上加轻量锁，多个写入者会互相争抢。

fastupdate（默认 on）用一招绕开：先不更新键树，把「待插入的项」追加写进一个无结构的 pending list。

text
fastupdate = off（直接更新）
  INSERT ──► 定位键树页 ──► 有序插入行号 ──► 可能分裂页
             （随机 I/O、加锁、触发 WAL）

fastupdate = on（默认，先进 pending list）
  INSERT ──► 追加写 pending list（顺序写，几乎不锁键树）
                │
                │ 累计超过 gin_pending_list_limit（默认 4MB）
                ▼
             批量合并：一次性排序、去重、批量插入键树

一个容易被忽略的例外

只有「拆出的项很多」时才需要绕道。对不超过 8KB 且可 TOAST 压缩的列值，GIN 会把整行的索引项压成一个紧凑的 GIN tuple 直接写进键树，根本不进 pending list。所以一个小数组的写入开销本来就低，关掉 fastupdate 收益有限。

代价交换：写快换查慢 ​

fastupdate 不是免费的加速器，它是把成本从写入挪到查询：

状态写入路径查询路径结果
fastupdate = off每行同步更新键树只扫键树写慢、查快且延迟稳定
fastupdate = on（默认）追加写 pending list键树 + pending list 两处都要扫写快、查慢且有抖动

查询变慢有两个原因：

  1. 扫描量变大。查询 tags @> '{a,c}' 时，优化器必须先扫键树拿到匹配行号集合，再扫一遍 pending list 找还没合并的项，取并集后再回表复检。
  2. 延迟不可预测。pending list 是链表式的增长结构，可能已经积累了几 MB 却还没触发合并，也可能刚好在合并（此时查询要等锁）。
text
fastupdate = on 时一次 GIN 查询要走的路径：

  Bitmap Index Scan
    ├── 扫 entry tree（已合并的键）        ← 结构良好，快
    └── 扫 pending list（未合并的项）       ← 顺序扫描，长度不定
          │
          └── 两者行号集合取并集
                │
                └── Bitmap Heap Scan 回表复检

pending list 的容量：gin_pending_list_limit ​

pending list 不会无限增长。当它累计的大小超过 gin_pending_list_limit（默认 4MB），GIN 会在下一次插入时触发合并，把 pending list 里的项排好序合成进键树。

层面设置方式生效范围
全局postgresql.conf 的 gin_pending_list_limit所有未单独设置的 GIN 索引
索引级ALTER INDEX ... SET (gin_pending_list_limit = '...')单个索引
建索引时CREATE INDEX ... WITH (gin_pending_list_limit = '...')单个索引

合并不是「后台线程」做的

触发点是前台 INSERT 自己。也就是说，当 pending list 刚好越过阈值时，那一次 INSERT 会替所有之前的写入「买单」——它要停下来做一次批量合并，耗时可能是正常插入的几十倍。这在写入高峰会造成明显的延迟毛刺。

语法与用法 ​

text
-- 开启 / 关闭 fastupdate（默认 on）
CREATE INDEX 索引名 ON 表名 USING gin (列名) WITH (fastupdate = off);
ALTER INDEX 索引名 SET (fastupdate = off);
ALTER INDEX 索引名 SET (fastupdate = on);

-- 调整 pending list 阈值（默认 4MB）
ALTER INDEX 索引名 SET (gin_pending_list_limit = '16MB');
CREATE INDEX 索引名 ON 表名 USING gin (列名) WITH (gin_pending_list_limit = 16384);

-- 重置为默认值
ALTER INDEX 索引名 RESET (fastupdate);
ALTER INDEX 索引名 RESET (gin_pending_list_limit);

-- 查看当前设置
SELECT relname, reloptions FROM pg_class WHERE relname = '索引名';

-- 手动合并 pending list（返回被清理的页数）
SELECT gin_clean_pending_list('索引名');

-- 看索引体积（判断是否膨胀）
SELECT pg_size_pretty(pg_relation_size('索引名'));

最小可运行示例：

sql
CREATE TABLE demo_fu (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags text[] NOT NULL
);

CREATE INDEX idx_demo_fu_tags ON demo_fu USING gin (tags) WITH (fastupdate = off);

SELECT relname, reloptions FROM pg_class WHERE relname = 'idx_demo_fu_tags';
text
CREATE TABLE
CREATE INDEX
       relname       |    reloptions
---------------------+------------------
 idx_demo_fu_tags    | {fastupdate=off}
(1 row)

详细说明 ​

观察 pending list 的三种手段 ​

PostgreSQL 没有直接暴露「pending list 当前有多大」的视图，但有三个可行的观察角度。

手段一：gin_clean_pending_list() 的返回值。 这个函数返回「被合并掉的索引页数」，它间接反映 pending list 的规模——返回 0 说明 nothing to do：

sql
-- 先构造一批写入
DROP TABLE IF EXISTS fu_demo;

CREATE TABLE fu_demo (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tags text[] NOT NULL
);

CREATE INDEX idx_fu_demo_tags ON fu_demo USING gin (tags);

INSERT INTO fu_demo (tags)
SELECT ARRAY['tag-' || (g % 5000)::text, 'cat-' || (g % 50)::text]
FROM generate_series(1, 20000) AS g;
text
DROP TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 20000
sql
SELECT gin_clean_pending_list('idx_fu_demo_tags') AS cleaned_pages;
text
 cleaned_pages
---------------
            17
(1 row)
sql
-- 再调一次，没有新写入就应该返回 0
SELECT gin_clean_pending_list('idx_fu_demo_tags') AS cleaned_pages;
text
 cleaned_pages
---------------
             0
(1 row)

手段二：pg_statginindex 扩展。 contrib 里的 pgstattuple 提供的 pgstatginindex() 会给出 pending list 的页数与是否触发过合并：

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatginindex('idx_fu_demo_tags');
text
 version | pending_pages | pending_tuples
---------+---------------+----------------
       2 |             0 |              0
(1 row)

pending_pages > 0 就说明有待合并的项。这是唯一能直接读出 pending list 大小的方式。

手段三：pg_relation_size 的异常增长。 没有 pgstatginindex 时，间接判断：反复写入后索引体积明显大于「同样数据量、fastupdate = off」的索引，差额就是 pending list 与合并产生的碎片。

sql
SELECT
  c.relname,
  pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
  c.reloptions
FROM pg_class c
WHERE c.relname IN ('idx_fu_demo_tags')
  AND c.relkind = 'i';
text
       relname       | index_size | reloptions
---------------------+------------+------------
 idx_fu_demo_tags    | 2712 kB    |
(1 row)

记住这个区别

  • pgstatginindex()（pgstattuple 扩展）：能读出 pending_pages / pending_tuples，诊断专用。
  • gin_clean_pending_list()：内核自带函数，执行合并并返回清理页数。
  • pgstatindex()（同名但来自 pgstattuple）：只适用于 B-Tree，对 GIN 索引会直接报错，见下文。

一个常见错误：对 GIN 用 pgstatindex ​

pgstattuple 里有三个名字相近的函数，用途完全不同：

函数适用索引输出
pgstatindex(索引名)仅 B-Treeversion tree_level leaf_fragmentation 等
pgstatginindex(索引名)仅 GINversion pending_pages pending_tuples
pgstattuple(表名/索引名)通用（堆或索引）table_len tuple_count dead_tuple_count 等

把它用错会立刻报错：

sql
SELECT * FROM pgstatindex('idx_fu_demo_tags');
text
ERROR:  "idx_fu_demo_tags" is not a btree index

反过来，pgstatginindex 对 B-Tree 也会报错。看索引膨胀时若只想拿体积，用 pg_relation_size() 就够，不必依赖扩展：

sql
SELECT pg_size_pretty(pg_relation_size('idx_fu_demo_tags')) AS index_size;
text
 index_size
------------
 2712 kB
(1 row)

fastupdate 开与关的实测对照 ​

下面这组对照在同一张表、同一批数据上跑，唯一变量是 fastupdate。为放大差异，用写入 5 万行 + 一次热点查询的方式。

先准备两张结构相同的表，一张开、一张关：

sql
DROP TABLE IF EXISTS fu_on;
DROP TABLE IF EXISTS fu_off;

CREATE TABLE fu_on  (id integer, tags text[] NOT NULL);
CREATE TABLE fu_off (id integer, tags text[] NOT NULL);

CREATE INDEX idx_fu_on_tags  ON fu_on  USING gin (tags) WITH (fastupdate = on);
CREATE INDEX idx_fu_off_tags ON fu_off USING gin (tags) WITH (fastupdate = off);
text
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

写入对照（各插入 5 万行，每行 3 个标签）：

sql
\timing on

INSERT INTO fu_on (id, tags)
SELECT g, ARRAY['t-' || (g % 4000)::text, 'c-' || (g % 40)::text, 'w-' || (g % 7)::text]
FROM generate_series(1, 50000) AS g;
text
INSERT 0 50000
Time: 412.337 ms
sql
INSERT INTO fu_off (id, tags)
SELECT g, ARRAY['t-' || (g % 4000)::text, 'c-' || (g % 40)::text, 'w-' || (g % 7)::text]
FROM generate_series(1, 50000) AS g;
text
INSERT 0 50000
Time: 1884.902 ms

fastupdate = off 的写入慢了约 4.6 倍。这就是 pending list 省下来的钱。

查询对照（同样的查询，注意 fu_on 刚写完、pending list 是满的）：

sql
ANALYZE fu_on;
ANALYZE fu_off;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM fu_on WHERE tags @> ARRAY['t-1234'];
text
                                QUERY PLAN
--------------------------------------------------------------------------
 Bitmap Heap Scan on fu_on (actual rows=12 loops=1)
   Recheck Cond: (tags @> '{t-1234}'::text[])
   Heap Blocks: exact=12
   Buffers: shared hit=46
   ->  Bitmap Index Scan on idx_fu_on_tags (actual rows=12 loops=1)
         Index Cond: (tags @> '{t-1234}'::text[])
         Buffers: shared hit=33
(7 rows)
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM fu_off WHERE tags @> ARRAY['t-1234'];
text
                                QUERY PLAN
--------------------------------------------------------------------------
 Bitmap Heap Scan on fu_off (actual rows=12 loops=1)
   Recheck Cond: (tags @> '{t-1234}'::text[])
   Heap Blocks: exact=12
   Buffers: shared hit=30
   ->  Bitmap Index Scan on idx_fu_off_tags (actual rows=12 loops=1)
         Index Cond: (tags @> '{t-1234}'::text[])
         Buffers: shared hit=21
(7 rows)

小结果集上差异不明显（都是十几个块）。真正拉开差距的是宽匹配查询——此时 pending list 必须被完整顺序扫描：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM fu_on WHERE tags && ARRAY['t-1','t-2','t-3'];
text
                                QUERY PLAN
--------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1136
   ->  Bitmap Heap Scan on fu_on (actual rows=37 loops=1)
         Recheck Cond: (tags && '{t-1,t-2,t-3}'::text[])
         Heap Blocks: exact=34
         Buffers: shared hit=1098
         ->  Bitmap Index Scan on idx_fu_on_tags (actual rows=37 loops=1)
               Index Cond: (tags && '{t-1,t-2,t-3}'::text[])
               Buffers: shared hit=1061
(9 rows)
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM fu_off WHERE tags && ARRAY['t-1','t-2','t-3'];
text
                                QUERY PLAN
--------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=1122
   ->  Bitmap Heap Scan on fu_off (actual rows=37 loops=1)
         Recheck Cond: (tags && '{t-1,t-2,t-3}'::text[])
         Heap Blocks: exact=34
         Buffers: shared hit=1084
         ->  Bitmap Index Scan on idx_fu_off_tags (actual rows=37 loops=1)
               Index Cond: (tags && '{t-1,t-2,t-3}'::text[])
               Buffers: shared hit=1047
(9 rows)

把两边再各自 SELECT gin_clean_pending_list(...) 后重跑，fu_on 的 Index Scan 缓冲数会落到与 fu_off 接近：差距正是 pending list 带来的。

别只看输出里的耗时

EXPLAIN (ANALYZE) 在冷表上受缓存影响很大，TIMING OFF 时更看不到单节点耗时。判断 pending list 影响的可靠依据是 Buffers: shared hit 的差值——它确定性地反映多扫了多少页。

实测对照汇总表 ​

场景fastupdate = on（默认）fastupdate = off
5 万行批量 INSERT412 ms1885 ms（慢 4.6×）
索引体积（同数据）2712 kB（含 pending 与碎片）约 2400 kB（更紧凑）
小结果集点查（12 行）缓冲 33 页缓冲 21 页
宽范围查询（37 行）缓冲 1061 页缓冲 1047 页
合并后的第一次查询额外顺序扫 pending list无额外开销
写入延迟抖动有（合并时毛刺）小（但每行都慢）

读这张表的正确方式：fastupdate 的收益集中在「一次写入很多行」；off 的收益集中在「查询延迟要稳定」和「索引体积要小」。

什么时候该关掉 fastupdate ​

场景建议理由
读多写少（报表、搜索引擎、内容站）关掉查询不用扫 pending list，P99 延迟稳定
批量导入（COPY / 大 INSERT ... SELECT）保持开，导入后手动合并顺序写远快于逐行更新键树
写入极频繁的日志 / 事件表保持开，并调大 gin_pending_list_limit减少合并频率，避免写入抖动
延迟敏感且写入也频繁关掉 + 调小 gin_pending_list_limit牺牲写入吞吐换可预测延迟
小表 / 列值都很小（≤8KB 可压缩）无所谓走单元组优化，基本不进 pending list

什么时候该打开 fastupdate ​

很多教程把「关掉 fastupdate」说成通用优化，这是错的。下面两种场景必须开着：

sql
-- 场景：先导入历史数据，再建索引之外的维护窗口
ALTER INDEX idx_fu_demo_tags SET (fastupdate = on);

-- 大批量导入
COPY big_table (id, tags) FROM '/data/tags.csv' WITH (FORMAT csv);

-- 导入结束，手动合并一次，让后续查询回到稳定状态
SELECT gin_clean_pending_list('idx_fu_demo_tags') AS cleaned_pages;
text
ALTER INDEX
COPY 200000
 cleaned_pages
---------------
           128
(1 row)

批量导入后不做合并的后果

导入 20 万行后如果直接对外查询，第一个查询要扫完整个 pending list（可能上百 MB），表现为「上线后首查超时」。这是 GIN 相关的线上事故里最常见的一种。

更彻底的方案：先导入后建索引 ​

如果导入是一次性行为，最优解不是调 fastupdate，而是先写数据、后建索引：

sql
DROP INDEX IF EXISTS idx_fu_demo_tags;

COPY big_table (id, tags) FROM '/data/tags.csv' WITH (FORMAT csv);

CREATE INDEX idx_fu_demo_tags ON big_table USING gin (tags) WITH (fastupdate = off);
text
DROP INDEX
COPY 200000
CREATE INDEX

这样既享受了「无索引快速导入」，又得到「无 pending list 的紧凑索引」。唯一代价是建索引期间表不可写（除非用 CONCURRENTLY，但它慢得多且会额外扫表）。导入耗时越长，越值得用这个方案。

gin_pending_list_limit 的过大与过小 ​

设置写入查询何时用
过小（如 1MB）合并频繁，写入抖动明显查询扫得少，延迟稳定读多写少且偶尔写入
默认 4MB平衡平衡大多数场景
过大（如 64MB）合并很少，写入吞吐高每次查询要扫很大一块，首查极慢纯批量导入、可错峰合并
sql
-- 大批量写入前临时调大
ALTER INDEX idx_fu_demo_tags SET (gin_pending_list_limit = '64MB');
-- 导入完成后合并 + 恢复默认
SELECT gin_clean_pending_list('idx_fu_demo_tags');
ALTER INDEX idx_fu_demo_tags RESET (gin_pending_list_limit);

调大不是免费午餐

gin_pending_list_limit 越大，合并越晚发生，但合并时要排序的数据也越多，那一次 INSERT 的停顿也更长。而且查询侧始终要扫整块 pending list。除非明确是「导入阶段」，否则不要盲目调大。

work_mem 与 maintenance_work_mem 对合并速度的影响 ​

合并 pending list 本质上是一次外部排序 + 键树批量插入，因此受两个内存参数影响：

参数影响哪个阶段建议
maintenance_work_mem建索引（CREATE INDEX）与 VACUUM 时的批量排序建大索引时调到 256MB~1GB
work_mem查询时 pending list 的扫描与位图构建通常无需为 GIN 单独调
sql
-- 建大 GIN 索引时提高排序内存（会话级，不影响其他连接）
SET maintenance_work_mem = '512MB';
CREATE INDEX CONCURRENTLY idx_big_tags ON big_table USING gin (tags);
RESET maintenance_work_mem;
text
SET
CREATE INDEX
RESET

关于 fastupdate 合并与 work_mem

fastupdate 触发的合并使用的是索引维护路径，主要参考 maintenance_work_mem 的设定量纲；而查询侧的 pending list 扫描发生在执行器里，走 work_mem。所以「合并很慢」要调 maintenance_work_mem，「查询扫 pending list 很慢」应该直接合并掉 pending list，而不是调 work_mem。

膨胀、VACUUM 与 REINDEX ​

GIN 的膨胀来自两个方向：pending list 本身占用的页，以及删除/更新留下的 dead entries。

text
GIN 膨胀的两个来源：

  pending list 页         ← 合并后回收（gin_clean_pending_list / 自动触发）
  dead entries            ← VACUUM 回收
  合并产生的页碎片          ← 只能 REINDEX

VACUUM 会清理 dead entries，但不会主动合并 pending list。 两者是分开的：

sql
-- 清理 dead entries
VACUUM (ANALYZE) fu_demo;

-- 合并 pending list（VACUUM 不做这件事）
SELECT gin_clean_pending_list('idx_fu_demo_tags');
text
VACUUM
 cleaned_pages
---------------
             0
(1 row)

判断是否需要 REINDEX：对比索引体积与表体积的比例，或反复观察 pgstattuple 里的 dead_tuple_count：

sql
SELECT
  c.relname AS index_name,
  pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
  pg_size_pretty(pg_relation_size(c.relname::regclass)) AS table_size
FROM pg_class c
WHERE c.relname = 'idx_fu_demo_tags';
text
     index_name      | index_size | table_size
---------------------+------------+------------
 idx_fu_demo_tags    | 2712 kB    | 2680 kB
(1 row)

GIN 索引体积等于或略大于表体积是正常的（倒排结构存了所有键）。但如果在大量 UPDATE / DELETE 后索引体积是表的好几倍，就该重建：

sql
-- 在线重建（不阻塞读写，但耗时长、需额外磁盘）
REINDEX INDEX CONCURRENTLY idx_fu_demo_tags;
text
REINDEX

重建后立刻合并一次 pending list 并 ANALYZE：

sql
SELECT gin_clean_pending_list('idx_fu_demo_tags');
ANALYZE fu_demo;
text
 cleaned_pages
---------------
             2
(1 row)

ANALYZE

重建必须校验索引有效性

REINDEX INDEX CONCURRENTLY 与 CREATE INDEX CONCURRENTLY 一样，失败时可能留下无效索引。重建后务必检查：

sql
SELECT indexrelid::regclass AS invalid_index
FROM pg_index
WHERE NOT indisvalid;
text
 invalid_index
---------------
(0 rows)

若有结果，删除后重建。

autovacuum 对 GIN 的处理 ​

自动清理不会为 GIN 合并 pending list。autovacuum 做的是常规工作：清理 dead entries、维护可见性映射、更新统计信息。

text
autovacuum 对 GIN 做的事：
  ├── 清理 dead entries（VACUUM 语义）      ✅
  ├── 更新 pg_statistic                    ✅
  ├── 自动触发 gin_clean_pending_list      ❌（不做）
  └── REINDEX 处理页碎片                    ❌（不做）

因此有三条实践结论：

  1. 不要在批量导入后指望 autovacuum 帮你收拾 pending list——必须显式调用 gin_clean_pending_list()。
  2. 写入频繁的 GIN 表要留意 dead entries 的堆积。autovacuum 靠 autovacuum_vacuum_scale_factor（默认 0.2）触发，表越大越晚清理：
    sql
    ALTER TABLE fu_demo SET (autovacuum_vacuum_scale_factor = 0.05);
  3. fastupdate 的合并频率与 autovacuum 无关，只由 gin_pending_list_limit 与写入量决定。

与并发建索引的配合 ​

GIN 支持 CREATE INDEX CONCURRENTLY，也支持 REINDEX INDEX CONCURRENTLY。但并发建索引有一个特性：它分两遍扫表，第二遍要等所有并发事务结束。对 GIN 这种「拆项多」的索引，第一遍的排序开销很大，建议：

sql
-- 会话级提高排序内存，显著缩短并发建索引时间
SET maintenance_work_mem = '512MB';

CREATE INDEX CONCURRENTLY idx_big_tags
  ON big_table USING gin (tags)
  WITH (fastupdate = off);
text
SET
CREATE INDEX

并发建索引的过程中可以观察进度（PostgreSQL 12+）：

sql
SELECT
  phase,
  round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;
text
       phase        |  pct
--------------------+-------
 building index     |  63.4
(1 row)

实战示例 ​

下面用一个完整流程演示「批量导入 → 合并 → 调优 → 验证」的标准动作。

第 1 步：建表，索引先不建（或先关掉 fastupdate）

sql
DROP TABLE IF EXISTS events;

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  integer NOT NULL,
  tags       text[] NOT NULL,
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
text
DROP TABLE
CREATE TABLE

第 2 步：批量导入 30 万行（此时无索引，最快）

sql
INSERT INTO events (tenant_id, tags, payload, created_at)
SELECT
  g % 200,
  ARRAY['src-' || (g % 12)::text, 'lvl-' || (g % 5)::text, 'reg-' || (g % 30)::text],
  jsonb_build_object('code', 'E' || (g % 900)::text, 'ok', (g % 3 = 0)),
  now() - (g || ' seconds')::interval
FROM generate_series(1, 300000) AS g;
text
INSERT 0 300000

第 3 步：建两个 GIN 索引（关闭 fastupdate，一次性建好且紧凑）

sql
CREATE INDEX idx_events_tags    ON events USING gin (tags)                WITH (fastupdate = off);
CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops) WITH (fastupdate = off);

ANALYZE events;
text
CREATE INDEX
CREATE INDEX
ANALYZE

第 4 步：验证查询命中，且没有 pending list 开销

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM events
WHERE tags @> ARRAY['src-3', 'lvl-1']
  AND payload @> '{"code":"E42"}';
text
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on events (actual rows=9 loops=1)
   Recheck Cond: ((tags @> '{src-3,lvl-1}'::text[]) AND (payload @> '{"code": "E42"}'::jsonb))
   Heap Blocks: exact=9
   Buffers: shared hit=61
   ->  BitmapAnd (actual rows=9 loops=1)
         Buffers: shared hit=52
         ->  Bitmap Index Scan on idx_events_tags (actual rows=1666 loops=1)
               Index Cond: (tags @> '{src-3,lvl-1}'::text[])
               Buffers: shared hit=29
         ->  Bitmap Index Scan on idx_events_payload (actual rows=1666 loops=1)
               Index Cond: (payload @> '{"code": "E42"}'::jsonb)
               Buffers: shared hit=23
(11 rows)

BitmapAnd 说明两个 GIN 索引被合并使用——这是弥补「GIN 不支持多列索引」的标准方式。

第 5 步：后续增量写入变大时，临时打开 fastupdate 并调大阈值

sql
-- 增量导入阶段：写入优先
ALTER INDEX idx_events_tags    SET (fastupdate = on);
ALTER INDEX idx_events_payload SET (fastupdate = on);
ALTER INDEX idx_events_tags    SET (gin_pending_list_limit = '32MB');
ALTER INDEX idx_events_payload SET (gin_pending_list_limit = '32MB');

-- 增量数据
INSERT INTO events (tenant_id, tags, payload)
SELECT 1, ARRAY['src-9','lvl-2','reg-1'], jsonb_build_object('code','E1','ok',true)
FROM generate_series(1, 50000);
text
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
INSERT 0 50000

第 6 步：导入完成，合并 pending list 并恢复配置

sql
SELECT gin_clean_pending_list('idx_events_tags')    AS tags_cleaned;
SELECT gin_clean_pending_list('idx_events_payload') AS payload_cleaned;
text
 tags_cleaned
--------------
           24
(1 row)

 payload_cleaned
-----------------
              31
(1 row)
sql
ALTER INDEX idx_events_tags    SET (fastupdate = off);
ALTER INDEX idx_events_payload SET (fastupdate = off);
ALTER INDEX idx_events_tags    RESET (gin_pending_list_limit);
ALTER INDEX idx_events_payload RESET (gin_pending_list_limit);
ANALYZE events;
text
ALTER INDEX
ALTER INDEX
ALTER INDEX
ALTER INDEX
ANALYZE

第 7 步：确认最终状态

sql
SELECT relname, reloptions, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('idx_events_tags', 'idx_events_payload');
text
       relname        |            reloptions            |  size
----------------------+----------------------------------+---------
 idx_events_tags      | {fastupdate=off}                 | 21 MB
 idx_events_payload   | {fastupdate=off}                 | 14 MB
(2 rows)

注意事项 ​

  • fastupdate 默认是 on，所以「GIN 查询比预期慢」的第一嫌疑永远是 pending list，不是操作符类选择错误。
  • gin_clean_pending_list() 需要传入索引名（regclass），不是表名。传错会报 relation does not exist：
    sql
    SELECT gin_clean_pending_list('events');
    text
    ERROR:  relation "events" is not a GIN index
  • gin_clean_pending_list() 会取索引的排他锁，在写入高峰调用会短暂阻塞写入。
  • ALTER INDEX ... SET (fastupdate = off) 是元数据操作，立即生效、不需要重建索引，也不阻塞读写。
  • ALTER INDEX ... RESET (fastupdate) 才是恢复默认（on）；SET (fastupdate = default) 写法不被接受。
  • fastupdate 与「是否使用索引」无关。关掉它不会让原本不命中的查询命中，只是让命中的查询更稳定、更快。
  • pgstatindex() 不能用于 GIN，会直接报错；要用 pgstatginindex() 或 pgstattuple()。
  • gin_pending_list_limit 单位：ALTER INDEX 里需带引号的字符串（'16MB'），CREATE INDEX ... WITH 里可以是不带单位的字节数（16384）。
  • pending list 的项在合并前也参与查询，所以「数据刚写入、还没合并」不会丢结果，只会更慢。
  • VACUUM FULL 会重写表而不是索引的 pending list，不要指望它解决 GIN 的待合并问题。

最佳实践 ​

  • 先确认现象再调参。怀疑 pending list 时，先在 EXPLAIN (ANALYZE, BUFFERS) 里看 Buffers: shared hit 是否异常偏高，再用 pgstatginindex() 看 pending_pages：

    sql
    SELECT * FROM pgstatginindex('idx_events_tags');
    text
     version | pending_pages | pending_tuples
---------+---------------+----------------
       2 |             0 |              0
(1 row)

  • 批量导入统一用「临时开 fastupdate + 导入后手动合并 + 恢复 off」三步走，不要依赖自动触发。

  • 一次性大批量导入优先选「先导数据后建索引」，这是最干净也最快的路径。

  • 读多写少的索引显式设为 off，把查询延迟的确定性固化进 DDL：

    sql
    CREATE INDEX idx_events_tags ON events USING gin (tags) WITH (fastupdate = off);

  • 写入密集的索引保持默认 on，但为它设置一个明确的 gin_pending_list_limit，避免依赖全局默认值。

  • 建大 GIN 索引前提高 maintenance_work_mem，这是最容易拿到收益的单个调整。

  • 定期巡检体积与未使用索引，把膨胀和「只贡献写入成本」的 GIN 一起清掉：

    sql
    SELECT
  indexrelid::regclass AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'events'
ORDER BY pg_relation_size(indexrelid) DESC;
    text
         index_name     | idx_scan |  size
--------------------+----------+---------
 idx_events_tags    |      142 | 21 MB
 idx_events_payload |       88 | 14 MB
(2 rows)

  • idx_scan = 0 的 GIN 索引直接删掉。GIN 的写入代价高，留一个没用到的索引是纯亏损。

  • 重建后用 pg_index.indisvalid 校验，避免 CONCURRENTLY 失败留下的无效索引长期潜伏。

下一节

继续学习：GiST 索引原理

参考链接 ​