你真的搞懂visibility map了吗？
xiongcc约 7 分钟1前言
今天下午我在捣鼓数据库的时候，发现了一个有趣的现象，想起之前在①群里有位筒子提过类似的问题：
借此机会，和各位唠唠这个机制。
2现象
对于这个问题，想必各位拍脑袋就能猜到，数据库肯定会有自己的处理方式，本身就由于无法并行清理表饱受诟病了（ 因为 ShareUpdateExclusiveLock 是自斥的），如果每次 vacuum 还要再吭哧吭哧扫描全部的数据块，那一定太 low 了，所以为什么建议使用分区表？就是为了将大表拆分成多个子表，这样就可以逻辑意义上地并行清理了。
那是什么机制呢？从原理上稍微想一下就清晰了，vacuum 要清理对事物不可见的死元组，那假如数据块里不包含死元组自然就不用清理了，没错，正是 visibility map，可见性映射文件。
让我们看个正常情况：
postgres=# create table t1(id int);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,50000000));
INSERT 0 50000000
postgres=# select relallvisible,relpages,reltuples from pg_class where relname = 't1';
relallvisible | relpages | reltuples
---------------+----------+---------------
221239 | 221239 | 5.0000016e+07
(1 row)
relallvisible：在 vm 文件中标识为全部可见的数据块，Number of pages that are marked all-visible in the table's visibility map. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.
可以看到这个值更新了，其实这是 13 的一个新特性，允许插入操作触发 vacuum 动作 👇🏻
2023-03-22 17:10:13.669 CST [16462] LOG: automatic vacuum of table "postgres.public.t1": index scans: 0
pages: 0 removed, 221239 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 50000000 remain, 0 are dead but not yet removable, oldest xmin: 931
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 23.835 MB/s, avg write rate: 24.705 MB/s
buffer usage: 237346 hits, 205233 misses, 212724 dirtied
WAL usage: 221240 records, 8 full page images, 13114665 bytes
system usage: CPU: user: 15.96 s, system: 2.11 s, elapsed: 67.26 s
2023-03-22 17:10:15.515 CST [16462] LOG: automatic analyze of table "postgres.public.t1"
avg read rate: 117.632 MB/s, avg write rate: 0.008 MB/s
buffer usage: 2290 hits, 27810 misses, 2 dirtied
system usage: CPU: user: 0.61 s, system: 0.11 s, elapsed: 1.84 s
13 以前的版本假如是纯插入的行为，可能在大量插入之后，会碰到一个冻结💣，因为不会触发 vacuum ( prevent wraparound )，所以在 13 之前的版本对于大量插入或者 append-only 的表，记得手动定期执行 vacuum。除此之外，还有临时表也要特别注意，之前处理过一个案例，是某位公众号读者提供的
业务运行过程中会频繁创建写入删除大量的unlogged表，在业务运行过程中系统的sys cpu比较高（40%）user cpu比较低（5%），在对应的系统上产生了大量的文件（600w+），业务运行过程中手动触发checkpoint几个小时都无法正常结束，等待事件为CheckpointStart，这个checkpoint无法完成可能跟什么有关系？当业务运行结束后手动checkpoint很快能完成，系统上的文件也很快会回收到10w以内
这个其实和 WAL 的归档类似，归档进程需要遍历所有的 ready 文件并找到最老的文件，临时表也会产生类似大量的文件，所以也要当心，同时大量临时表的创建与消亡会在系统表中留下大量的死元祖，导致系统表膨胀。你以为坑就这些了吗？NONONO，autovacuum 是无法处理临时表的，因此假如对临时表也做了类似的大量 insert ，同样会产生冻结💣。临时表的坑完了吗？Too young，还有很多但我不说。
可以看到 vacuum 了之后，就会生成 vm 文件。注意，对于 vacuum full 和 cluster 是不会生成 vm 文件的，虽然 vacuum full 和 vacuum 的原理类似，假如你发现执行了 vacuum full，表大小依旧无法收缩，那么就要看下 OldestXmin，因为原理是一样的，要保留那些仍然需要的死元祖。
我们可以使用 pg_visibility 插件观察：
postgres=# select * from pg_visibility_map_summary('t1');
all_visible | all_frozen
-------------+------------
221239 | 0
(1 row)postgres=# select * from pg_visibility('t1') limit 5;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
0 | t | f | t
1 | t | f | t
2 | t | f | t
3 | t | f | t
4 | t | f | t
(5 rows)
更新了之后，可见性就会发生改变，但是 pg_class 里面的值并不会实时更新，所以这也是为什么我很早之前写的 index only scan的误区——不要认为执行计划显式的是 index only scan 就真的不会回表了，取决于实际的 Heap Fetchs 是否不为零，此处就不再赘述了，感兴趣的读者翻一下之前的文章。
postgres=# update t1 set id = 99 where id = 1;
UPDATE 1
postgres=# select * from pg_visibility_map_summary('t1');
all_visible | all_frozen
-------------+------------
221237 | 0
(1 row)postgres=# select * from pg_visibility('t1') limit 5;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
0 | f | f | f
1 | t | f | t
2 | t | f | t
3 | t | f | t
4 | t | f | t
(5 rows)
postgres=# select relallvisible,relpages,reltuples from pg_class where relname = 't1'; ---relallvisible未实时变化
relallvisible | relpages | reltuples
---------------+----------+---------------
221239 | 221239 | 5.0000016e+07
(1 row)
因此，有了 vm 文件的加持，九阳神功护体，vacuum / autovacuum 便可以择机跳过某些数据块了，至于 all_frozen 这一列，顾名思义，判断某个数据块里的元组是否全部被冻结，于 9.6 引入。
3反常行为
以上是正常行为，现在让各位看一个反常的行为，一模一样的操作，但是这次 relallvisible 和 pg_visibility 都显式全部不可见。
postgres=# drop table t1;
DROP TABLE
postgres=# create table t1(id int);
CREATE TABLE
postgres=# insert into t1 values(generate_series(1,50000000));
INSERT 0 50000000
postgres=# select relallvisible,relpages,reltuples from pg_class where relname = 't1';
relallvisible | relpages | reltuples
---------------+----------+---------------
0 | 221239 | 4.9999912e+07
(1 row)postgres=# select * from pg_visibility_map_summary('t1');
all_visible | all_frozen
-------------+------------
0 | 0
(1 row)
postgres=# select * from pg_visibility('t1') limit 5;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
0 | f | f | f
1 | f | f | f
2 | f | f | f
3 | f | f | f
4 | f | f | f
(5 rows)
postgres=# select last_vacuum,last_autovacuum from pg_stat_all_tables where relname = 't1';
last_vacuum | last_autovacuum
-------------+-------------------------------
| 2023-03-22 18:03:15.574691+08
(1 row)
难道 autovacuum 出幺蛾子了？手动再给他清理一下试试
postgres=# \timing on
Timing is on.
postgres=# vacuum analyze t1;
VACUUM
Time: 8933.111 ms (00:08.933)
postgres=# select relallvisible,relpages,reltuples from pg_class where relname = 't1';
relallvisible | relpages | reltuples
---------------+----------+---------------
0 | 221239 | 5.0000016e+07
(1 row)Time: 0.784 ms
postgres=# select * from pg_visibility_map_summary('t1');
all_visible | all_frozen
-------------+------------
0 | 0
(1 row)
Time: 11.354 ms
postgres=# select * from pg_visibility('t1') limit 5;
blkno | all_visible | all_frozen | pd_all_visible
-------+-------------+------------+----------------
0 | f | f | f
1 | f | f | f
2 | f | f | f
3 | f | f | f
4 | f | f | f
(5 rows)
Time: 3650.268 ms (00:03.650)
无济于事。所以加速自然也是加速不了的 👇🏻
postgres=# vacuum analyze t1; ---见鬼，居然还变慢了
VACUUM
Time: 10284.382 ms (00:10.284)
除此之外，让我们再看下额外失效点（注意，至此我只做了插入操作）
postgres=# create index on t1(id);
CREATE INDEX
postgres=# analyze t1;
ANALYZE
postgres=# select count(*) from t1;
count
----------
50000000
(1 row)postgres=# explain select id from t1 where id = 99;
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using t1_id_idx on t1 (cost=0.56..8.58 rows=1 width=4)
Index Cond: (id = 99)
(2 rows)
postgres=# explain analyze select id from t1 where id = 99;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
------
Index Only Scan using t1_id_idx on t1 (cost=0.56..8.58 rows=1 width=4) (actual time=4.548..4.551 rows=1 loo
ps=1)
Index Cond: (id = 99)
Heap Fetches: 1 ---👈🏻回表了
Planning Time: 0.163 ms
Execution Time: 4.612 ms
(5 rows)
想必各位也看到了，回表了，虽然我们站在上帝视角，元组是全部可见的，PostgreSQL 为什么还是要回表去确认元组的可见性呢？
...
4小结
卖个关子，此期就先到这里，各位读者趁着我揭开谜底之前思考一下是什么行为导致的，自己思考明白比直接看我文章理解地更加透彻。