PostgreSQL 12: VACUUM 新增 INDEX_CLEANUP 选项控制是否回收索引

根据 PostgreSQL 的 MVCC 机制,表的数据被更新或删除后,空间并不会释放给操作系统也不能立即重用,执行 VACUUM 操作后,Dead Tuples 的空间才能被清除回收重用(空间依然不释放给操作系统)。

11 版本之前, VACUUM 操作会对表和索引进行垃圾回收,某些场景下从运维角度可能只希望表被 VACUUM , 而不希望索引被 VACUUM,12 版本的 VACUUM 命令新增 INDEX_CLEANUP 选项,可以控制是否对表上的索引进行 VACUUM

补丁说明

Robert Haas 提交的补丁,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Allow VACUUM to be run with index cleanup disabled.


This commit adds a new reloption, vacuum_index_cleanup, which
controls whether index cleanup is performed for a particular
relation by default. It also adds a new option to the VACUUM
command, INDEX_CLEANUP, which can be used to override the
reloption. If neither the reloption nor the VACUUM option is
used, the default is true, as before.

Masahiko Sawada, reviewed and tested by Nathan Bossart, Alvaro
Herrera, Kyotaro Horiguchi, Darafei Praliaskouski, and me.
The wording of the documentation is mostly due to me.

Discussion: http://postgr.es/m/CAD21AoAt5R3DNUZSjOoXDUY=naYPUOuffVsRzuTYMz29yLzQCA@mail.gmail.com

发行说明

Allow vacuum to avoid index cleanup with the INDEX_CLEANUP option (Masahiko Sawada)

本文简单演示下。

INDEX_CLEANUP

Specifies that VACUUM should attempt to remove index entries pointing to dead tuples. This is normally the desired behavior and is the default unless the vacuum_index_cleanup option has been set to false for the table to be vacuumed. Setting this option to false may be useful when it is necessary to make vacuum run as quickly as possible, for example to avoid imminent transaction ID wraparound (see Section 24.1.5). However, if index cleanup is not performed regularly, performance may suffer, because as the table is modified, indexes will accumulate dead tuples and the table itself will accumulate dead line pointers that cannot be removed until index cleanup is completed. This option has no effect for tables that do not have an index and is ignored if the FULL option is used.

环境准备

创建测试表并插入少量数据,如下:

1
2
3
4
5
6
7
8
mydb=> CREATE TABLE t_vacuum(id int4, name text);
CREATE TABLE

mydb=> INSERT INTO t_vacuum (id, name) SELECT n, n || '_index_cleanup' FROM generate_series(1,100000) n;
INSERT 0 100000

mydb=> CREATE INDEX idx_t_vacuum_id ON t_vacuum USING BTREE (id);
CREATE INDEX

测试 Vacuum 的 Index_cleanup 选项

对表 t_vacuum 进行 VACUUM,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mydb=> VACUUM (verbose) t_vacuum ;
psql: INFO: vacuuming "pguser.t_vacuum"
psql: INFO: index "idx_t_vacuum_id" now contains 100000 row versions in 276 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql: INFO: "t_vacuum": found 0 removable, 100000 nonremovable row versions in 637 out of 637 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 2470873
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql: INFO: vacuuming "pg_toast.pg_toast_32928"
psql: INFO: index "pg_toast_32928_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql: INFO: "pg_toast_32928": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 2470873
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

查找以上关键字 INFO 的输出,发现了 index 相关日志。

删除数据后,再次执行 VACUUM 命令,同时设置 INDEX_CLEANUP 参数为 true,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mydb=> DELETE FROM t_vacuum WHERE id < 10;
DELETE 9

mydb=> VACUUM (verbose,index_cleanup false) t_vacuum ;
psql: INFO: vacuuming "pguser.t_vacuum"
psql: INFO: "t_vacuum": found 9 removable, 296 nonremovable row versions in 2 out of 637 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 2470874
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql: INFO: vacuuming "pg_toast.pg_toast_32928"
psql: INFO: "pg_toast_32928": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 2470874
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

以上 INFO 相关的输出没有 index 相关的日志。

设置表的存储参数

VACUUM 命令的 INDEX_CLEANUP 选项作为 Storage Parameters,可以在表级进行设置,之后对表进行 ‘VACUUM’ 时不会回收索引,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mydb=> ALTER TABLE t_vacuum SET (vacuum_index_cleanup = false);
ALTER TABLE

mydb=> VACUUM (verbose) t_vacuum;
psql: INFO: vacuuming "pguser.t_vacuum"
psql: INFO: "t_vacuum": found 0 removable, 148 nonremovable row versions in 1 out of 637 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 2470876
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql: INFO: vacuuming "pg_toast.pg_toast_32928"
psql: INFO: "pg_toast_32928": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 2470876
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%