PostgreSQL 自动vacuum(垃圾回收)与手动vacuum的参数详解

digoal 2019-06-17

5430

作者

digoal

日期

2019-06-17

背景

PostgreSQL 11以及以前的版本，默认使用的表存储是HEAP存储，用于并发多版本控制的所有版本都是存储在HEAP内部，而不是独立的UNDO文件中。所以如果表有更新、删除时，实际上需要VACUUM动作来回收垃圾版本。

除了垃圾回收以外，由于PG目前XID（事务号）使用的是32位长度，所以需要轮询使用，事务号被记录在TUPLE的头信息中，是tuple可见性判断的关键。既然XID是轮询使用，为了可见性判断的正确性，需要在TUPLE的年龄超过20亿之前对TUPLE头部的XID进行FREEZE操作，保证可见性（FREEZE状态的TUPLE对所有事务都是可见的，不需要依赖XID的比较判断）。

垃圾回收和FREEZE、包括ANALYZE这三种操作(analyze是收集统计信息)，都是由vacuum命令来完成的。

vacuum命令又分为“自动”和“手动”调用。

一、自动触发vacuum、analyze、freeze

目前PG的自动垃圾回收（自动vacuum）的调度做得还是比较好的，例如有一个autovacuum launcher进程在间歇性(autovacuum_naptime)的监测PG集群中的所有database，检查database中是否有需要执行(vacuum, freeze, analyze)的table。

可配置自动调度参数包括：

https://www.postgresql.org/docs/devel/runtime-config-autovacuum.html

track_counts (boolean)

必须开启track_counts，因为autovacuum需要依赖计数器，否则无法知道dead tuples的数量以判断是否需要触发vacuum,analyze。

Enables collection of statistics on database activity.

This parameter is on by default, because the autovacuum daemon needs the collected information. Only superusers can change this setting.

autovacuum (boolean)

Controls whether the server should run the autovacuum launcher daemon.
This is on by default; however, track_counts must also be enabled for autovacuum to work.
This parameter can only be set in the postgresql.conf file or on the server command line;
however, autovacuuming can be disabled for individual tables by changing table storage parameters.

Note that even when this parameter is disabled, the system will launch autovacuum processes if necessary to prevent transaction ID wraparound.
See Section 24.1.5 for more information.

log_autovacuum_min_duration (integer)

向日志打印autovacuum的统计信息（以及资源消耗），大于阈值，输出这次autovacuum触发的事件的统计信息。

Causes each action executed by autovacuum to be logged if it ran for at least the specified number of milliseconds.
Setting this to zero logs all autovacuum actions.
-1 (the default) disables logging autovacuum actions.

For example, if you set this to 250ms then all automatic vacuums and analyzes that run 250ms or longer will be logged.

In addition, when this parameter is set to any value other than -1, a message will be logged if an autovacuum action is skipped due to a conflicting lock or a concurrently dropped relation. Enabling this parameter can be helpful in tracking autovacuum activity. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_max_workers (integer)

autovacuum同时最多允许发起多少个vacuum工作进程(autovacuum work process)。设置太大的话容易引起资源使用过多。

Specifies the maximum number of autovacuum processes (other than the autovacuum launcher) that may be running at any one time. The default is three. This parameter can only be set at server start.

autovacuum_naptime (integer)

autovacuum launcher监测进程多久轮询完所有数据库，轮询的目的是了解数据库中有哪些对象需要vacuum、freeze、analyze。例如1分钟，PG实例中有10个数据库，表示每分钟要轮询完10个数据库。

Specifies the minimum delay between autovacuum runs on any given database. In each round the daemon examines the database and issues VACUUM and ANALYZE commands as needed for tables in that database. The delay is measured in seconds, and the default is one minute (1min). This parameter can only be set in the postgresql.conf file or on the server command line.

autovacuum_vacuum_threshold (integer)

当表的垃圾版本(dead tuples)超过 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor*reltuples ，触发vacuum。

Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_analyze_threshold (integer)

当上次analyze以来表的新增记录超过 autovacuum_analyze_threshold + autovacuum_analyze_scale_factor*reltuples ，触发analyze。

