PostgreSQL / 系统课时
为热点表设置表级 Autovacuum 参数
为热点表设置表级 Autovacuum 参数
全局 autovacuum 参数只能提供折中基线,高 churn 表往往需要更早触发、更高资源预算或更积极的冻结策略。本课使用表级 reloptions 覆盖阈值、限速和冻结参数,让热点表单独治理而不拖动整个实例。
学习目标
- 用表大小和变更量解释 autovacuum 的触发阈值
- 使用
ALTER TABLE ... SET (...)为单表配置 autovacuum reloptions - 验证表级参数是否生效,并避免把冻结安全边界当作性能旋钮
把触发阈值变成可解释规则
对 UPDATE、DELETE 造成的死元组,autovacuum 的触发点可以近似理解为:
autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples
其中 reltuples 是统计估算值,所以这不是逐行精确计数。对于一张一百万行的表,如果表级 threshold 为 500、scale factor 为 0.02,触发量级约为 20,500 个死元组。相比默认的统一策略,这能让大表在更低比例的 churn 下更早进入清理。
ANALYZE 使用同样的“固定阈值 + 比例阈值”思路,由 autovacuum_analyze_threshold 和 autovacuum_analyze_scale_factor 控制。纯追加型或大量 INSERT 的表还应评估 autovacuum_vacuum_insert_threshold 与 autovacuum_vacuum_insert_scale_factor,不要只盯着死元组。
给热点表设置 reloptions
下面创建一张实验表,并为它设置更积极的清理阈值、单表成本限速和更早的 freeze table age。数值用于演示调节方法,生产环境应根据表大小、churn、I/O 余量和历史 VACUUM 时长确定。
DROP TABLE IF EXISTS public.hot_orders_lab;
CREATE TABLE public.hot_orders_lab (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status text NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE public.hot_orders_lab SET (
autovacuum_vacuum_threshold = 500,
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_analyze_threshold = 500,
autovacuum_analyze_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 2,
autovacuum_vacuum_cost_limit = 800,
autovacuum_freeze_table_age = 100000000
);
SELECT option_name, option_value
FROM pg_options_to_table(
(SELECT reloptions
FROM pg_class
WHERE oid = 'public.hot_orders_lab'::regclass)
)
ORDER BY option_name;预期输出
option_name | option_value
--------------------------------+-------------
autovacuum_analyze_scale_factor | 0.01
autovacuum_analyze_threshold | 500
autovacuum_freeze_table_age | 100000000
autovacuum_vacuum_cost_delay | 2
autovacuum_vacuum_cost_limit | 800
autovacuum_vacuum_scale_factor | 0.02
autovacuum_vacuum_threshold | 500
(7 rows)表级 reloptions 只影响这张表。这样可以让热点表更积极,而普通表继续使用全局基线。显式设置表级 autovacuum_vacuum_cost_limit 的 worker 不参与普通的 autovacuum cost-limit 平衡,因此同时给多张热点表设置很高的 Limit 可能把总 I/O 压力意外放大。
区分触发、限速和冻结三类参数
触发阈值决定“什么时候开始”,Cost Delay 与 Cost Limit 决定“开始后跑多快”,freeze 参数决定“多老的事务版本需要被处理”。这三类参数作用不同,调错方向会得到相反结果。
| 目标 | 主要表级参数 | 常见调节方向 |
|---|---|---|
| 更早清理 UPDATE/DELETE 垃圾 | autovacuum_vacuum_threshold、autovacuum_vacuum_scale_factor |
降低阈值或比例 |
| 更早刷新统计信息 | autovacuum_analyze_threshold、autovacuum_analyze_scale_factor |
对查询计划敏感的表适当降低 |
| 改变单表清理速度 | autovacuum_vacuum_cost_delay、autovacuum_vacuum_cost_limit |
减小 Delay 或增大 Limit 会更激进 |
| 提前进入更积极的冻结扫描 | autovacuum_freeze_table_age |
适当降低可更早处理老 XID |
验证实际运行而不只看 reloptions
配置存在不等于治理有效。表创建并产生持续 UPDATE/DELETE 后,应结合统计视图观察死元组、最后一次 autovacuum 时间和累计运行次数。统计值是估算并存在刷新延迟,适合看趋势,不适合作为逐行精确计数。
SELECT
relname,
n_live_tup,
n_dead_tup,
last_autovacuum,
autovacuum_count,
last_autoanalyze,
autoanalyze_count
FROM pg_stat_user_tables
WHERE relid = 'public.hot_orders_lab'::regclass;预期输出
relname | n_live_tup | n_dead_tup | last_autovacuum | autovacuum_count | last_autoanalyze | autoanalyze_count
----------------+------------+------------+-----------------+------------------+------------------+------------------
hot_orders_lab | <VALUE> | <VALUE> | <TIME_OR_NULL> | <VALUE> | <TIME_OR_NULL> | <VALUE>
(1 row)如果表级阈值降低后仍长时间没有 autovacuum,不要继续盲目降低 scale factor;应进一步检查 worker 是否被其他大表占用、全局并发上限、autovacuum 是否启用以及存储是否长期饱和。表级参数的价值在于把“这张热点表需要更积极”表达清楚,而不是绕过实例级资源上限。