一、PostgreSQL VACUUM命令介绍：
1.1、VACUUM功能
PostgreSQL的VACUUM命令用于回收死元组（dead tuples）占用的存储空间。在PostgreSQL中，被删除或更新废弃的元组并没有在物理上从它们的表中移除，它们将一直存在直到一次VACUUM被执行。因此，有必要周期性地执行VACUUM，特别是在频繁被更新的表上。
1.2、VACUUM语法：
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table_name ]复制
FULL选项会执行更广泛的处理，包括跨块移动行以压缩表，但会锁定整个表； FREEZE选项用于冻结旧的事务ID，防止事务ID包裹问题； VERBOSE选项会显示详细的清理工作报告； ANALYZE选项用于更新查询优化器的统计信息。 table_name是指要清理的表的名称，如果未加表，则VACUUM会处理当前数据库中的所有表。
1.3、功能预览图
二、手动清理死元组演示
2.1、VACUUM手动清理死元组
说明： VACUUM主要用于回收表中的死数据，即那些已经被删除或更新但尚未从物理存储中移除的数据。这个过程不会释放磁盘空间给操作系统，但会将这些死数>据标记为可重用，以便在将来插入新数据时可以重用这些空间。VACUUM操作不会锁定表，因此可以与其他操作并行进行。对于有大量更新的表，VACUUM通常就足够了，因为它的空间会再次增长。
代码如下：
postgres=# create table t1(id int); CREATE TABLE postgres=# insert into t1 values (generate_series(1,20000000)); INSERT 0 20000000 [root@PostgreSQLBeta ~]# df -h 文件系统 容量 已用 可用 已用% 挂载点 devtmpfs 952M 0 952M 0% /dev tmpfs 970M 1.1M 968M 1% /dev/shm tmpfs 970M 8.8M 961M 1% /run tmpfs 970M 0 970M 0% /sys/fs/cgroup /dev/nvme0n1p3 46G 7.9G 38G 18% / /dev/nvme0n1p1 295M 190M 105M 65% /boot tmpfs 194M 0 194M 0% /run/user/0 [root@PostgreSQLBeta ~]# postgres=# postgres=# delete from t1 where id<=10000000; DELETE 10000000 postgres=# postgres=# vacuum t1; VACUUM postgres=# [root@PostgreSQLBeta ~]# df -h 文件系统 容量 已用 可用 已用% 挂载点 devtmpfs 952M 0 952M 0% /dev tmpfs 970M 1.1M 968M 1% /dev/shm tmpfs 970M 8.8M 961M 1% /run tmpfs 970M 0 970M 0% /sys/fs/cgroup /dev/nvme0n1p3 46G 7.9G 38G 18% / /dev/nvme0n1p1 295M 190M 105M 65% /boot tmpfs 194M 0 194M 0% /run/user/0 [root@PostgreSQLBeta ~]#复制
2.2、VACUUM FULL手动清理死元组
说明： VACUUM FULL是一个更彻底的清理过程，它不仅执行常规VACUUM的功能，还会重新组织表中的数据，将有效数据移动到表的开头，释放表末尾的空页，并将这些空页交还给操作系统。这个过程会锁定表，因此在执行期间不能对表进行访问。
- 注意：由于VACUUM FULL会锁定表，它可能会对数据库的可用性造成影响，因此通常不建议在生产环境中频繁使用。
代码如下：
postgres=# create table t1(id int); CREATE TABLE postgres=# insert into t1 values (generate_series(1,20000000)); INSERT 0 20000000 [root@PostgreSQLBeta ~]# df -h 文件系统 容量 已用 可用 已用% 挂载点 devtmpfs 952M 0 952M 0% /dev tmpfs 970M 1.1M 968M 1% /dev/shm tmpfs 970M 8.8M 961M 1% /run tmpfs 970M 0 970M 0% /sys/fs/cgroup /dev/nvme0n1p3 46G 7.9G 38G 18% / /dev/nvme0n1p1 295M 190M 105M 65% /boot tmpfs 194M 0 194M 0% /run/user/0 [root@PostgreSQLBeta ~]# postgres=# postgres=# delete from t1 where id<=10000000; DELETE 10000000 postgres=# postgres=# vacuum full; VACUUM [root@PostgreSQLBeta ~]# df -h 文件系统 容量 已用 可用 已用% 挂载点 devtmpfs 952M 0 952M 0% /dev tmpfs 970M 1.1M 968M 1% /dev/shm tmpfs 970M 8.8M 961M 1% /run tmpfs 970M 0 970M 0% /sys/fs/cgroup /dev/nvme0n1p3 46G 7.5G 39G 17% / /dev/nvme0n1p1 295M 190M 105M 65% /boot tmpfs 194M 0 194M 0% /run/user/0 [root@PostgreSQLBeta ~]#复制
2.3、vacuumdb命令行工具清理死元组
说明： vacuumdb 是一个用于清理 PostgreSQL 数据库的工具，它可以帮助你回收由死元组占用的存储空间。死元组是指在更新或删除操作后不再被引用的数据行。vacuumdb 命令可以清除这些死元组，并将释放的空间标记为可重用。
- 基本用法 要使用 vacuumdb 清理特定表的死元组，你可以使用以下命令格式：
vacuumdb -U username -d databasename -t tablename复制
- 说明： -U ：数据库用户名 -d： 要清理的数据库名称 -t ：要清理的表名称。
如果您要查看更多信息，可查看帮助vacuumdb --help
代码如下：
[postgres@PostgreSQLBeta ~]$ vacuumdb --help vacuumdb cleans and analyzes a PostgreSQL database. Usage: vacuumdb [OPTION]... [DBNAME] Options: -a, --all vacuum all databases --buffer-usage-limit=SIZE size of ring buffer used for vacuum -d, --dbname=DBNAME database to vacuum --disable-page-skipping disable all page-skipping behavior -e, --echo show the commands being sent to the server -f, --full do full vacuuming -F, --freeze freeze row transaction information --force-index-cleanup always remove index entries that point to dead tuples -j, --jobs=NUM use this many concurrent connections to vacuum --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum --no-index-cleanup don't remove index entries that point to dead tuples --no-process-main skip the main relation --no-process-toast skip the TOAST table associated with the table to vacuum --no-truncate don't truncate empty pages at the end of the table -n, --schema=SCHEMA vacuum tables in the specified schema(s) only -N, --exclude-schema=SCHEMA do not vacuum tables in the specified schema(s) -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available -q, --quiet don't write any messages --skip-locked skip relations that cannot be immediately locked -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only -v, --verbose write a lot of output -V, --version output version information, then exit -z, --analyze update optimizer statistics -Z, --analyze-only only update optimizer statistics; no vacuum --analyze-in-stages only update optimizer statistics, in multiple stages for faster results; no vacuum -?, --help show this help, then exit Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as -w, --no-password never prompt for password -W, --password force password prompt --maintenance-db=DBNAME alternate maintenance database Read the description of the SQL command VACUUM for details. Report bugs to <pgsql-bugs@lists.postgresql.org>. PostgreSQL home page: <https://www.postgresql.org/>复制
示例：
使用用户：postgres，数据：postgres， 清理表名：t4。 主要功能是清理死元组、更新统计信息，并显示详细的清理过程信息。
代码如下：
[postgres@PostgreSQLBeta ~]$ vacuumdb -U postgres -d postgres -t t4 -f -z -v vacuumdb: vacuuming database "postgres" INFO: vacuuming "public.t4" INFO: "public.t4": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: analyzing "public.t4" INFO: "t4": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows [postgres@PostgreSQLBeta ~]$复制
三、自动清理机制
3.1、autoVacuum功能
- autovacuum功能： PostgreSQL的autovacuum是一个自动执行VACUUM和ANALYZE命令的系统辅助进程，它的主要目的是回收被标识为删除状态的空间，更新表的统计信息，以及防止事务ID回卷。autovacuum可以自动执行这些操作，减轻数据库管理员的负担，并确保数据库性能不会因为数据膨胀而下降。
- autovacuum的配置参数 autovacuum的行为可以通过一系列配置参数进行调整，包括： autovacuum：控制是否启动系统自动清理功能，默认值为on。 autovacuum_max_workers：设置系统自动清理工作进程的最大数量。 autovacuum_naptime：设置两次系统自动清理操作之间的间隔时间。 autovacuum_vacuum_threshold和autovacuum_analyze_threshold：设置当表上被更新的元组数的阈值超过这些阈值时分别需要执行vacuum和analyze。 autovacuum_vacuum_scale_factor和autovacuum_analyze_scale_factor：设置表大小的缩放系数。 autovacuum_freeze_max_age：设置需要强制对数据库进行清理的XID上限值。
3.2、autovacuum配置
说明：开启autovacuum相关参数，在生产系统中不建议将其关闭
代码如下;
vi $PGDATA/postgresql.conf autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart) autovacuum_naptime = 1min # time between autovacuum runs autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts # before vacuum; -1 disables insert # vacuums autovacuum_analyze_threshold = 50 # min number of row updates before # analyze autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table # size before insert vacuum autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age # before forced vacuum # (change requires restart) autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit复制
四、执行vacuum full错误
4.1、在执行vacuum full产生错误
代码如下：
postgres=# vacuum full; 2024-06-23 01:51:46.764 CST [774244] ERROR: could not create file "base/5/25578": 设备上没有空间 2024-06-23 01:51:46.764 CST [774244] STATEMENT: vacuum full; ERROR: could not create file "base/5/25578": 设备上没有空间 postgres=#复制
- 结论：由于磁盘空间不足，导致无法执行vacuum full。 由于在执行vacuum full过程中，会占用磁盘空间，导致存储空间翻倍，因此在执行vacuum full时，需注意磁盘空间，并禁止在业务繁忙期间执行该操作。
五、总结
5.1、在以下情况下应该避免使用 VACUUM FULL 选项：
- 高并发环境：在高并发的生产环境中，VACUUM FULL 可能会导致长时间的锁等待，影响其他事务的执行。
- 业务高峰期：在业务高峰期运行 VACUUM FULL 可能会导致锁竞争加剧，增加死锁的风险，因此不建议在此类时期执行。
- 频繁更新的表：对于经常更新的表，简单的 VACUUM 操作通常足够，因为它可以在不影响并发操作的情况下回收空间。
- 系统表：除非特别需要，否则通常不建议对系统表执行 VACUUM FULL，因为这可能会影响数据库的整体性能。
- 备份策略：如果数据库有定期备份的策略，可以利用备份和还原机制来恢复表的状态，而不是依赖 VACUUM FULL 来清理表。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
