goovo

Postgres 索引膨胀排查手记：从 40GB 到 6GB

一张 2000 万行的表，索引占了 40GB。记录一次完整的膨胀排查过程：怎么确认、怎么安全重建、怎么防止再次发生。

3 分钟Postgres数据库运维

告警是磁盘使用率 87% 触发的。表本身不大 —— 2000 万行，数据约 12GB。但它的索引加起来 40GB。

索引比数据大三倍多，基本可以确定是膨胀。

先确认，别急着动手

Postgres 的 MVCC 决定了索引膨胀是必然的：更新一行会产生新的索引项，旧项要等 VACUUM 确认没有事务能看见才会被回收。如果 autovacuum 因为长事务或者频率不够而被拖延，索引就会持续长胖。

先看整体情况：

select
  relname,
  pg_size_pretty(pg_relation_size(oid))       as table_size,
  pg_size_pretty(pg_indexes_size(oid))        as index_size,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 1) as dead_pct,
  last_autovacuum
from pg_stat_user_tables
where relname = 'orders'
order by pg_indexes_size(oid) desc;

结果：

 relname | table_size | index_size | n_live_tup | n_dead_tup | dead_pct | last_autovacuum
---------+------------+------------+------------+------------+----------+-----------------
 orders  | 12 GB      | 40 GB      |   20134882 |   18420993 |     91.5 | 6 days ago

死元组比例 91.5%，而 autovacuum 六天没跑过。到这里原因已经很清楚了 ——有长事务一直持有 snapshot，autovacuum 永远清理不掉。

排查长事务：

select pid, state, now() - xact_start as duration, query
from pg_stat_activity
where xact_start is not null
order by duration desc
limit 5;

找到了：一个 BI 报表连接开着事务跑了四天（客户端忘记 commit）。

单索引级别的确认

pg_stat_user_tables 只能告诉你表级别的死元组数。要精确知道哪个索引胖了，用 pgstattuple：

create extension if not exists pgstattuple;

select * from pgstattuple('orders_customer_id_idx');
 table_len | tuple_count | tuple_len | dead_tuple_count | dead_tuple_len | free_space
-----------+-------------+-----------+------------------+----------------+------------
 8589934592 |    20134882 | 4832371680 |        18420993 |    4421038320 | 1200000000

dead_tuple_len 占了 51%，这个索引的实际有效数据只有 4.8GB。

重建：必须用 CONCURRENTLY

最直接的处理方式是重建索引。但普通的 REINDEX 会持有排他锁，在线上环境等于整表不可写。

从 Postgres 12 开始，REINDEX INDEX CONCURRENTLY 可以在不阻塞写入的情况下重建：

reindex index concurrently orders_customer_id_idx;

注意几个约束：

  • 不能在事务块里执行
  • 需要额外的磁盘空间（同时存在新旧两份索引）
  • 失败会留下一个 INVALID 状态的索引，需要手动 drop 后重试

批量处理时我写了个循环，一次一个：

do $$
declare
  idx record;
begin
  for idx in
    select indexrelid::regclass::text as name
    from pg_index
    where indrelid = 'orders'::regclass
      and pg_relation_size(indexrelid) > 1024 * 1024 * 1024
  loop
    execute format('reindex index concurrently %s', idx.name);
    raise notice 'rebuilt %', idx.name;
  end loop;
end $$;

重建后索引从 40GB 降到 6GB，磁盘使用率回到 61%。

防止复发

重建只是止血。根因是长事务，所以三件事必须做：

1. 给长事务告警。 我们定的阈值是 30 分钟：

select pid, now() - xact_start as duration, left(query, 80)
from pg_stat_activity
where state <> 'idle'
  and now() - xact_start > interval '30 minutes';

2. 给报表库单独的连接。 BI 查询不该跑在业务主库上，这个建议我说了很多年，每次有人觉得「就查一下没事」。

3. 调 autovacuum 的触发阈值。 默认的 0.2 对大表太迟钝了：

alter table orders set (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.01
);

对大表，按比例触发意味着要有 400 万死元组才开始清理 —— 太晚了。改成固定阈值 + 小比例，让清理更早、更平缓地发生。

复盘

这次事故的教训不是「索引会膨胀」这个常识，而是： 监控指标选错了。 我们一直在看磁盘使用率，而不是死元组比例。前者是症状，后者才是可以直接告警的原因。

现在 dead_pct > 20% 会直接触发 P2。

← 全部文章