PostgreSQL教程FG087-PG常规维护VACUUM操作实战

本文档详细介绍PostgreSQL数据库VACUUM维护操作的原理、配置和实战方法，包括VACUUM、VACUUM FULL、ANALYZE等操作的详细说明，风哥教程参考PostgreSQL官方文档Routine Vacuuming内容，适合DBA在日常维护工作中使用。

Part01-基础概念与理论知识

1.1 PostgreSQL VACUUM概念

PostgreSQL使用多版本并发控制（MVCC）机制，当数据被更新或删除时，旧版本的数据不会被立即物理删除，而是标记为”死元组”（dead tuple）。VACUUM的作用就是清理这些死元组，回收空间供后续使用。如果不定期执行VACUUM，会导致表膨胀、查询性能下降、磁盘空间浪费等问题。更多视频教程www.fgedu.net.cn

PostgreSQL VACUUM主要作用：

回收被死元组占用的空间
更新表的统计信息
防止事务ID回卷
更新可见性映射
为索引优化提供支持

1.2 PostgreSQL VACUUM类型

# PostgreSQL VACUUM类型

# 1. VACUUM（标准VACUUM）
# – 标记死元组空间为可重用
# – 不锁表，不影响读写
# – 不回收磁盘空间给操作系统
# – 适合日常维护

# 2. VACUUM FULL
# – 重写整个表，回收所有空间
# – 锁表，阻塞读写
# – 回收磁盘空间给操作系统
# – 适合严重膨胀的表

# 3. VACUUM ANALYZE
# – 执行VACUUM后更新统计信息
# – 不锁表，不影响读写
# – 适合日常维护

# 4. ANALYZE
# – 只更新统计信息
# – 不清理死元组
# – 不锁表，不影响读写
# – 适合统计信息过期时

# 5. VACUUM (VERBOSE)
# – 显示详细的清理信息
# – 用于监控和诊断

# 6. VACUUM (FREEZE)
# – 冻结旧的事务ID
# – 防止事务ID回卷

1.3 PostgreSQL VACUUM参数说明

PostgreSQL VACUUM相关参数：

autovacuum：是否启用自动清理
autovacuum_vacuum_threshold：触发VACUUM的最小死元组数
autovacuum_vacuum_scale_factor：触发VACUUM的死元组比例
autovacuum_vacuum_cost_limit：自动清理成本限制
autovacuum_vacuum_cost_delay：自动清理成本延迟
vacuum_cost_limit：手动VACUUM成本限制
vacuum_cost_delay：手动VACUUM成本延迟

风哥提示：VACUUM是PostgreSQL维护的核心操作，理解其原理和配置对于保证数据库性能至关重要。建议保持autovacuum开启，并根据业务特点调整参数。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 PostgreSQL VACUUM规划

# PostgreSQL VACUUM规划要点

# 1. 自动清理配置
autovacuum = on # 启用自动清理
autovacuum_max_workers = 3 # 最大工作进程数
autovacuum_naptime = 1min # 检查间隔

# 2. 触发阈值配置
autovacuum_vacuum_threshold = 50 # 最小死元组数
autovacuum_vacuum_scale_factor = 0.1 # 死元组比例（10%）
autovacuum_analyze_threshold = 50 # 分析最小变化数
autovacuum_analyze_scale_factor = 0.05 # 分析变化比例（5%）

# 3. 性能控制配置
autovacuum_vacuum_cost_limit = 200 # 成本限制
autovacuum_vacuum_cost_delay = 2ms # 成本延迟

# 4. 大表特殊配置
# 对于大表，可以降低触发阈值
ALTER TABLE fgedu_big_table SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000
);

# 5. 高频更新表配置
# 对于高频更新的表，增加清理频率
ALTER TABLE fgedu_hot_table SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_cost_limit = 1000
);

2.2 PostgreSQL自动清理配置

PostgreSQL自动清理配置建议：

保持启用：风哥教程针对风哥教程针对生产环境建议保持autovacuum开启
合理阈值：根据表大小和更新频率设置触发阈值
性能控制：调整成本参数避免影响业务
监控告警：监控自动清理执行情况
特殊处理：对大表和高频更新表单独配置

2.3 PostgreSQL VACUUM调度规划

# PostgreSQL VACUUM调度规划

# 1. 自动清理（推荐）
# – 由autovacuum自动执行
# – 根据阈值自动触发
# – 对业务影响最小

# 2. 定期手动清理
# – 在业务低峰期执行
# – 针对特定表执行
# – 使用cron定时任务