Specifies the minimum number of inserted, updated or deleted tuples needed to trigger an ANALYZE in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_vacuum_scale_factor (floating point)

Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_analyze_scale_factor (floating point)

Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE. The default is 0.1 (10% of table size). This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_freeze_max_age (integer)

当表的年龄超过autovacuum_freeze_max_age，即使关闭autovacuum，数据库实例依旧会强行触发vacuum freeze。

Specifies the maximum age (in transactions) that a table's pg_class.relfrozenxid field can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table. Note that the system will launch autovacuum processes to prevent wraparound even when autovacuum is otherwise disabled.

Vacuum also allows removal of old files from the pg_xact subdirectory, which is why the default is a relatively low 200 million transactions. This parameter can only be set at server start, but the setting can be reduced for individual tables by changing table storage parameters. For more information see Section 24.1.5.

autovacuum_multixact_freeze_max_age (integer)

当表的multixacts年龄超过autovacuum_freeze_max_age，即使关闭autovacuum，数据库实例依旧会强行触发vacuum freeze。

Specifies the maximum age (in multixacts) that a table's pg_class.relminmxid field can attain before a VACUUM operation is forced to prevent multixact ID wraparound within the table. Note that the system will launch autovacuum processes to prevent wraparound even when autovacuum is otherwise disabled.

Vacuuming multixacts also allows removal of old files from the pg_multixact/members and pg_multixact/offsets subdirectories, which is why the default is a relatively low 400 million multixacts. This parameter can only be set at server start, but the setting can be reduced for individual tables by changing table storage parameters. For more information see Section 24.1.5.1.

autovacuum_vacuum_cost_delay (floating point)

autovacuum触发的vacuum、freeze、analyze的平滑化调度，但是注意：设置过大，会导致AUTOVACUUM launcher触发的vacuum耗时过长。所以也不是好事，特别是大表，耗时会非常长，可能导致膨胀等问题。

Specifies the cost delay value that will be used in automatic VACUUM operations. If -1 is specified, the regular vacuum_cost_delay value will be used. The default value is 2 milliseconds. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.

autovacuum_vacuum_cost_limit (integer)

Specifies the cost limit value that will be used in automatic VACUUM operations.

If -1 is specified (which is the default), the regular vacuum_cost_limit value will be used.

Note that the value is distributed proportionally among the running autovacuum workers, if there is more than one, so that the sum of the limits for each worker does not exceed the value of this variable.

This parameter can only be set in the postgresql.conf file or on the server command line;
but the setting can be overridden for individual tables by changing table storage parameters.

二、手动执行vacuum、analyze、freeze

https://www.postgresql.org/docs/devel/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

vacuum_cost_delay (floating point)

手工执行的vacuum、freeze、analyze的平滑化调度，但是注意：设置过大，会导致AUTOVACUUM launcher触发的vacuum耗时过长。所以也不是好事，特别是大表，耗时会非常长，可能导致膨胀等问题。

The length of time, in milliseconds, that the process will sleep when the cost limit has been exceeded. The default value is zero, which disables the cost-based vacuum delay feature. Positive values enable cost-based vacuuming.

When using cost-based vacuuming, appropriate values for vacuum_cost_delay are usually quite small, perhaps less than 1 millisecond. While vacuum_cost_delay can be set to fractional-millisecond values, such delays may not be measured accurately on older platforms. On such platforms, increasing VACUUM's throttled resource consumption above what you get at 1ms will require changing the other vacuum cost parameters. You should, nonetheless, keep vacuum_cost_delay as small as your platform will consistently measure; large delays are not helpful.

vacuum_cost_page_hit (integer)

平滑化调度相关的代价计算方法。当vacuum的这个page已经在buffer时，它的代价是多少？

The estimated cost for vacuuming a buffer found in the shared buffer cache. It represents the cost to lock the buffer pool, lookup the shared hash table and scan the content of the page. The default value is one.

vacuum_cost_page_miss (integer)

平滑化调度相关的代价计算方法。当vacuum的这个page未在buffer时，它的代价是多少？

The estimated cost for vacuuming a buffer that has to be read from disk. This represents the effort to lock the buffer pool, lookup the shared hash table, read the desired block in from the disk and scan its content. The default value is 10.

