PostgreSQL自真空调优：自真空配置之技术深探

作者自注是篇记叙生产事故之查究，及返归根本文献而得之技术见闻。其修正由同僚施为；此文录得研习之途，于正当之文献检阅中。

事故：CPU高负荷因自动清理冲突

一生产级 Aurora PostgreSQL 集群，在 3-4 小时内持续 CPU 利用率高达 85-90%。CloudWatch Performance Insights 识别主要等待事件为 CPU（非 I/O 或锁竞争），消耗最大的操作为运行于两张大表上的自动清理 VACUUM 进程。

观测状态：

表 A（593 GB，623 万行）：有 1.24 亿条死亡元组（死亡比率为 16.6%）
表B（四百六十五万行）：七千四百万元组已死（死率百分之十三点七）
自真空工作者：二个并行运行
CPU利用率：百分之八十五至九十
自真空频率：四至六时辰一重大真空操作
表之状态：自实例创建以来未尝手动真空

根本之由： 自動清空閾值依系統默認設置，此對於每兩三小時由計劃數據載入過程進行大量更新的高流動性表不適當。

理解多版本並發控制：PostgreSQL 與 Oracle 的撤銷架構

調整有效之前，必先明瞭 PostgreSQL 與 Oracle 在管理並發訪問上的根本區別。

雅虎之法：自动回滚保留管理 (AUM)

雅虎之中，多版本并发控制 (MVCC) 乃借 回滚表空间段 以行：

更新之操作： 当行被更，旧版写入回滚表空间（非写入表本身）。
回滚保留： 之自動回滅保留管理 (AUM) 以環形緩衝區管理回滅表區。回滅範圍依 UNDO_RETENTION 之參數及可用表區自動循環。
空間回收： 撤销空间，或因保留期已至，或因表空间压力迫令旧撤销数据回滚，自能自动释出。DBA之责，唯在于事前预拨足量撤销表空间而已。

要旨： DBA一调此机，既定保留期与表空间之量，遂委诸Oracle之后台进程，自为撤销生命周期之管理。

[圖像二：Oracle與PostgreSQL多版本并发控制(MVCC)架構圖]

PostgreSQL之法：堆基多版本并发控制(MVCC)與顯式清空(VACUUM)

於PostgreSQL，MVCC實施於表(堆)之層級：

更新之動作： 更新一行时，新版本之行插入于同表。旧版标记为"死"，然仍存于表内。
空间回收： VACUUM须扫描表，辨析死元组，标其空间为可复用。死元组非自动消去。
自动清理触发： 自动清理乃背景之程，决其事。当依可调阈值而执行清理。异于Oracle之自动回滚回收，PostgreSQL需显式配置清理触发之时。

要义： DBA须据表更迭之态，主动调校VACUUM参数。无类OracleAUM之"设之即忘"之机制。

所系： 于 Oracle 之世，表之 UPDATE 量高者，则多生 undo，此 Oracle 之 AUM 所司也。于 PostgreSQL 之域，同量之 UPDATE 亦生多死元组，若 autovacuum 之阈限过谨，则死元组积聚，终至 autovacuum 触发——往往于高量之时，致 CPU 爆发。

真空阈限之式：其数理之基

自真空运行时，其于每表皆度此式：

VACUUM_TRIGGER_THRESHOLD = autovacuum_vacuum_threshold + 
                           (autovacuum_vacuum_scale_factor × n_live_tup)

其式为：

autovacuum_vacuum_threshold：绝对死元之最（其额：五十）
autovacuum_vacuum_scale_factor：表之巨细之比（其额：0.1 = 十分之一）
n_live_tup：表内活元之数

预察之配置

集群系统默认值：

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1

表A（7250万行）计算：

THRESHOLD = 50 + (0.1 × 725,000,000)
          = 50 + 72,500,000
          = 72.5 million dead tuples

解读：自动清理机制将不会触发于表A，直至72.5万死元组累积。此乃关键配置之失。

较之而言，表A实积死元一千二百四十万于清空之前——远超此限，示自清早已于生命周之始触发，然持续对积压之工也.

根因：加载模式与阈限失配

数据载入之务（每二至三时辰，以四重并行之工）更新行，用COALESCE合并之式：

UPDATE table_a t SET 
  column_1 = COALESCE(s.column_1, t.column_1),
  column_2 = COALESCE(s.column_2, t.column_2),
  column_3 = COALESCE(s.column_3, t.column_3)
FROM staging_table s
WHERE t.id = s.id

此模式于所触之每一行皆生成虚元组，无论其值是否实变。历二三时辰，涉兆行之多，虚元组生成之率，远逾自真空之能复空间，盖因阈值之值甚高。

其冲突：

批量UPDATE操作所生虚元组之率甚高
自真空阈值已依默认之用例校准（中表之典型大小，其表之变动率适中也）
无表级之覆载，以应此特定之工作模式

果：死元积聚，达表之十六有六，而后稳定

