PostgreSQL中表的年龄与Vacuum的实验探索：Vacuum有大用

前言

对于PG中的数据库表，随着系统当前事务的ID的快速增长，表的年龄也会不断增长。年龄在达到2^^31之前，就会要主动执行freeze或被动freeze, 以维持数据可见性。

以下是PG14.8当中与vacuum相关的配置的默认值：

mydb=# select name, setting from pg_settings where name ~ 'vacuum';
                 name                  |  setting   
---------------------------------------+------------
 autovacuum                            | on
 autovacuum_analyze_scale_factor       | 0.1
 autovacuum_analyze_threshold          | 50
 autovacuum_freeze_max_age             | 200000000
 autovacuum_max_workers                | 3
 autovacuum_multixact_freeze_max_age   | 400000000
 autovacuum_naptime                    | 60
 autovacuum_vacuum_cost_delay          | 2
 autovacuum_vacuum_cost_limit          | -1
 autovacuum_vacuum_insert_scale_factor | 0.2
 autovacuum_vacuum_insert_threshold    | 1000
 autovacuum_vacuum_scale_factor        | 0.2
 autovacuum_vacuum_threshold           | 50
 autovacuum_work_mem                   | -1
 log_autovacuum_min_duration           | -1
 vacuum_cost_delay                     | 0
 vacuum_cost_limit                     | 200
 vacuum_cost_page_dirty                | 20
 vacuum_cost_page_hit                  | 1
 vacuum_cost_page_miss                 | 2
 vacuum_defer_cleanup_age              | 0
 vacuum_failsafe_age                   | 1600000000
 vacuum_freeze_min_age                 | 50000000
 vacuum_freeze_table_age               | 150000000
 vacuum_multixact_failsafe_age         | 1600000000
 vacuum_multixact_freeze_min_age       | 5000000
 vacuum_multixact_freeze_table_age     | 150000000
(27 rows)

文中的实作部分基本就围绕其中一些参数来进行实验验证。验证的目的，是为了给vacuum, autovacuum的维护性操作，提供参考依据。生产环境当中，相当大一部分依赖于autovacuum，但是也有一部分要依赖于vacuum。因为这个freeze(冻结) 操作的存在。

实作

1、建表并初始化

mydb=# create table t(id int);
CREATE TABLE
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1088 |         1088 |   1
(1 row)

mydb=# insert into t values(1);
INSERT 0 1
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1088 |         1088 |   2
(1 row)

mydb=# select tableoid, xmin, xmax, * from t;
 tableoid | xmin | xmax | id 
----------+------+------+----
    16413 | 1089 |    0 |  1
(1 row)

我们也看到，刚建表的时候（没有任何数据），系统表pg_class里头会记录一行，并以xmin最小事务ID作为冻结ID（relfrozenxid）“记录在案”。这里我们可以看到它就是从1088开始的。不妨，把它当作表t的“出生日期”。从这一刻起，它就开始有年龄了。这个xmin是个神奇的玩意儿，它是整个PG实例级别的。就是说，我们如果在同一实例下，另一个库：postgres下边，再建另一张表t2，它的xmin和relfrozenxid一定是从最新的xmin或者txid: 1092开始记起。

\c postgres
postgres=# begin;
BEGIN
postgres=*# select pg_current_xact_id();
 pg_current_xact_id 
--------------------
               1092
(1 row)

postgres=*# create table t2(id int);
CREATE TABLE
postgres=*# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t2';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16419 | t2      | 1092 |         1092 |   0
(1 row)

postgres=*# rollback;
ROLLBACK

2、参数说明

我们先重点关注vacuum_freeze_table_age和vacuum_freeze_min_age：

完整的参数说明，参考：

postgresqlco.nf：vacuum_freeze_min_age/^[1]

pg14 document:routine-vacuuming.html^[2]

vacuum_freeze_table_age: Age at which VACUUM should scan whole table to freeze tuples. 
    当表的pg_class.relfrozenxid域达到该设置指定的年龄时，VACUUM会执行一次激进的扫描。激进的扫描与常规VACUUM的不同在于它会访问每一个可能包含未冻结 XID 或者 MXID 的页面，而不只是那些可能包含死元组的页面。
