postgresql数据库中Vacuum

简介

        在使用 oracle数据库，我们都知道，如果使用delete语句，数据并不是立即清理掉，而只是被标价为不可见，往往需要手动处理，例如（alter tablespace *****）。

        产生高水位的原因，往往是当数据库中的数据不断插入、更新或删除时，表或索引的大小可能会增长，占用更多的存储空间。如果存储空间使用率达到了接近或超过数据库的容量限制，则会导致性能下降、查询执行变慢甚至数据库崩溃。所以工作中建议需要周期性处理高水位问题。

       在PostgreSQL数据库中，对表进行DELETE操作后，原有数据并不会立即被删除，而且在更新数据时，也并不会在原有数据上做更新，而是会新生成一行数据。此时，原有数据只是被标识为不可见状态，被称为多版本模式。旧版本的数据会导致查询更慢一些，因为旧版本的数据存储于数据文件中，查询时同样也会被扫描。只有在没有并发的其他事务读到这些旧数据时，才会将其清除。这个清除工作就是由AutoVacuum进程来完成的。同时一些系统统计信息的收集也是由AutoVacuum进程来完成的。

语法

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

其中option可以是下列之一：

    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP [ boolean ]
    TRUNCATE [ boolean ]
    PARALLEL integer

参数详解

 FULL    

       选择“完全”清理，它可以收回更多空间，并且需要更长时间和表上的排他锁。这种方法还需要额外的磁盘空间，因为它会创建该表的一个新拷贝，并且在操作完成之前都不会释放旧的拷贝。通常这种方法只用于需要从表中收回数量庞大的空间时。------不推荐

FREEZE

选择激进的元组“冻结”。指定FREEZE 等价于参数vacuum_freeze_min_age和 vacuum_freeze_table_age设置为0的 VACUUM。强制VACUUM总是扫描所有页面， 因此指定FULL时这个选项是多余的。VACUUM FULL 和 VACUUM FREEZE 都是比普通的 VACUUM 操作更为耗时和消耗资源的。------不推荐

VERBOSE

展示详细vacuum执行进程情况        

ANALYZE

更新优化器用以决定最有效执行一个查询的方法的统计信息。

DISABLE_PAGE_SKIPPING

通常，VACUUM将基于可见性映射（vm文件）跳过页面。已知所有元组都被冻结的页面总是会被跳过，而那些所有元组对所有事务都可见的页面则可能会被跳过（除非执行的是激进的清理）。此外，除非在执行激进的清理时，一些页面也可能会被跳过，这样可避免等待其他页面完成对其使用。这个选项禁用所有的跳过页面的行为，其意图是只在可见性映射内容被怀疑时使用，这种情况只有在硬件或者软件问题导致数据库损坏时才会发生。------不推荐

SKIP_LOCKED

规定VACUUM在开始处理关系时不等待任何冲突锁被释放：如果关系不能立即锁定而不等待，则跳过关系。 请注意即使采用此选项，VACUUM在打开关系的索引时仍可能阻塞。 此外，VACUUM ANALYZE在从分区、继承子表和某些类型的外表获取示例行时，仍然可能阻塞。 还有，虽然VACUUM通常处理指定分区表的所有分区，但如果分区表上的锁冲突， 此选项将导致VACUUM跳过所有分区。------不推荐

INDEX_CLEANUP

规定VACUUM尝试删除指向死元组的索引条目。 这通常是所需的行为，并且是默认行为，除非将vacuum_index_cleanup 选项设置为 false，对要被清空的表。 如果需要尽快运行清空操作的话，将此选项设置为 false 可能很有用，例如，为了避免即将发生的事务 ID 回绕（xid回卷到0）。 但是，如果不定期执行索引清理，性能可能会受到影响，因为随着表的修改，索引将累积死元组，并且表本身将累积死行指针，在索引清理完成之前都无法删除。 此选项对于没有索引的表无效，如果使用 FULL 选项，则忽略此选项。

