Postgres执行VACUUM FULL的最小死元组占比及表适配咨询

问题背景 #

作为开发者，我正在优化维护PostgreSQL数据库，由于磁盘占用增长过快，研究清理碎片并释放空间的方案时发现VACUUM FULL可回收死元组空间，但不清楚执行该命令的死元组数量/占比标准。目前Nextcloud数据库中有两张存在死元组的表，总关系大小远超\dt+报告的磁盘使用量，需判断是否适合执行VACUUM FULL。

表统计信息 #

1. 表磁盘使用量（\dt+命令输出） #

Schema |            Name             | Type  |  Owner   |    Size    | Description 
--------+-----------------------------+-------+----------+------------+-------------
public | oc_activity                 | table | XXXXXXXX | 4796 MB    | 
public | oc_filecache                | table | XXXXXXXX | 127 MB     | 

2. oc_activity表详情 #

总关系大小（含索引、TOAST表等）

SELECT pg_size_pretty( pg_total_relation_size('oc_activity') )

输出：

pg_size_pretty 
----------------
 9666 MB

表统计数据

SELECT * FROM pg_stat_all_tables WHERE relname='oc_activity'

输出：

relid | schemaname |   relname   | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count 
-------+------------+-------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------+-----------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
 yyyyy | public     | oc_activity |      272 |   1046966870 |     4737 |      57914604 |   1548217 |         0 |    325585 |             0 |   11440511 |     940192 |              268430 |             |                 |              | 2023-02-15 10:01:36.657028+00 |            0 |                0 |             0 |                 3

3. oc_filecache表详情 #

总关系大小

SELECT pg_size_pretty( pg_total_relation_size('oc_filecache') )

输出：

pg_size_pretty 
----------------
 541 MB

表统计数据

SELECT * FROM pg_stat_all_tables WHERE relname='oc_filecache'

输出：

relid | schemaname |   relname    | seq_scan | seq_tup_read |  idx_scan  | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count 
-------+------------+--------------+----------+--------------+------------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
 zzzzz | public     | oc_filecache |   104541 |  28525391484 | 1974398333 |    2003365293 |     43575 |    695612 |     39541 |        348823 |     461510 |      19418 |                4069 |             | 2023-02-16 10:46:15.165442+00 |              | 2023-02-16 16:25:32.568168+00 |            0 |                8 |             0 |                33

适用性分析与建议 #

oc_activity表：强烈建议执行VACUUM FULL #

• 死元组占比约7.5%，虽不算极高，但从未执行过任何VACUUM操作（手动/自动次数均为0），死元组持续积累导致空间膨胀严重。
• 总关系大小是表大小的近2倍，执行VACUUM FULL预计可释放约9666MB - 4796MB = 4870MB空间，效果显著。

oc_filecache表：建议根据业务情况执行VACUUM FULL #

• 死元组占比约4%，自动VACUUM已执行8次，但总关系大小仍是表大小的4倍多，说明存在严重的索引或表碎片膨胀，自动VACUUM无法释放已占用的文件系统空间。
• 执行VACUUM FULL预计可释放约541MB - 127MB = 414MB空间，需注意该命令会锁表，务必选择业务低峰期执行。

注意事项 #

• VACUUM FULL会对目标表加排他锁，执行前需备份数据，并确保在业务低峰期操作，避免影响服务可用性。
• 执行完成后建议手动执行ANALYZE命令，更新表统计信息，保证查询优化器能生成最优执行计划。
• 后续需检查PostgreSQL自动VACUUM配置（如autovacuum_vacuum_threshold、autovacuum_vacuum_scale_factor等参数），调整至适合业务的阈值，防止死元组和空间膨胀再次积累。

内容的提问来源于stack exchange，提问作者Rob Wilkinson