vacuum_freeze_min_age: Minimum age at which VACUUM should freeze a table row． 执行VACUUM时，是否冻结表行的最小年龄。

3、调整freeze相关参数

为了尽快看到效果，我们可以把一些参数值调的小一些。

postgres=# alter system set vacuum_freeze_min_age=5;
ALTER SYSTEM
postgres=# alter system set vacuum_freeze_table_age=10;
ALTER SYSTEM
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".

我把freeze_min调成5， 把freeze_table_age调成10，就是想让表t尽快进入能执行freeze触发的状态。当然这个需要reload才能生效：

mydb=# select name, setting from pg_settings where name in ('vacuum_freeze_min_age', 'vacuum_freeze_table_age');
          name           |  setting  
-------------------------+-----------
 vacuum_freeze_min_age   | 50000000
 vacuum_freeze_table_age | 150000000
(2 rows)

mydb=# select pg_reload_conf(); -- reload强制生效
 pg_reload_conf 
----------------
 t
(1 row)

mydb=# select name, setting from pg_settings where name in ('vacuum_freeze_min_age', 'vacuum_freeze_table_age');
          name           | setting 
-------------------------+---------
 vacuum_freeze_min_age   | 5
 vacuum_freeze_table_age | 10
(2 rows)

接着，我们看看表t的行为，为让系统实验尽量干净，我们可以将表t进行truncate，并观察一下结果先：

mydb=# truncate t;
TRUNCATE TABLE
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1104 |   1
(1 row)

当我们truncate以后，oid保持不变，但是它的“出生日期”（relfrozenxid）变了。

插入3条记录并vacuum:

mydb=# insert into t values(1);
INSERT 0 1
mydb=# insert into t values(1);
INSERT 0 1
mydb=# insert into t values(1);
INSERT 0 1
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1104 |   4
(1 row)

mydb=# vacuum (verbose) t;
INFO:  vacuuming "public.t"
INFO:  table "t": found 0 removable, 3 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1108
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1104 |   4
(1 row)

我们看到三条记录插入以后（有三个事务的增长），年龄变为4，不到触发条件。因而年龄为4。

再插入一条：

mydb=# insert into t values(2);
INSERT 0 1
mydb=# vacuum (verbose) t;
INFO:  vacuuming "public.t"
INFO:  table "t": found 0 removable, 4 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1109
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1104 |   5
(1 row)

age为5。relfrozenxid为1104。未变。

再插入一或多条试试:

mydb=# insert into t values(2);
INSERT 0 1
mydb=# vacuum (verbose) t;
INFO:  vacuuming "public.t"
INFO:  table "t": found 0 removable, 5 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1110
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1105 |   5
(1 row)

这个时候，我们发现，oldest xmin: 1110, 但是呢，出生日期改了，改成1105了。年龄还是5。

不放心的放，我们再插入几条触发多个事务再看看：

mydb=# insert into t values(2);
INSERT 0 1
mydb=# insert into t values(2);
INSERT 0 1
mydb=# insert into t values(2);
INSERT 0 1
mydb=# insert into t values(2);
INSERT 0 1
mydb=# insert into t values(2);
INSERT 0 1
mydb=# vacuum (verbose) t;
INFO:  aggressively vacuuming "public.t"
INFO:  table "t": found 0 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1115
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1110 |   5
(1 row)

我们发现干什么现象，通过普通的vacuum操作，系统能将目标表的age维持在5：vacuum_freeze_min_age。

4、进一步触发

上一步实验，我们是因为提前知道有多笔事务，可以触发vacuum_freeze_min_age，如果不知道呢，或者说我们就没有在那一个阶段去执行vacuum。那么还有一个阶段去做，我们接着模拟多个事务，让这个差值拉大一些。

mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# insert into t values(3);
INSERT 0 1
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1110 |  15
(1 row)

此是age变为15了。我们做一次vacuum操作看看：

mydb=# vacuum (verbose) t;
INFO:  aggressively vacuuming "public.t"
INFO:  table "t": found 0 removable, 20 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1125
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1120 |   5
(1 row)