TRUNCATE

指定VACUUM尝试截断表末尾的任何空页，并允许将截断页的磁盘空间返回到操作系统。 这通常是所需的行为，并且是默认行为，除非将vacuum_truncate选项设置为 false，对要被清空的表。 将此选项设置为 false 可能有助于避免ACCESS EXCLUSIVE锁定需要截断的表。如果使用FULL选项，则忽略此选项。--不推荐

PARALLEL

 使用integer后台处理器并行执行VACUUM 的索引真空和索引清理阶段。 用于执行操作的处理器数量等于关系上支持并行清理的索引数量，该数量受PARALLEL 数量的限制，如果有的话，该数量还受到max_parallel_maintenance_workers限制。 当且仅当索引的大小大于min_parallel_index_scan_size时，索引才能参与并行清理。 请注意，不保证在执行期间会使用integer中指定的并行工作线程数。 清理运行时可能需要比指定的更少的处理器，甚至根本没有处理器。每个索引只能使用一名处理器。 所以只有当表中至少有2索引时才会启动并行工作程序。 在每个阶段开始之前启动清理工作进程，并在阶段结束时退出。这些行为可能会在未来的版本中发生变化。 此选项不能与FULL选项一起使用。--不推荐

       其实在使用vacuum指令时，我们使用更多VERBOSE，ANALYZE，而INDEX_CLEANUP使用相对较少一点。建议使用多频率指定表名，替代使用vacuum full。

      在使用vacuum 指令时表会被添加ShareUpdateExclusiveLock五级排他锁

      在使用vacuum full 指令时表会被添加AccessExclusiveLock八级访问独占锁锁

注意：

 vacuum语法并不是一个SQL语法标准

MySQL：MySQL 使用 OPTIMIZE TABLE 命令来执行表的优化和碎片整理操作。它会重新组织表的数据并重新建立索引，以提高查询性能。此命令等效于 PostgreSQL 的 VACUUM FULL。

Oracle Database：Oracle 使用 ALTER TABLE ... DEALLOCATE UNUSED 命令来释放表中的未使用空间。它会删除表中已经删除的数据行，并回收空间。此命令类似于 PostgreSQL 的 VACUUM。

Microsoft SQL Server：SQL Server 使用 DBCC CHECKDB 命令来检查数据库的完整性和清理空间。此命令可修复索引、删除无效的引用，以及回收未使用的空间。

进程

启动PostgreSQL数据库时，会先启动一个叫Postmaster的主进程，还会fork出一些辅助子进程，这些辅助子进程各自负责一部分功能。其中包含了autovacuum进程。

通过以下数据字典表查看表的dead touple数量，可以反映出磁盘空间的利用率

pg_stat_user_tables；--相较于pg_stat_all_tables，过滤了pg_catalog，information_schema，pg_toast。
pg_stat_all_tables ;

以上两表字段含义均相同，数据库版本不同字段数量会有不相同，基于postgresql-15.3举例。

relid:一个表的 OID
schemaname:这个表所在的模式的名称
relname:这个表的名称
seq_scan:在这个表上发起的顺序扫描的次数
seq_tup_read:连续扫描获取的实时行数
idx_scan:在这个表上发起的索引扫描的次数
idx_tup_fetch:索引扫描获取的实时行数
n_tup_ins:被插入的行数
n_tup_upd:被更新的行数（包括 HOT 更新的行）
n_tup_del:被删除的行数
n_tup_hot_upd:被更新的 HOT 行数（即不要求独立索引更新的行更新）
n_live_tup:活着的行的估计数量
n_dead_tup:死亡行的估计数量
n_mod_since_analyze:自上次分析此表以来修改的行的估计数量
n_ins_since_vacuum :自上次清空此表以来插入的行的估计数量
last_vacuum:最后一次手动清理这个表(不包括VACUUM FULL)
last_autovacuum:上次这个表被自动清理守护进程清理的时间
last_analyze:上次这个表被手动分析的时间
last_autoanalyze:上次这个表被自动清理守护进程分析的时间
vacuum_count:这个表已被手工清理的次数（不统计VACUUM FULL）
autovacuum_count:这个表已被自动清理守护进程清理的次数
analyze_count:这个表已被手工分析的次数
autoanalyze_count:这个表已被自动清理守护进程分析的次数