# 3. 维护窗口清理
# – 在维护窗口执行VACUUM FULL
# – 需要停机或只读模式
# – 适合严重膨胀的表

# 定时任务示例
# 每天凌晨3点执行VACUUM
0 3 * * * /postgresql/scripts/maintain/vacuum_tables.sh

# 每周日凌晨4点执行VACUUM FULL（小表）
0 4 * * 0 /postgresql/scripts/maintain/vacuum_full_small.sh

# 每月1号凌晨2点执行统计信息更新
0 2 1 * * /postgresql/scripts/maintain/analyze_all.sh

风哥教程针对风哥教程针对生产环境建议：优先依赖autovacuum进行日常维护，手动VACUUM只在特殊情况下使用。VACUUM FULL需要锁表，应该只在维护窗口执行。学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 PostgreSQL VACUUM操作实战

3.1.1 标准VACUUM操作

# 标准VACUUM操作
$ psql -U fgedu -d fgedudb

# 对单个表执行VACUUM
fgedudb=# VACUUM fgedu_orders;
VACUUM

# 对单个表执行VACUUM VERBOSE
fgedudb=# VACUUM VERBOSE fgedu_orders;
INFO: vacuuming “public.fgedu_orders”
INFO: “fgedu_orders”: removed 10000 row versions in 50 pages
INFO: “fgedu_orders”: found 10000 removable, 50000 nonremovable row versions in 500 out of 550 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 12345
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
VACUUM

# 对整个数据库执行VACUUM
fgedudb=# VACUUM;
VACUUM

# 对指定Schema执行VACUUM
fgedudb=# VACUUM fgedu.orders, fgedu.customers;
VACUUM

# 对表和索引执行VACUUM
fgedudb=# VACUUM (VERBOSE, ANALYZE) fgedu_orders;
INFO: vacuuming “public.fgedu_orders”
INFO: “fgedu_orders”: removed 10000 row versions in 50 pages
INFO: analyzing “public.fgedu_orders”
INFO: “fgedu_orders”: scanned 500 of 500 pages, containing 50000 live rows and 0 dead rows; 50000 rows in sample, 50000 estimated total rows
VACUUM

# 查看VACUUM进度（PostgreSQL 9.6+）
fgedudb=# SELECT * FROM pg_stat_progress_vacuum;

3.1.2 VACUUM FULL操作

# VACUUM FULL操作（需要锁表）
$ psql -U fgedu -d fgedudb

# 查看表大小
fgedudb=# SELECT pg_size_pretty(pg_total_relation_size(‘fgedu_orders’));
pg_size_pretty
—————-
512 MB
(1 row)

# 执行VACUUM FULL
fgedudb=# VACUUM FULL VERBOSE fgedu_orders;
INFO: vacuuming “public.fgedu_orders”
INFO: “fgedu_orders”: found 0 removable, 50000 nonremovable row versions in 500 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.05 s, system: 0.01 s, elapsed: 0.10 s.
INFO: “fgedu_orders_pkey”: index “fgedu_orders_pkey” now contains 50000 row versions in 100 pages
INFO: analyzing “public.fgedu_orders”
VACUUM

# 再次查看表大小
fgedudb=# SELECT pg_size_pretty(pg_total_relation_size(‘fgedu_orders’));
pg_size_pretty
—————-
256 MB
(1 row)

# 注意：VACUUM FULL会锁表，阻塞所有读写操作
# 查看锁等待
fgedudb=# SELECT pid, mode, granted FROM pg_locks WHERE relation = ‘fgedu_orders’::regclass;

pid | mode | granted
——+———————+———
1234 | AccessExclusiveLock | t
(1 row)

# 使用pg_repack替代VACUUM FULL（不锁表）
# pg_repack需要单独安装
$ pg_repack -t fgedu_orders -d fgedudb
INFO: repacking table “public.fgedu_orders”

3.2 PostgreSQL ANALYZE操作实战

3.2.1 手动ANALYZE操作

# ANALYZE操作
$ psql -U fgedu -d fgedudb

# 对单个表执行ANALYZE
fgedudb=# ANALYZE fgedu_orders;
ANALYZE

# 对单个表执行ANALYZE VERBOSE
fgedudb=# ANALYZE VERBOSE fgedu_orders;
INFO: analyzing “public.fgedu_orders”
INFO: “fgedu_orders”: scanned 500 of 500 pages, containing 50000 live rows and 0 dead rows; 50000 rows in sample, 50000 estimated total rows
ANALYZE