vacuum_cost_page_dirty (integer)

平滑化调度相关的代价计算方法。当vacuum的这个page之前不是个脏页，由于VACUUM对它的内容造成了改变时（一个clean page vacuum内容后变成了dirty page），它的代价是多少？

The estimated cost charged when vacuum modifies a block that was previously clean. It represents the extra I/O required to flush the dirty block out to disk again. The default value is 20.

vacuum_cost_limit (integer)

手工执行的vacuum、freeze、analyze的平滑化调度，但是设置过大，会导致vacuum耗时过长。所以也不是好事，特别是大表，耗时会非常长，可能导致膨胀等问题。

The accumulated cost that will cause the vacuuming process to sleep. The default value is 200.

Note

There are certain operations that hold critical locks and should therefore complete as quickly as possible. Cost-based vacuum delays do not occur during such operations. Therefore it is possible that the cost accumulates far higher than the specified limit. To avoid uselessly long delays in such cases, the actual delay is calculated as vacuum_cost_delay * accumulated_balance / vacuum_cost_limit with a maximum of vacuum_cost_delay * 4.

三、自动触发、手动执行的vacuum,freeze,analyze，行为一样吗？

https://www.postgresql.org/docs/devel/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT

答案：一样。

不管是自动还是手动vacuum,freeze,analyze，它们在执行的过程中的行为同样都受到如下参数的影响。

vacuum_freeze_table_age (integer)

当表的pg_class.relfrozenxid年龄大于等于vacuum_freeze_table_age，VACUUM会扫描全表，并执行freeze。（内核优化：跳过vm文件中已经标注为FREEZE的PAGE）

VACUUM performs an aggressive scan if the table's pg_class.relfrozenxid field has reached the age specified by this setting. An aggressive scan differs from a regular VACUUM in that it visits every page that might contain unfrozen XIDs or MXIDs, not just those that might contain dead tuples. The default is 150 million transactions. Although users can set this value anywhere from zero to two billions, VACUUM will silently limit the effective value to 95% of autovacuum_freeze_max_age, so that a periodical manual VACUUM has a chance to run before an anti-wraparound autovacuum is launched for the table. For more information see Section 24.1.5.

vacuum_freeze_min_age (integer)

freeze时，哪些tuple会被置为frozen状态呢？年龄大于或等于vacuum_freeze_min_age的tuple。

在自动触发(由于表的年龄大于autovacuum_freeze_max_age) 或手动执行vacuum freeze，或autovacuum触发、手工执行VACUUM时表的年龄大于等于vacuum_freeze_table_age时。以上几种情况的vacuum都将会进入FREEZE模式(扫描全表，并执行freeze)。

有效记录的年龄大于vacuum_freeze_min_age的，将该有效记录设置为freeze状态。

普通vacuum(不带freeze的)，并且表的年龄小于vacuum_freeze_table_age，不会执行FREEZE，所以不受这个参数影响。

Specifies the cutoff age (in transactions) that VACUUM should use to decide whether to freeze row versions while scanning a table. The default is 50 million transactions. Although users can set this value anywhere from zero to one billion, VACUUM will silently limit the effective value to half the value of autovacuum_freeze_max_age, so that there is not an unreasonably short time between forced autovacuums. For more information see Section 24.1.5.

vacuum_multixact_freeze_table_age (integer)

当表的pg_class.relminmxid年龄大于vacuum_multixact_freeze_table_age，VACUUM会扫描全表，并执行freeze。（内核优化：跳过vm文件中已经标注为FREEZE的PAGE）

VACUUM performs an aggressive scan if the table's pg_class.relminmxid field has reached the age specified by this setting. An aggressive scan differs from a regular VACUUM in that it visits every page that might contain unfrozen XIDs or MXIDs, not just those that might contain dead tuples. The default is 150 million multixacts. Although users can set this value anywhere from zero to two billions, VACUUM will silently limit the effective value to 95% of autovacuum_multixact_freeze_max_age, so that a periodical manual VACUUM has a chance to run before an anti-wraparound is launched for the table. For more information see Section 24.1.5.1.

vacuum_multixact_freeze_min_age (integer)