探究：诊断之问与所获

三问得诊断之明：

查问一：当前死元组状态

SELECT 
  relname, 
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_ratio_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('table_a', 'table_b')
ORDER BY n_dead_tup DESC;

结果：

表A：六百二十三兆活，一百二十四兆死（十六·六%）
表B：四百六十五兆活，七十四兆死（十三·七%）

查问二：活跃自清理进程

SELECT 
  pid, 
  query, 
  query_start,
  EXTRACT(EPOCH FROM (NOW() - query_start))::INT AS runtime_seconds
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%' 
  AND query NOT LIKE '%pg_stat%';

得二自真空工同运，一主一表，皆勤于探查时五十五分以上、三分钟以上。

问三：累积流失分析

SELECT 
  relname,
  n_live_tup,
  n_dead_tup,
  n_tup_upd + n_tup_del AS total_modifications,
  ROUND(100.0 * (n_tup_upd + n_tup_del) / n_live_tup, 1) AS churn_ratio_pct
FROM pg_stat_user_tables
WHERE relname IN ('table_a', 'table_b');

得

表A：七百二十五兆行，凡十六亿变改（累积流失二千二百三百分之一）。
表二：四百六十五兆行中，共有一百一十九亿之变改（累积流转率二千五百六十有六）

解读：此乃自实例肇始，累世之统计。二千二百有百分之一以上之比，示每行平均于实例存续间，约经触二十二回.

配置调适：公式之法

非临时更易，乃依公式之法，以定适阈。

同事治之，引典籍公式以明维护内存分配与阈限之算，证系统之常设不妥于此更迭之表。

已施之变（唯表级耳）

ALTER TABLE table_a SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 5000,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 5000
);

ALTER TABLE table_b SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 5000,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 5000
);

各参数之理：

缩放因子（0.1 → 0.01）：将比例触发自10%降为1%之表大小，致自动清理始于725万死元组而非7250万。是增清理频而减每操作之工量。
阈值（50 → 10000）： 设显式下限，以防于甚小之表过度触发清空，然犹允于大表合理触发之。
分析比例因子（0.05 → 0.005）： 分析（更新查询规划之表统计）触发更频，防于高更迭期陈旧之统计。
成本参数： cost_delay = 2ms 及cost_limit = 5000 将真空之工分而治之，渐次为之，间以长息，则每举之而CPU之峰渐平，终成其事。

表A之阈限，新算如左：

THRESHOLD = 10,000 + (0.01 × 725,000,000)
          = 10,000 + 7,250,000
          = 7.26 million dead tuples

此示 十倍之减也值阈限之内，致自动清空触发频数增十倍，然工作负荷则相应减焉。

要旨之决断：表级配置，非集群级

乃有意抉择，凡调谐诸参数惟施于表级而已，非施于系统/集群级也。

何故需设表级配置：

异构负载：集群中非所有表格皆同其更迭之态。表A、表B乃高更迭批量更新之目标。集群中他表或多静态或偏重读取。
阻绝级联之效：集群整体削减autovacuum_vacuum_scale_factor将致all表频发自动清理，虽含变动甚微者亦然。此或致：
- CPU与I/O之虚耗，因不必要之清理操作
- 稳表频遭分析
- 若同時多清理并行，则锁争加剧
风险之隔离： 仅调适所涉之表，则变故独及病源，不致他数据库之物或应用生意外之效。

此乃有意为之之工程之律：于最小之范围调适，以解其症。

结果：前後之度

配置之前（四月十三日，上午九时五十一分）：

度	表甲	表乙
死元组	一十二兆	七四兆
死率	一六·六%	一三·七%
最近自清理	不适用（初次清理）	不适用（初次清理）
CPU利用率	八五至九〇%	八五至九〇%
真空頻率	一时一刻，四至六时更替。	一时一刻，四至六时更替。

配置既毕（四月十三日午时十二时七分至一时四十五分）

公尺	表一	表B
死元组	零（真空完成）	二十一岁（渐衰）
亡率	零之百之一	七点八%
末次自清理	二零二六-四月十三日午时十二刻七分八秒	二零二六-四月十三日未时一时四十五分四十四秒
CPU利用率	三十至四十%	三十至四十%
清理频次	每载具循环多次	每载具循环多次

真空作业自若，无需人为干预。死元层级稳定，远低于新阈。CPU警报已消。

监察：主动之明察

为防再发，设监测之表，以录死元趋势。

CREATE TABLE pg_table_stats_history (
  captured_at TIMESTAMP DEFAULT NOW(),
  table_name TEXT,
  n_live_tup BIGINT,
  n_dead_tup BIGINT,
  dead_ratio_percent NUMERIC,
  n_tup_upd BIGINT,
  n_tup_del BIGINT,
  last_autovacuum TIMESTAMP
);