# 对指定列执行ANALYZE
fgedudb=# ANALYZE fgedu_orders (order_no, customer_id);
ANALYZE

# 对整个数据库执行ANALYZE
fgedudb=# ANALYZE;
ANALYZE

# 查看表的统计信息
fgedudb=# SELECT
attname,
n_distinct,
null_frac,
avg_width
FROM pg_stats
WHERE tablename = ‘fgedu_orders’;

attname | n_distinct | null_frac | avg_width
————-+————+———–+———–
order_id | -1 | 0 | 4
order_no | -1 | 0 | 20
customer_id | 1000 | 0 | 4
status | 5 | 0 | 8
created_at | -1 | 0 | 8
(5 rows)

# 查看表的统计信息更新时间
fgedudb=# SELECT
relname,
last_analyze,
last_autoanalyze,
analyze_count,
autoanalyze_count
FROM pg_stat_user_tables
WHERE relname = ‘fgedu_orders’;

relname | last_analyze | last_autoanalyze | analyze_count | autoanalyze_count
————+——————————+——————————+—————+——————-
fgedu_orders | 2026-04-07 10:00:00.123456 | 2026-04-07 09:00:00.123456 | 2 | 10
(1 row)

3.2.2 统计信息管理

# 统计信息管理

# 设置统计目标
fgedudb=# ALTER TABLE fgedu_orders ALTER COLUMN customer_id SET STATISTICS 500;
ALTER TABLE

# 重新收集统计信息
fgedudb=# ANALYZE fgedu_orders;
ANALYZE

# 查看统计目标
fgedudb=# SELECT attname, attstattarget FROM pg_attribute
WHERE attrelid = ‘fgedu_orders’::regclass AND attnum > 0;

# 全局统计目标设置
fgedudb=# ALTER SYSTEM SET default_statistics_target = 200;
ALTER SYSTEM

fgedudb=# SELECT pg_reload_conf();
pg_reload_conf
—————-
t
(1 row)

# 删除统计信息
fgedudb=# UPDATE pg_class SET relpages = 0, reltuples = 0
WHERE relname = ‘fgedu_orders’;
UPDATE 1

# 禁用自动分析（不推荐）
fgedudb=# ALTER TABLE fgedu_orders SET (autovacuum_enabled = false);
ALTER TABLE

# 重新启用自动分析
fgedudb=# ALTER TABLE fgedu_orders SET (autovacuum_enabled = true);
ALTER TABLE

3.3 PostgreSQL自动清理监控

3.3.1 自动清理状态监控

# 自动清理状态监控
$ psql -U fgedu -d fgedudb

# 查看自动清理配置
fgedudb=# SHOW autovacuum;
autovacuum
————
on
(1 row)

fgedudb=# SELECT name, setting FROM pg_settings
WHERE name LIKE ‘autovacuum%’ ORDER BY name;

# 查看自动清理工作进程
fgedudb=# SELECT pid, datname, state, query
FROM pg_stat_activity
WHERE query LIKE ‘%autovacuum%’;

# 查看表的自动清理统计
fgedudb=# SELECT
relname,
n_live_tup,
n_dead_tup,
last_vacuum,
last_autovacuum,
vacuum_count,
autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

relname | n_live_tup | n_dead_tup | last_autovacuum | autovacuum_count
————+————+————+——————————+——————
fgedu_logs | 1000000 | 100000 | 2026-04-07 09:00:00.123456 | 10
fgedu_orders| 500000 | 50000 | 2026-04-07 08:00:00.123456 | 5
(2 rows)

3.3.2 自动清理性能调优

# 自动清理性能调优

# 查看当前成本参数
fgedudb=# SELECT name, setting FROM pg_settings
WHERE name LIKE ‘%cost%’ ORDER BY name;

# 调整自动清理性能参数
# 增加成本限制，加快清理速度
fgedudb=# ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;
ALTER SYSTEM

# 减少延迟，加快清理速度
fgedudb=# ALTER SYSTEM SET autovacuum_vacuum_cost_delay = ‘1ms’;
ALTER SYSTEM

# 增加工作进程数
fgedudb=# ALTER SYSTEM SET autovacuum_max_workers = 5;
ALTER SYSTEM

# 减少检查间隔
fgedudb=# ALTER SYSTEM SET autovacuum_naptime = ’30s’;
ALTER SYSTEM

# 重载配置
fgedudb=# SELECT pg_reload_conf();
pg_reload_conf
—————-
t
(1 row)

