PostgreSQL bloat 治理实战:1.2 亿行表从 380GB 压到 152GB

线上 PostgreSQL 一张订单表 1.2 亿行,磁盘占用 380GB,扫描慢到 10 秒以上,autovacuum 显示一直在跑但表却越来越大。复盘下来是经典的 MVCC 膨胀(bloat)+ 长事务阻塞 vacuum 的组合拳。本文实录定位 + 清理 + 防复发的全过程,附 pg_repack / VACUUM FULL / 分区化迁移三套方案对比。

现象

告警:orders 表查询 p99 = 10s(往常 200ms)
DBA 看了一眼,表 1.2 亿行,实际 active 数据 6000 万行(一半是死元组)
磁盘:orders 表 + 索引 = 380GB,健康值 ~150GB
autovacuum:一直在跑,但 dead_tup 不降反升

什么是 bloat(膨胀)

PostgreSQL 用 MVCC(多版本并发控制)。UPDATE 不是原地改,而是插入新行 + 标记旧行死亡。DELETE 也只是打标记。死元组要等 VACUUM 来回收空间。如果 VACUUM 跟不上写入速度,死元组就堆积成 bloat。

-- 查看表的死元组比例
SELECT
  schemaname, relname,
  n_live_tup AS live,
  n_dead_tup AS dead,
  ROUND(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_ratio,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS total_size,
  last_autovacuum, last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 100000
ORDER BY n_dead_tup DESC
LIMIT 10;

-- 结果:
--  schemaname | relname |  live    |  dead     | dead_ratio | total_size |     last_autovacuum
-- ------------+---------+----------+-----------+------------+------------+-------------------------
--  public     | orders  | 65000000 | 58000000  |   47.18    | 380 GB     | 2024-03-12 02:11:33
--  public     | order_items | 120M | 28M      |   18.92    |  92 GB     | 2024-03-12 02:30:18
-- 死元组占 47%! 一半磁盘是垃圾

定位:为什么 autovacuum 没用

-- 怀疑 1:autovacuum 配置太保守
SHOW autovacuum_vacuum_scale_factor;   -- 0.2(默认)
SHOW autovacuum_vacuum_threshold;       -- 50
SHOW autovacuum_naptime;                -- 60s
SHOW autovacuum_max_workers;            -- 3
SHOW autovacuum_vacuum_cost_limit;      -- 200
SHOW autovacuum_vacuum_cost_delay;      -- 2ms

-- scale_factor 0.2 意味着:dead_tup > 0.2 × live_tup 才触发
-- 1.2 亿行的表,2400 万死元组才触发,触发后 cost_limit=200 跑得超慢

-- 怀疑 2:长事务阻塞 vacuum
SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY xact_start;

-- 找到一个跑了 8 小时的事务(报表 SQL):
--   pid=24351, duration=08:23:11, state=active
--   query='SELECT ... FROM orders WHERE created_at > ...'

-- 长事务持有快照,vacuum 不能回收"理论上可能还被这个事务看到"的行
-- 即使 vacuum 在跑,也只能回收最早的死元组,新产生的死不掉

第一步:杀掉长事务

-- 找出运行超过 1 小时的事务
SELECT pid, usename, application_name,
       now() - xact_start AS duration,
       state, wait_event_type, wait_event,
       LEFT(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 hour'
ORDER BY xact_start;

-- 跟业务确认能不能杀,杀掉
SELECT pg_terminate_backend(24351);

-- 杀完后查 vacuum 进度
SELECT * FROM pg_stat_progress_vacuum;
-- 显示在跑,但表太大,估算还要 20 小时

第二步:VACUUM FULL 还是 pg_repack?

方案对比:

VACUUM FULL:
  - 重写表 + 索引,完全释放空间
  - 全程持有 AccessExclusiveLock,业务完全不可用
  - 1.2 亿行 380GB 估算 4-6 小时不可写
  - 简单可靠,适合维护窗口

pg_repack:
  - 在线重建表,业务持续可用
  - 只在切换瞬间需要短暂锁(秒级)
  - 需要 2x 磁盘空间(重建期间双份)
  - 需要表有主键或唯一索引
  - 1.2 亿行估算 6-10 小时,但不阻塞

VACUUM(默认):
  - 标记可复用,但不归还磁盘给 OS
  - 后续新数据写入复用空间,磁盘不立即降
  - 适合 dead 比例不高的常规清理

我们选 pg_repack,生产不能停服

pg_repack 实战

# 安装(PostgreSQL 14 + pg_repack 1.4.8)
$ sudo apt install postgresql-14-repack

# CREATE EXTENSION 一次(超级用户)
$ psql -d production -c 'CREATE EXTENSION pg_repack;'

# 预先看磁盘:380GB 表 + 60GB 索引 = 440GB
# 服务器剩 600GB,刚好够(2x 紧巴巴)

# 跑 repack(单表)
$ pg_repack -h 127.0.0.1 -d production \
    -t orders \
    --jobs 4 \
    --no-superuser-check \
    -e

# --jobs 4 : 并行重建索引
# 1.2 亿行实际跑了 8 小时 20 分,业务无感

# 中途监控(另一个会话)
$ psql -d production -c "
  SELECT pid, query_start, state, LEFT(query, 80)
  FROM pg_stat_activity
  WHERE query LIKE '%repack%'
  ORDER BY query_start;
"

pg_repack 工作原理

清理后效果

-- 清理前后对比
                      清理前        清理后        变化
=================================================
orders 表大小         380 GB        152 GB        -60%
索引大小              62 GB         24 GB         -61%
n_dead_tup            58000000      120000        -99%
查询 p99              10s           180ms         -98%
autovacuum 跑得动     N (永远在跑)  Y(2 小时一次)

防复发:调整 autovacuum

-- 大表单独配置(降低触发阈值,提高速度)
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,         -- 死元组 2% 就触发
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.05,
  autovacuum_vacuum_cost_limit = 2000,           -- 加快(默认 200)
  autovacuum_vacuum_cost_delay = 10              -- ms
);

-- 全局调(postgresql.conf)
autovacuum = on
autovacuum_max_workers = 6                       -- 默认 3,调到 6
autovacuum_naptime = 30s                         -- 默认 60s
autovacuum_vacuum_cost_limit = 2000              -- 默认 200,加大
autovacuum_vacuum_cost_delay = 10ms
maintenance_work_mem = 2GB                       -- 提高 vacuum 内存

-- 重启或 SELECT pg_reload_conf();

长事务监控告警

-- 监控:超过 30 分钟的事务报警
CREATE OR REPLACE VIEW v_long_transactions AS
SELECT
  pid, usename, application_name, client_addr,
  now() - xact_start AS duration_sec,
  state, wait_event_type, wait_event,
  LEFT(query, 200) AS query
FROM pg_stat_activity
WHERE xact_start < now() - interval '30 minutes'
  AND state <> 'idle';

-- 配 Prometheus + postgres_exporter
-- alert.yml:
- alert: PostgresLongTransaction
  expr: max(pg_stat_activity_xact_age) > 1800   # 30 分钟
  for: 1m
  labels:
    severity: warning
  annotations:
    summary: '长事务超过 30 分钟,会阻塞 autovacuum'

-- 应用层:连接池设置 statement_timeout 和 idle_in_transaction_session_timeout
-- application.yml:
spring:
  datasource:
    hikari:
      connection-init-sql: "SET statement_timeout = '30s'; SET idle_in_transaction_session_timeout = '60s';"

查 bloat 的几个有用查询

-- 1. 表的 bloat 估算(基于统计信息,大致准)
WITH constants AS (
  SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
),
no_stats AS (
  SELECT table_schema, table_name,
    n_live_tup::numeric AS est_rows,
    pg_table_size(quote_ident(table_schema)||'.'||quote_ident(table_name)) AS table_size
  FROM information_schema.columns
  JOIN pg_stat_user_tables psut
    ON table_schema = psut.schemaname AND table_name = psut.relname
  GROUP BY table_schema, table_name, n_live_tup, table_size
)
SELECT table_schema, table_name,
  pg_size_pretty(table_size) AS size,
  est_rows
FROM no_stats
ORDER BY table_size DESC
LIMIT 20;

-- 2. 看 pg_stat_progress_vacuum(PG12+)进度
SELECT
  p.pid, now() - a.xact_start AS duration,
  p.phase,
  pg_size_pretty(p.heap_blks_total * 8192) AS table_size,
  pg_size_pretty(p.heap_blks_scanned * 8192) AS scanned,
  ROUND(p.heap_blks_scanned::numeric / NULLIF(p.heap_blks_total, 0) * 100, 2) AS pct,
  p.heap_blks_vacuumed, p.index_vacuum_count
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid;

-- 3. 索引 bloat
SELECT
  schemaname, indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 100 * 1024 * 1024  -- 100MB+
ORDER BY pg_relation_size(indexrelid) DESC;

更长远的方案:分区化

-- 1.2 亿行 orders 表按月分区
-- 老数据单独放,新数据写入热分区,vacuum 压力小

CREATE TABLE orders_new (
  id BIGSERIAL,
  user_id BIGINT NOT NULL,
  amount DECIMAL(12, 2),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  status VARCHAR(20),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- 按月建分区
CREATE TABLE orders_2024_01 PARTITION OF orders_new
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders_new
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... 略

-- pg_partman 自动维护
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  p_parent_table := 'public.orders_new',
  p_control := 'created_at',
  p_type := 'range',
  p_interval := 'monthly',
  p_premake := 12  -- 提前建 12 个月分区
);

-- 迁移老数据(INSERT ... SELECT 分批)
INSERT INTO orders_new
SELECT * FROM orders WHERE created_at >= '2024-01-01';

-- 切换:DROP 老表,RENAME 新表
BEGIN;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

-- 历史分区单独配置(冷数据不再更新,vacuum 压力极小)
ALTER TABLE orders_2024_01 SET (autovacuum_enabled = false);

VACUUM 三种模式对比

命令                     锁级别            释放磁盘  在线   备注
================================================================
VACUUM                  ShareUpdateExclLock  否       是   日常自动清理
VACUUM FULL              AccessExclLock       是       否   完全重写,业务停摆
pg_repack                AccessExclLock(秒级) 是       是   推荐:在线 + 释放
CLUSTER                  AccessExclLock       是       否   按索引顺序重写

事故复盘清单

1. 大表 autovacuum 默认参数太保守,要单独 ALTER TABLE 调
2. 长事务是 vacuum 的天敌,必须有监控告警
3. 连接池层面强制 statement_timeout 和 idle_in_transaction_session_timeout
4. 报表 SQL 应该走只读副本,不要在主库上跑长查询
5. 每月定期检查 pg_stat_user_tables 的 dead_tup 占比
6. 新业务表设计就考虑分区(按时间或租户)
7. 历史冷数据分区可以禁用 autovacuum,减少全局压力
8. 磁盘要预留 2x 空间,pg_repack 需要
9. UPDATE 频繁的列考虑用 fillfactor < 100,留 HOT update 空间
10. HOT 更新条件:不改索引列,且页内有空间

fillfactor 优化 HOT 更新

-- 频繁 UPDATE 的表降低 fillfactor
ALTER TABLE orders SET (fillfactor = 85);

-- fillfactor=85 意味着每页只填 85%,留 15% 给 HOT update
-- HOT 更新不需要更新索引,大幅减少索引膨胀

-- 但要注意:fillfactor 低会让表占用更多空间
-- 适合 UPDATE 远多于 INSERT 的场景

-- 配合 pg_repack 应用 fillfactor
$ pg_repack -t orders --no-superuser-check -d production

总结

这次 PG bloat 治理,从发现到完全解决用了一周时间。核心收获:autovacuum 不是万能药,默认配置在大表上跑不动;长事务比想象中更普遍(报表 SQL、慢查询、应用 bug);pg_repack 是生产环境清理 bloat 的最佳选择;分区化是治本方案。监控上一定要盯着 n_dead_tup、long transaction、vacuum 进度三个指标。每个 PG DBA 都该熟悉这套流程,bloat 不处理只会越来越糟,最后逼到不得不停机 VACUUM FULL。

—— 别看了 · 2026