vacuum 管理

vacuum分为两种机制

  lazy vacuum 机制 

        默认情况下，PostgreSQL 使用 Lazy VACUUM 机制来自动回收已标记为过期的行并释放未使用的空间。
         Lazy VACUUM 是一种自适应的机制，它使用可见性映射（visibility map，后缀为“_vm”的文件）来跟踪表页面中的可见行，并在需要时自动执行 VACUUM 操作。
         Lazy VACUUM 适用于大多数情况下，因为它可以在后台以延迟和自适应的方式处理垃圾回收，减少对系统性能的影响。

  full vacuum 机制

        Full Vacuum 首先会进行表的扫描，逐行检查表中的数据和元数据。它会检查每一行的标记状态，包括已标记为删除的行和未标记为删除的行。

       然后删除掉标记为删除的数据行，并释放空间。由于删除数据行后，数据块中会有空洞，此时Full Vacuum还是重新整理数据文件，使行数据连续存储并消除空洞。  

示例讲解

create table text (id serial,text varchar);  --创建测试表text

insert into text(text) SELECT chr(cast(TRUNC(33 + RANDOM() * (126 - 33 + 1)) as int)) AS random_character FROM generate_series(1, 1000);  --插入测试表
delete FROM text where id%6=0;  --删除表
create table txt1 (id serial);  --创建测试表txt1

查询表所在路径

 

可用空间标记       

可以看到txt并没对其进行数据操作，所以fsm并不会被创建。FSM文件并不是在创建表文件时立即创建的，而是等到需要时才会创建，也就是执行VACUUM操作时，或者在为了插入行第一次查询FSM文件时才创建。而这个文件的作用是当需要插入新的数据时，为了快速查找到满足要求的数据块，PostgreSQL使用了树型结构组织FSM文件。每一个FSM文件会记录此时数据块可容纳的空间大小。 每一次vacuum 操作时，清理掉了占用空间的dead toup 会重新释放空间。fsm文件也将会重新加载刷新。

可见性映射表文件       

在PostgreSQL中更新、删除行后，数据行并不会马上从数据块中被清理掉，而是需要等VACUUM时清理。为了能加快VACUUM清理的速度并降低对系统I/O性能的影响，PostgreSQL在8.4.1版本之后为每个数据文件加了一个后缀为“_vm”的文件，此文件被称为可见性映射表文件，简称VM文件。VM文件中为每个数据块存储了一个标志位，用来标记数据块中是否存在需要清理的行。有该文件后，做VACUUM扫描此文件时，如果发现VM文件中该数据块上的位表示该数据块没有需要清理的行，VACUUM就可以跳过对这个数据块的扫描，从而加快VACUUM清理的速度。

建议

PostgreSQL的VACUUM命令出于几个原因必须定期处理每一个表：

原因

恢复或重用被已更新或已删除行所占用的磁盘空间。
更新被PostgreSQL查询规划器使用的数据统计信息。
更新可见性映射，它可以加速只用索引的扫描。
保护老旧数据不会由于事务ID回卷或多事务ID回卷而丢失。

注意

autovacuum自动清理守护进程不会发出VACUUM FULL。

例行清理的一般目标是多做标准的VACUUM来避免需要VACUUM FULL。

一个表，整个内容会被周期性删除，考虑用TRUNCATE而不是先用DELETE再用VACUUM。TRUNCATE会立刻移除该表的整个内容，而不需要一次后续的VACUUM或VACUUM FULL来回收现在未被使用的磁盘空间。其缺点是会违背严格的 MVCC 语义。

开发环境中避免短时间频繁对单表进行使用delete。