# 对特定表设置清理参数
fgedudb=# ALTER TABLE fgedu_logs SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_vacuum_cost_limit = 2000
);
ALTER TABLE

# 查看表级设置
fgedudb=# SELECT relname, reloptions FROM pg_class WHERE relname = ‘fgedu_logs’;

relname | reloptions
———–+—————————————————
fgedu_logs| {autovacuum_vacuum_scale_factor=0.05,autovacuum_vacuum_cost_limit=2000}
(1 row)

风哥提示：自动清理参数调优需要在清理效率和业务影响之间找到平衡。增加成本限制可以加快清理速度，但会增加I/O压力。建议根据服务器性能和业务特点进行调整。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 PostgreSQL死元组监控案例

4.1.1 死元组监控SQL

# 死元组监控SQL
$ psql -U fgedu -d fgedudb

# 查看所有表的死元组情况
fgedudb=# SELECT
schemaname,
relname,
n_live_tup as live_tuples,
n_dead_tup as dead_tuples,
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(relid)) as total_size,
last_vacuum,
last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

schemaname | relname | live_tuples | dead_tuples | dead_ratio | total_size | last_autovacuum
————+—————+————-+————-+————+————+——————————
public | fgedu_logs | 1000000 | 100000 | 9.09 | 512 MB | 2026-04-07 09:00:00.123456
public | fgedu_orders | 500000 | 50000 | 9.09 | 256 MB | 2026-04-07 08:00:00.123456
public | fgedu_customers| 100000 | 10000 | 9.09 | 64 MB | 2026-04-07 07:00:00.123456
(3 rows)

# 查看需要VACUUM的表
fgedudb=# SELECT
schemaname,
relname,
n_dead_tup,
round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
AND n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) > 0.1
ORDER BY n_dead_tup DESC;

# 查看表的膨胀率（使用pgstattuple扩展）
fgedudb=# CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE EXTENSION

fgedudb=# SELECT * FROM pgstattuple(‘fgedu_orders’);

table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
———————+————-+———–+—————+——————+—————-+——————–+————+————–
5360844800 | 500000 | 200000000 | 3.73 | 100000 | 40000000 | 0.75 | 1073741824 | 20.00
(1 row)

# 查看索引膨胀率
fgedudb=# SELECT * FROM pgstatindex(‘fgedu_orders_pkey’);

version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
———+————+————+—————+—————-+————+————-+—————+——————-+——————–
4 | 2 | 1073741824| 4128| 50 | 10000| 0 | 0 | 90.00 | 10.00
(1 row)

4.2 PostgreSQL VACUUM性能优化案例

4.2.1 大表VACUUM优化

# 大表VACUUM优化案例

# 场景：fgedu_logs表有1亿条记录，死元组达到1000万

# 1. 查看表当前状态
fgedudb=# SELECT
relname,
n_live_tup,
n_dead_tup,
pg_size_pretty(pg_total_relation_size(relid)) as size
FROM pg_stat_user_tables
WHERE relname = ‘fgedu_logs’;

# 2. 调整表级autovacuum参数
fgedudb=# ALTER TABLE fgedu_logs SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 100000,
autovacuum_vacuum_cost_limit = 2000,
autovacuum_vacuum_cost_delay = ‘1ms’
);
ALTER TABLE

# 3. 手动触发VACUUM（使用VERBOSE监控进度）
fgedudb=# VACUUM (VERBOSE) fgedu_logs;
INFO: vacuuming “public.fgedu_logs”
INFO: “fgedu_logs”: removed 10000000 row versions in 50000 pages
CPU: user: 30.00 s, system: 5.00 s, elapsed: 120.00 s.
VACUUM

# 4. 验证清理结果
fgedudb=# SELECT
relname,
n_live_tup,
n_dead_tup,
pg_size_pretty(pg_total_relation_size(relid)) as size
FROM pg_stat_user_tables
WHERE relname = ‘fgedu_logs’;

# 5. 如果表严重膨胀，考虑VACUUM FULL（维护窗口）
fgedudb=# VACUUM FULL VERBOSE fgedu_logs;
INFO: vacuuming “public.fgedu_logs”
CPU: user: 60.00 s, system: 10.00 s, elapsed: 300.00 s.
VACUUM

# 6. 最终结果
fgedudb=# SELECT pg_size_pretty(pg_total_relation_size(‘fgedu_logs’));
pg_size_pretty
—————-
40 GB
(1 row)

4.3 PostgreSQL VACUUM问题排查案例

4.3.1 VACUUM无法清理死元组问题