Specifies the cutoff age (in multixacts) that VACUUM should use to decide whether to replace multixact IDs with a newer transaction ID or multixact ID while scanning a table. The default is 5 million multixacts. Although users can set this value anywhere from zero to one billion, VACUUM will silently limit the effective value to half the value of autovacuum_multixact_freeze_max_age, so that there is not an unreasonably short time between forced autovacuums. For more information see Section 24.1.5.1.

vacuum_cleanup_index_scale_factor (floating point)

《PostgreSQL 12 preview - vacuum 新增开关: index_cleanup , 是否同时回收索引》

《PostgreSQL 11 内核优化 - 降低vacuum cleanup阶段index scan概率 ( vacuum_cleanup_index_scale_factor , skip index vacuum cleanup stage)》

Specifies the fraction of the total number of heap tuples counted in the previous statistics collection that can be inserted without incurring an index scan at the VACUUM cleanup stage. This setting currently applies to B-tree indexes only.

If no tuples were deleted from the heap, B-tree indexes are still scanned at the VACUUM cleanup stage when at least one of the following conditions is met: the index statistics are stale, or the index contains deleted pages that can be recycled during cleanup. Index statistics are considered to be stale if the number of newly inserted tuples exceeds the vacuum_cleanup_index_scale_factor fraction of the total number of heap tuples detected by the previous statistics collection. The total number of heap tuples is stored in the index meta-page. Note that the meta-page does not include this data until VACUUM finds no dead tuples, so B-tree index scan at the cleanup stage can only be skipped if the second and subsequent VACUUM cycles detect no dead tuples.

The value can range from 0 to 10000000000. When vacuum_cleanup_index_scale_factor is set to 0, index scans are never skipped during VACUUM cleanup. The default value is 0.1.

四、表级vacuum、freeze、analyze相关参数

https://www.postgresql.org/docs/devel/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

自动垃圾回收时，通过表的如下参数，控制这个表的自动垃圾回收的行为。参数意义同上介绍一致。

autovacuum_enabled, toast.autovacuum_enabled (boolean)

Enables or disables the autovacuum daemon for a particular table. If true, the autovacuum daemon will perform automatic VACUUM and/or ANALYZE operations on this table following the rules discussed in Section 24.1.6. If false, this table will not be autovacuumed, except to prevent transaction ID wraparound. See Section 24.1.5 for more about wraparound prevention. Note that the autovacuum daemon does not run at all (except to prevent transaction ID wraparound) if the autovacuum parameter is false; setting individual tables' storage parameters does not override that. Therefore there is seldom much point in explicitly setting this storage parameter to true, only to false.

vacuum_index_cleanup (boolean)

Enables or disables index cleanup when VACUUM is run on this table. The default value is true. Disabling index cleanup can speed up VACUUM very significantly, but may also lead to severely bloated indexes if table modifications are frequent. The INDEX_CLEANUP parameter of VACUUM, if specified, overrides the value of this option.

vacuum_truncate, toast.vacuum_truncate (boolean)

Enables or disables vacuum to try to truncate off any empty pages at the end of this table. The default value is true. If true, VACUUM and autovacuum do the truncation and the disk space for the truncated pages is returned to the operating system. Note that the truncation requires ACCESS EXCLUSIVE lock on the table. The TRUNCATE parameter of VACUUM, if specified, overrides the value of this option.

autovacuum_vacuum_threshold, toast.autovacuum_vacuum_threshold (integer)

Per-table value for autovacuum_vacuum_threshold parameter.

autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor (float4)

Per-table value for autovacuum_vacuum_scale_factor parameter.

autovacuum_analyze_threshold (integer)

Per-table value for autovacuum_analyze_threshold parameter.

autovacuum_analyze_scale_factor (float4)

Per-table value for autovacuum_analyze_scale_factor parameter.

autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (floating point)

Per-table value for autovacuum_vacuum_cost_delay parameter.

autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit (integer)

Per-table value for autovacuum_vacuum_cost_limit parameter.

autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)

Per-table value for vacuum_freeze_min_age parameter. Note that autovacuum will ignore per-table autovacuum_freeze_min_age parameters that are larger than half the system-wide autovacuum_freeze_max_age setting.

autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)

Per-table value for autovacuum_freeze_max_age parameter. Note that autovacuum will ignore per-table autovacuum_freeze_max_age parameters that are larger than the system-wide setting (it can only be set smaller).

autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age (integer)

Per-table value for vacuum_freeze_table_age parameter.

autovacuum_multixact_freeze_min_age, toast.autovacuum_multixact_freeze_min_age (integer)

Per-table value for vacuum_multixact_freeze_min_age parameter. Note that autovacuum will ignore per-table autovacuum_multixact_freeze_min_age parameters that are larger than half the system-wide autovacuum_multixact_freeze_max_age setting.

autovacuum_multixact_freeze_max_age, toast.autovacuum_multixact_freeze_max_age (integer)

Per-table value for autovacuum_multixact_freeze_max_age parameter. Note that autovacuum will ignore per-table autovacuum_multixact_freeze_max_age parameters that are larger than the system-wide setting (it can only be set smaller).

autovacuum_multixact_freeze_table_age, toast.autovacuum_multixact_freeze_table_age (integer)

Per-table value for vacuum_multixact_freeze_table_age parameter.

log_autovacuum_min_duration, toast.log_autovacuum_min_duration (integer)

Per-table value for log_autovacuum_min_duration parameter.

例子

1 年龄大于vacuum_freeze_table_age的表，任何时候对这种表通过SQL执行vacuum、或autovacuum触发的vacuum都自动转换为vacuum freeze

将这两个参数设置为0，任何时候执行或自动触发vacuum，都会转换为FREEZE。

```
vacuum_freeze_min_age=0

vacuum_freeze_table_age=0
```

```
create table test1 (id int, info text);
insert into test1 select generate_series(1,1000000);
delete from test1 where id<1000000;

select * from pg_stat_all_tables where relname ='test1';
从pg_stat_all_tables.last_autovacuum字段看到这个表已经发生了autovacuum
```

剩余的这条记录的BLOCK ID如下4424。通过pageinspect插件的函数查看这个数据块内部的记录，掩码信息。

create extension pageinspect;

```
postgres=# select ctid from test1;
ctid

(4424,176)
(1 row)

SELECT t_infomask2,t_infomask FROM heap_page_items(get_raw_page('test1', 4424));
t_infomask2 | t_infomask
-------------+------------
2 | 2817
```

解析2817 t_infomask，包含了如下掩码(HEAP_XMIN_FROZEN)

src/include/access/htup_details.h

```
/
* information stored in t_infomask:
/

define HEAP_HASNULL 0x0001 / has null attribute(s) /

define HEAP_HASVARWIDTH 0x0002 / has variable-width attribute(s) /

define HEAP_HASEXTERNAL 0x0004 / has external stored attribute(s) /

define HEAP_HASOID_OLD 0x0008 / has an object-id field /

define HEAP_XMAX_KEYSHR_LOCK 0x0010 / xmax is a key-shared locker /

define HEAP_COMBOCID 0x0020 / t_cid is a combo cid /

define HEAP_XMAX_EXCL_LOCK 0x0040 / xmax is exclusive locker /

define HEAP_XMAX_LOCK_ONLY 0x0080 / xmax, if valid, is only a locker /

/ xmax is a shared locker /

define HEAP_XMAX_SHR_LOCK (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)

define HEAP_LOCK_MASK (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \

                                             HEAP_XMAX_KEYSHR_LOCK)
复制

define HEAP_XMIN_COMMITTED 0x0100 / t_xmin committed /

define HEAP_XMIN_INVALID 0x0200 / t_xmin invalid/aborted /

define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)

define HEAP_XMAX_COMMITTED 0x0400 / t_xmax committed /

define HEAP_XMAX_INVALID 0x0800 / t_xmax invalid/aborted /

define HEAP_XMAX_IS_MULTI 0x1000 / t_xmax is a MultiXactId /

define HEAP_UPDATED 0x2000 / this is UPDATEd version of row /

define HEAP_MOVED_OFF 0x4000 /* moved to another place by pre-9.0

                                                                             * VACUUM FULL; kept for binary      
                                                                             * upgrade support */
复制

define HEAP_MOVED_IN 0x8000 /* moved from another place by pre-9.0

                                                                             * VACUUM FULL; kept for binary      
                                                                             * upgrade support */
复制

define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)

define HEAP_XACT_MASK 0xFFF0 / visibility-related bits /

```

