AI 写的 PostgreSQL 索引都能用，为什么还是会拖慢写入

2026-09-11 22 预计阅读时间: 1 分钟
来源: postgr.es AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：13 分钟

现在的编码代理已经很会写 PostgreSQL：GIN、GiST、部分索引、表达式索引、INCLUDE，以及以租户 ID 开头的复合索引，通常都能写对。真正的问题不再是索引无效，而是每个索引单独看都合理，叠到同一张高频更新表上却形成了昂贵的写放大。

一组针对 30 份模型生成 schema 的实验加载并检查了 838 个索引。只有 10 个找不到明确需求，其余大多具备扎实的 SQL 水平。然而，在工单、课程场次、货运任务等核心表上，模型经常一次放置十几个索引。它们加速了读取，也同时增加了 WAL、更新延迟、VACUUM 工作量和缓存压力。

问题不是错索引，而是缺少全表视角

编码代理通常按功能逐条处理需求：

  • 工单列表按状态排序，需要一个索引；
  • 按负责人筛选，再来一个；
  • 按团队筛选，再来一个；
  • SLA 到期扫描，再来一个；
  • 搜索主题，补上 GIN 或 trigram 索引。

于是同一张 tickets 表可能出现多组结构相似的索引：

CREATE INDEX tickets_open_queue_idx
ON tickets (workspace_id, last_activity_at)
WHERE status IN ('open', 'pending');

CREATE INDEX tickets_open_by_assignee_idx
ON tickets (workspace_id, assignee_id, last_activity_at)
WHERE status IN ('open', 'pending');

CREATE INDEX tickets_open_by_team_idx
ON tickets (workspace_id, team_id, last_activity_at)
WHERE team_id IS NOT NULL
  AND status IN ('open', 'pending');

这些索引各自都有对应查询，也都可能通过代码评审。风险来自它们共同覆盖了 status 和 last_activity_at：回复、转派、关闭或重新打开工单时，这两个字段都会变化。

实验中的一个生成方案在 tickets 上拥有 15 个二级索引。与 7 个索引的人工基线相比，同样执行 20 万次更新时，它产生约 1.8 倍 WAL，更新耗时接近 1.9 倍，索引占用约为 1.6 倍，VACUUM 时间也明显上升。具体耗时会受硬件与缓存影响，但倍数揭示了累积成本。

与此同时，不能简单地把索引数量当作质量分数。另一个拥有 9 个索引的方案反而比 7 索引基线写入更快，因为其中几个部分索引的谓词非常严格，绝大多数更新行根本不会进入这些索引。真正应检查的是索引覆盖了哪些行、占用了多少页，以及更新时会弄脏多少页面。

一个可变列，可能让整张表失去 HOT

PostgreSQL 的 HOT（Heap-Only Tuple）更新可以把新版本留在原有堆页面中，避免修改二级索引。它是高频更新表非常重要的优化。

如果被更新的列参与了索引键，或者影响部分索引的谓词，HOT 往往就无法使用。之后的成本不只是更新那一个索引：PostgreSQL 需要生成新的行版本和索引指针，写入更多 WAL，并留下等待 VACUUM 回收的旧版本。

实验用同样的 6 个二级索引更新 30 万行，只改变其中一个索引是否包含 last_seen_at：

  • 未索引该列：HOT 比例为 46.2%，耗时 2743 ms；
  • 索引该列：HOT 比例降为 0%，耗时 3979 ms。

索引总数完全相同，仅仅改变一个索引的位置，更新时间就增加了约 45%。因此，类似下面这个看似自然的队列索引值得额外审查：

CREATE INDEX tickets_queue_idx
ON tickets (workspace_id, status, last_activity_at DESC);

它可能把队列查询从毫秒级进一步压低，却也意味着每次触碰工单活动时间都无法执行 HOT 更新。是否值得，要结合读写比例、WAL 预算、复制链路和缓存容量判断。

租户前缀并不适合所有查询

多租户系统经常把 workspace_id 放在复合索引首列。这对于单租户列表查询通常正确，却可能伤害跨租户后台任务。

例如，下面的部分索引很适合查询某个工作区中即将超时的工单：

CREATE INDEX tickets_first_response_due_idx
ON tickets (workspace_id, first_response_due_at)
WHERE first_response_at IS NULL
  AND first_response_due_at IS NOT NULL
  AND status NOT IN ('solved', 'closed');

但 SLA 扫描往往横跨所有工作区。实验中，这类扫描在生成 schema 上慢了 111 倍：以 workspace_id 开头的索引无法高效支持全局时间范围扫描，有时优化器使用它反而比顺序扫描更慢。人工基线只以 first_response_due_at 建索引，同一任务读取的缓冲块少得多。

这说明索引评审不能只问“它能否支持某条查询”，还要确认：

  1. 查询是单租户还是跨租户；
  2. 等值条件与范围条件的顺序是否匹配；
  3. 部分索引谓词是否与真实 SQL 完全一致；
  4. 优化器是否真的选择了它；
  5. 选择后读取的页面是否比顺序扫描更少。

在生产库里检查写路径

下面是一组可以直接改造的审计命令。执行前将数据库名、schema 和表名替换为自己的值。统计视图适合辅助判断，不应直接驱动自动删索引。