SELECT cron.schedule('capture_table_stats', '*/30 * * * *', $$
  INSERT INTO pg_table_stats_history
  SELECT 
    NOW(), relname, n_live_tup, n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2),
    n_tup_upd, n_tup_del, last_autovacuum
  FROM pg_stat_user_tables
  WHERE relname IN ('table_a', 'table_b');
$$);

每卅刻摄一影，可察日节之律： "于载具之时（午时十二至十四），死元聚至五八万，而后自净复归半万。"

要义：所察之秘

1. 自净之费机

VACUUM有二费制之数：

autovacuum_vacuum_cost_limit：工之单位（阅一页为壹，书一页为贰拾）所许，自真空暂歇之前
autovacuum_vacuum_cost_delay：成本所限，暂歇之毫秒数

成本限低，迟滞高，则真空缓，CPU峰微

成本限高，迟滞低，则真空速，CPU峰显

事变之前之配置，有cost_delay = 5ms与cost_limit = 1800（已显凌厉）。事后之配置，用cost_delay = 2ms与cost_limit = 5000，增其工之预算，然犹频令暂歇以分之。

当自真空者发，则遍察其表。若死之元者生之速于复之（盖载者犹行），则真空之务历时而久，耗CPU之能不绝。

事状：

表A之真空始于09:51:54，讫于12:07:08（二时十五分）
表B之真空始于08:59:39，讫于13:45:44（四时四十六分）
此间，载具亦在运行（始于12:37:27），遂生赘元

并作之业，致CPU与I/O之资争竞，故CPU利用率达八五至九成。

3. 回环之护：要义之背景机制

虽非此案之主动问题，然洞悉回环之护，实为要义之脉络也

PostgreSQL以四字节之事务标识（2^32，合四千三百亿可能值）。欲使多版本并发控制之可见性比较得宜，一时仅可用两千亿之值范围。若自动清理事务积压至甚，致未冻结之元组近两千亿事务之界，PostgreSQL将：

于两千兆事务余时，录警示之文
于百兆事务余时，转至唯读之模
若达上限则强制关机

此非空谈之虑——实乃安全之机，曾迫使监控不足之数据库停摆。自动清理须及时，否则数据库将废.

文档之救赎：独凭人工智能不足也

初遇此变，AI之诊器仅予泛策：曰"减迟滞之费"，曰"增限额之值"，曰"检维护之存"。

此策非谬，然未合其境。阈值之式、扩缩之理、群集之变，非独观AI之文，莫能明其危。

需返归本源之文牍以求解。

PostgreSQL VACUUM 文档（官言）释其费制与阈式
AWS Aurora PostgreSQL调优指南: 为受管 Aurora 实例提供情境特定之指导
《教科书参考书目关于MVCC》：明其死元之积，及自真空之御环也

：同僚之执于览籍，迫吾深察，乃得：

：自真空触发之决，所恃之数理公式也
：众更之务与默认阈限之交相激也
表级配置与簇级配置之风险与裨益

其训也： 基础之理—多版本并发控制、死亡元组、自动清理阈值、循环保护—乃不可动摇之基石。欲明其理，须读其文。基石既明，人工智能之器可加速诊断，且能献配置之策。然无基石，所献之策，不过旋钮耳，未解其果。

兼之基础阅读与人工智能辅助诊断，其效胜于单行其一.

未来事件之诊断查询

当自动清理CPU峰值突现，此查询立显其状：

死元状态：

SELECT 
  relname, n_live_tup, n_dead_tup,
  ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

活跃自动清理活动：

SELECT pid, query, query_start, 
  EXTRACT(EPOCH FROM (NOW() - query_start))::INT AS runtime_sec
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%';

客户流失率之析

SELECT 
  relname, n_live_tup, 
  n_tup_upd + n_tup_del AS total_mods,
  ROUND(100.0 * (n_tup_upd + n_tup_del) / n_live_tup, 1) AS churn_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

交易时日（回绕之险）：

SELECT 
  datname,
  age(datfrozenxid) AS txid_age,
  (SELECT setting::int FROM pg_settings 
   WHERE name = 'autovacuum_freeze_max_age') - age(datfrozenxid) 
   AS txid_remaining
FROM pg_database
WHERE datallowconn
ORDER BY txid_age DESC;

结论

是事明矣，PostgreSQL之VACUUM机制，须因事务之态而调。异于Oracle之Automatic Undo Retention Management，彼能自循策略而复旧表之空，PostgreSQL则需明设autovacuum之阈，以应各表更迭之序。

此解非由随机调参得，实因洞悉自真空之理，循法施之乎表域也。

其大义在术：遇生产数据库之效能困厄，究其机理（载于官书）而合诊断之实，较之独荐参数，其效尤胜。

参考文献之列

PostgreSQL 官方： VACUUM
PostgreSQL 官方： Autovacuum
AWS 数据库博客：于 Amazon RDS for PostgreSQL 中理解 autovacuum
Percona：克服 VACUUM 环绕之困
CYBERTEC：自真空包裹防护

推薦訂閱源

DEV Community