# 问题：VACUUM执行后死元组仍然存在

# 1. 检查是否有长事务
fgedudb=# SELECT
pid,
usename,
state,
now() – xact_start as duration,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

# 问题原因：长事务阻止了VACUUM清理

# 2. 解决方案：终止长事务
fgedudb=# SELECT pg_terminate_backend(1234);
pg_terminate_backend
———————-
t
(1 row)

# 3. 重新执行VACUUM
fgedudb=# VACUUM fgedu_orders;
VACUUM

# 4. 检查是否有prepared事务
fgedudb=# SELECT * FROM pg_prepared_xacts;

transaction | gid | prepared | owner | database
————-+—–+———-+——-+———-
(0 rows)

# 5. 检查是否有复制槽阻止清理
fgedudb=# SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

slot_name | active | restart_lsn
—————+——–+————-
standby_slot | f | 0/10000000
(1 row)

# 问题原因：非活跃的复制槽阻止了WAL清理

# 6. 删除不需要的复制槽
fgedudb=# SELECT pg_drop_replication_slot(‘standby_slot’);
pg_drop_replication_slot
————————–

(1 row)

# 7. 检查是否有其他原因
fgedudb=# SELECT * FROM pg_stat_activity WHERE backend_xmin IS NOT NULL;

pid | datid | datname | usename | backend_xmin
—–+——-+———+———+————–
(0 rows)

风哥教程针对风哥教程针对生产环境建议：VACUUM无法清理死元组的常见原因包括长事务、prepared事务、复制槽、逻辑复制等。定期检查这些因素，确保VACUUM能够正常工作。from PostgreSQL视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 PostgreSQL VACUUM最佳实践

PostgreSQL VACUUM最佳实践：

保持autovacuum开启：让系统自动处理大部分清理工作
合理设置阈值：根据表大小和更新频率调整触发阈值
监控死元组：定期检查表的死元组情况
避免长事务：长事务会阻止VACUUM清理
定期维护：在维护窗口执行VACUUM FULL
使用pg_repack：大表使用pg_repack替代VACUUM FULL

5.2 PostgreSQL VACUUM维护脚本

#!/bin/bash
# vacuum_maintenance.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# PostgreSQL VACUUM维护脚本

PGHOME=/postgresql/fgapp
PGUSER=fgedu
PGDATABASE=fgedudb
LOG_FILE=/postgresql/scripts/logs/vacuum_maintenance.log

log_message() {
echo “$(date ‘+%Y-%m-%d %H:%M:%S’) – $1″ >> $LOG_FILE
}

# 获取需要VACUUM的表
get_vacuum_tables() {
$PGHOME/bin/psql -U $PGUSER -d $PGDATABASE -t -A -F’|’ -c ”
SELECT schemaname, relname, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
AND n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) > 0.1
ORDER BY n_dead_tup DESC;
”
}

# 执行VACUUM
do_vacuum() {
local schema=$1
local table=$2

log_message “VACUUM $schema.$table”
$PGHOME/bin/psql -U $PGUSER -d $PGDATABASE -c “VACUUM ANALYZE $schema.$table;” >> $LOG_FILE 2>&1
}

# 主函数
main() {
log_message “==========================================”
log_message “开始VACUUM维护”
log_message “==========================================”

tables=$(get_vacuum_tables)

if [ -z “$tables” ]; then
log_message “没有需要VACUUM的表”
else
echo “$tables” | while IFS=’|’ read -r schema table dead_tup; do
do_vacuum $schema $table
done
fi

log_message “==========================================”
log_message “VACUUM维护完成”
log_message “==========================================”
}

main

5.3 PostgreSQL VACUUM检查清单

# PostgreSQL VACUUM检查清单

# 配置检查
– [ ] autovacuum已启用
– [ ] 触发阈值配置合理
– [ ] 成本参数配置合理
– [ ] 工作进程数配置合理

# 监控检查
– [ ] 死元组监控正常
– [ ] 自动清理执行正常
– [ ] 表膨胀率正常
– [ ] 索引膨胀率正常

# 维护检查
– [ ] 定期VACUUM执行
– [ ] 长事务监控
– [ ] 复制槽检查
– [ ] prepared事务检查

# 性能检查
– [ ] VACUUM不影响业务
– [ ] I/O压力可控
– [ ] 清理效率合理
– [ ] 空间回收正常

风哥提示：VACUUM是PostgreSQL维护的核心操作，需要持续关注和优化。建议建立完善的监控体系，及时发现和处理问题，保证数据库的健康运行。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html