先找出更新量较大且 HOT 比例较低的表：

SELECT
    s.schemaname,
    s.relname,
    s.n_tup_upd,
    s.n_tup_hot_upd,
    round(
        100.0 * s.n_tup_hot_upd / NULLIF(s.n_tup_upd, 0),
        1
    ) AS hot_pct,
    (
        SELECT count(*)
        FROM pg_index i
        WHERE i.indrelid = s.relid
    ) AS index_count
FROM pg_stat_user_tables AS s
WHERE s.n_tup_upd > 10000
ORDER BY hot_pct ASC, s.n_tup_upd DESC;

如果某张热点表的 hot_pct 接近 0，继续查看完整索引定义，重点寻找频繁修改的列和可变谓词：

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'app'
  AND tablename = 'tickets'
ORDER BY indexname;

再查看没有扫描记录的大索引：

SELECT
    s.schemaname,
    s.relname,
    s.indexrelname,
    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
    i.indisunique AS is_unique,
    s.idx_scan,
    s.last_idx_scan
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i
  ON i.indexrelid = s.indexrelid
WHERE NOT i.indisprimary
  AND s.idx_scan = 0
ORDER BY pg_relation_size(s.indexrelid) DESC;

零扫描不等于可以删除。统计可能刚刚重置，索引可能只被副本上的查询使用，也可能是唯一索引或约束的一部分。某些全文索引在测试中没有被使用，也可能只是因为前置租户过滤已经把候选集缩小到几百行。

对于准备新增或删除的索引，可以在预发布环境用真实形状的数据测量 WAL 和缓冲区。下面的 EXPLAIN ANALYZE 会真实执行更新，但事务最终回滚；不要直接在繁忙生产库运行：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE app.tickets
SET last_activity_at = clock_timestamp()
WHERE id IN (
    SELECT id
    FROM app.tickets
    ORDER BY id
    LIMIT 1000
);

ROLLBACK;

对比索引变更前后的执行时间、WAL records、WAL bytes、shared hit 和 read blocks。若需要模拟生产结论，还应保持相同的数据量、数据分布、fillfactor、缓存条件和更新组合。

缓存与复制会放大账单

额外 WAL 不会停留在主库。它还要发送到每个副本，并进入归档和备份链路。实验估算每次更新多写 1.75 KB 看似不大，但在每秒 100 次更新时，一张表每天就可能多产生约 15 GB WAL。

索引页面也会争夺 shared_buffers。在 512 MB 缓存中，当工作集能够全部放下时，额外索引的缓存影响不明显；把缓存限制为 128 MB 后，15 索引方案的物理读取量达到 7 索引基线的 6.7 倍。索引页面挤走了堆页面，更新耗时差距也进一步扩大。

这不是说 128 MB 测试能预测所有服务器，而是提醒我们：在内存充足的开发环境中表现正常的 schema，上线后可能因为工作集装不下而出现截然不同的行为。

让代理优化系统，而不是堆满索引

提示词本身也会改变数据库成本。实验中，仅增加 make it production-ready，索引密度就提高了约 17% 到 20%。模型往往把“生产可用”解释成“为每个可能的过滤条件建立索引”，却不会自动获得真实读写比、WAL 上限或缓存预算。

可以把请求改得更具约束性：

为这个 PostgreSQL schema 设计索引，但不要按查询逐条新增。

要求：
- 标出写入量最高、更新最频繁的表和列；
- 优先复用已有复合索引；
- 解释每个索引服务的查询、预期选择性和写入成本；
- 检查索引键与部分索引谓词是否会破坏 HOT 更新；
- 区分单租户请求和跨租户后台任务；
- 对可选索引给出 EXPLAIN、pg_stat_user_indexes 和 WAL 验证方案；
- 不要仅因为“生产可用”而添加推测性索引。

更重要的是，在后续功能请求中明确要求代理重新审视旧索引，而不是只为新过滤条件追加第十七个索引。

上线前的索引评审清单

索引不是越少越好。追加型事件表几乎不更新，多几个索引的代价可能很低；如果一个索引能把 40 秒报表压到可接受范围，通常值得添加。真正需要警惕的是高频更新的业务核心表。

合并 schema 变更前，可以逐项确认：

  • 这是追加型表，还是会频繁更新的操作型表？
  • 哪些列在回复、状态流转、心跳或批处理时持续变化？
  • 新索引是否包含这些列，或以它们作为部分索引谓词？
  • 是否存在可由现有复合索引覆盖的查询？
  • 查询是单租户还是跨租户，首列顺序是否正确？
  • HOT 比例、WAL bytes、索引尺寸和 VACUUM 时间如何变化？
  • 低使用率索引是否受统计重置、副本查询或完整性约束影响？
  • 如果未来增加批量更新功能，当前读写平衡是否仍成立？

AI 生成的索引已经足够专业，不能再用“明显错误”筛掉它们。新的评审重点是系统效应：第十二个索引可能完全正确，但它是否值得让最忙的表在之后每一次写入中持续付费？

# AI # Docker # Go # LLM # PostgreSQL # 云原生 # 全栈 # 后端 # 数据库

相关推荐