这个时候，依然有机会触发freeze，并且将年龄打回到5这个值。

5、我们调整一下参数vacuum_freeze_min_age：

mydb=# alter system set vacuum_freeze_min_age=20;
ALTER SYSTEM
mydb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

mydb=# select name, setting from pg_settings where name in ('vacuum_freeze_min_age', 'vacuum_freeze_table_age');
          name           | setting 
-------------------------+---------
 vacuum_freeze_min_age   | 20
 vacuum_freeze_table_age | 10
(2 rows)

mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1120 |   5
(1 row)

再次插入6条左右数据：

mydb=# insert into t values(4);
INSERT 0 1
mydb=# insert into t values(4);
INSERT 0 1
mydb=# insert into t values(4);
INSERT 0 1
mydb=# insert into t values(4);
INSERT 0 1
mydb=# insert into t values(4);
INSERT 0 1
mydb=# insert into t values(4);
INSERT 0 1
mydb=# insert into t values(4);
INSERT 0 1
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1120 |  12
(1 row)

mydb=# vacuum (verbose) t;
INFO:  aggressively vacuuming "public.t"
INFO:  table "t": found 0 removable, 27 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1132
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1120 |  12
(1 row)

虽然到了table_age:10, 但是并未修改冻结ID。

好吧，再补几条：

mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# insert into t values(5);
INSERT 0 1
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1120 |  22
(1 row)

mydb=# vacuum (verbose) t;
INFO:  aggressively vacuuming "public.t"
INFO:  table "t": found 0 removable, 37 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1142
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
mydb=# select oid, relname, xmin, relfrozenxid, age(relfrozenxid) from pg_class where relname='t';
  oid  | relname | xmin | relfrozenxid | age 
-------+---------+------+--------------+-----
 16413 | t       | 1104 |         1122 |  20
(1 row)

我们能看到，最后维持的年龄就是20： vacuum_freeze_min_age

总结

可能有人会说，上边的实验很无聊啊。是的，有点儿。但是它可以为我们生产环境当中的做法提供依据。

首先，我们会注意到，autovacuum默认是ON，生产环境里头，它大多禁止disable，也不假。但是对于所有的表而言，并不是每个表每次都能被触发。它有触发条件的，这个您懂的。有些空表，可能自出生第一天起，就没有被autovacuum过，那么，它就会一次次变老，直至最后被迫freeze（要到相当相当晚的时候)

其次，基于上边的实验，vacuum是一个弥补性的大功臣。虽然有时候vacuum可能对于空间回收，啥用也没有。比如，上边的实验，全是insert操作（我故意为之），它压根就不是回收空间，只是想借机看能不能改变年龄。这个在很多时候能救系统的命。

结合这两方面，在系统监控方面，我们有必要统计出，比如最近7天或者多少天从未执行过AUTOVACUUM的表（这个SQL网上也能查到），比如这样的：

SELECT 'VACUUM  (VERBOSE)  ' || schemaname || '.' || relname ||';'
FROM pg_stat_user_tables
WHERE (now() - last_autovacuum > '7 days'::interval
OR now() - last_vacuum >'7 days'::interval )
OR (last_autovacuum IS NULL AND last_vacuum IS NULL );

-- 实际情况，要根据是否要求加入系统表得行调整

然后强行触发一下VACUUM即可，可以用schedule task，比如一周执行一次。最终的目的是要将系统中的所有的表的age给尽早降下来。如果是本地部署的PG，因为DBA们拥有至高无上的权限，所以，所有的库下的所有表，他都可以控制。两者结合，可以尽乎完美的完成任务。

但在云环境当中，哪怕是牛气冲天的AWS上的RDS，他们权限控制的很死板，每个提供给客户的实例当中，有一个他们自己专属的库：rdsadmin，美其名曰：用于他们的内部维护。但是它们的autovacuum处理的并不完善，并不是所有的表都vacuum，于是经常出现一个怪现象是，rdsadmin这个库把实例中的age给拖垮了。业务库做的再好，比如降到50000000, rdsadmin依然可以跑到近乎2亿的age。这个在本地部署的PG上边基本是不会了。

参考资料