2 当表的年龄大于vacuum_freeze_table_age时，手工执行或自动触发的vacuum会自动转换为vacuum freeze。

vacuum freeze的动作：当TUPLE的年龄大于vacuum_freeze_min_age，设置TUPLE头信息，标记为frozen。

把vacuum_freeze_table_age值设大，确保vacuum时，不会转换为freeze

vacuum_freeze_min_age=5000000 vacuum_freeze_table_age=250000000

```
create table test1 (id int, info text);
insert into test1 select generate_series(1,1000000);
delete from test1 where id<1000000;

last_autovacuum字段看到已经发生了autovacuum
```

查看剩余记录的数据块位置。查看它的t_infomask掩码

```
postgres=# select ctid from test1;
ctid

(4424,176)
(1 row)

SELECT t_infomask2,t_infomask FROM heap_page_items(get_raw_page('test1', 4424));
t_infomask2 | t_infomask
-------------+------------
2 | 2305
```

分析掩码，未包含HEAP_XMIN_FROZEN，也就是说，没有转换为FREEZE。

哪些记录能被垃圾回收

详见：

《PostgreSQL物理"备库"的哪些操作或配置，可能影响"主库"的性能、垃圾回收、IO波动》

《PostgreSQL垃圾回收代码分析 - why postgresql cann't reclaim tuple is HEAPTUPLE_RECENTLY_DEAD》

当存在长事务，备库开启了feedback（并且有长事务）时，vacuum无法回收垃圾无法FREEZE，那么可能使得VACUUM做无用功，一直发起vacuum但是一直在扫描，耗费CPU判断TUPLE时候可以回收，发现不可以回收，所以扫完年龄和垃圾比例都没有下架，那么AUTOVACUUM LAUNCHER又会触发下一轮的vacuum，循环往复，知道长事务消失，可以回收之后才能解决。

小结

autovacuum launcher

autovacuum launcher是判断数据库是否需要执行VACUUM或ANALYZE的后台监测进程，频率为autovacuum_naptime：

1、如果开启了autovacuum，当垃圾记录数大于 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor*reltuples ，autovacuum launcher触发普通的vacuum。

当表的年龄大于vacuum_freeze_table_age，则自动转换成vacuum freeze。

2、如果开启了autovacuum，当新增记录数大于autovacuum_analyze_threshold + autovacuum_analyze_scale_factor*reltuples，autovacuum launcher触发analyze。

3、即使没有开启autovacuum，当表的年龄大于autovacuum_freeze_max_age，也会强制触发vacuum freeze。

手功执行vacuum

```
vacuum

vacuum analyze

analyze

vacuum freeze
```

1、其中，手工执行VACUUM时，并且表的年龄大于vacuum_freeze_table_age，则普通vacuum即使没有带freeze选项，也会自动转换成vacuum freeze。

2、手工执行vacuum freeze时，或者自动触发vacuum freeze时，哪些tuple需要freeze，取决于如下：当tuple的年龄大于vacuum_freeze_min_age，则该TUPLE设置为frozen。

3、一旦VACUUM带了freeze选项，就需要扫描全表（当然会跳过VM中已经标记为FROZEN的BLOCK，内核的优化手段）。

其他:

哪些参数可能导致膨胀？除了关闭垃圾回收，delay大（解决办法不要DELAY，或者LIMIT设大，DELAY设小），还有standby feedback(解决办法关闭), 长事务(解决方法，快照过旧，idle in transaction session timeout), 主动延迟垃圾回收(解决办法关闭)，大表（vacuum目前单表不能并行，解决办法分区），autovacuum work mem太小（如果HOLD不下所有DEAD TUPLE的CTID则会导致INDEX需要多次扫描，解决办法设大），唤醒频率太低（解决办法设小naptime，但是不建议太小），autovacuum worker太少（解决办法加大）.