PostgreSQL 13:索引并行vacuum

原文

https://blog.dbi-services.com/postgresql-13-parallel-vacuum-for-indexes/

正文

PostgreSQL的MVCC机制的原因，需要清理old/dead记录。这写动作由vacuum完成。PostgreSQL12为止，vacuum还是一个表一个表，一个索引一个索引的进行。有一系列针对自动vacuum的参数对其进行调优。但是只有一个参数autovacuum_max_workers对表并行vacuum进行调优，对于索引并行vacuum仍不支持。PostgreSQL 13即将改变这种现状。

通过帮助信息可以看到vacuum新增了一个选项：

postgres=# \h vacuum

Command: VACUUM

Description: garbage-collect and optionally analyze a database

Syntax:

VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:

FULL [ boolean ]

FREEZE [ boolean ]

VERBOSE [ boolean ]

ANALYZE [ boolean ]

DISABLE_PAGE_SKIPPING [ boolean ]

SKIP_LOCKED [ boolean ]

INDEX_CLEANUP [ boolean ]

TRUNCATE [ boolean ]

PARALLEL intege

and table_and_columns is:

table_name [ ( column_name [, ...] ) ]

URL: https://www.postgresql.org/docs/devel/sql-vacuum.html

通过新增的“PARALLEL”选项，告诉vacuum使用多少后台进程针对给定表并行vacuum索引[0表示禁止并行处理]。下面是测试：

postgres=# create table t1 as select i as a, i::text as b, now() as c from generate_series(1,3000000) i;

SELECT 3000000

postgres=# create index i1 on t1(a);

CREATE INDEX

postgres=#

postgres=# create index i2 on t1(b);

CREATE INDEX

postgres=# create index i3 on t1(c);

CREATE INDEX

这表中有4个索引，如果指定4，则会有4个后台进程在这个表的索引上进行并行vacuum。

postgres=# update t1 set a=5,b='ccc',c=now() where mod(a,5)=0;

UPDATE 600000

postgres=# vacuum (parallel 4) t1;

VACUUM

由于这个表太小，只需要至少2个并行进程显示在vacuum的进程列表：

postgres 16688 15925 13 07:30 ? 00:01:07 postgres: postgres postgres [local] VACUUM

postgres 19184 15925 0 07:39 ? 00:00:00 postgres: parallel worker for PID 16688

postgres 19185 15925 0 07:39 ? 00:00:00 postgres: parallel worker for PID 16688

注意：并行vacuum的索引个数由min_parallel_index_scan_size控制。这个值最大为1024

postgres=# vacuum (parallel -4) t1;

ERROR: parallel vacuum degree must be between 0 and 1024

LINE 1: vacuum (parallel -4) t1;

下面是并行vacuum，并打印日志：

postgres=# vacuum (parallel 4, verbose true) t1;

INFO: vacuuming "public.t1"

INFO: launched 2 parallel vacuum workers for index vacuuming (planned: 2)

INFO: scanned index "i2" to remove 600000 row versions by parallel vacuum worke

DETAIL: CPU: user: 0.24 s, system: 0.06 s, elapsed: 0.89 s

INFO: scanned index "i1" to remove 600000 row versions

DETAIL: CPU: user: 0.17 s, system: 0.10 s, elapsed: 1.83 s

INFO: scanned index "i3" to remove 600000 row versions by parallel vacuum worke

DETAIL: CPU: user: 0.16 s, system: 0.14 s, elapsed: 1.69 s

INFO: scanned index "i4" to remove 600000 row versions by parallel vacuum worke

DETAIL: CPU: user: 0.25 s, system: 0.09 s, elapsed: 1.17 s

INFO: "t1": removed 600000 row versions in 20452 pages

DETAIL: CPU: user: 0.17 s, system: 0.16 s, elapsed: 1.43 s

INFO: index "i1" now contains 3000000 row versions in 14308 pages

DETAIL: 600000 index row versions were removed.

1852 index pages have been deleted, 640 are currently reusable.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO: index "i2" now contains 3000000 row versions in 14305 pages

DETAIL: 600000 index row versions were removed.

1851 index pages have been deleted, 640 are currently reusable.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO: index "i3" now contains 3000000 row versions in 14326 pages

DETAIL: 600000 index row versions were removed.

3941 index pages have been deleted, 1603 are currently reusable.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO: index "i4" now contains 3000000 row versions in 23391 pages

DETAIL: 600000 index row versions were removed.

5527 index pages have been deleted, 2246 are currently reusable.

CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

INFO: "t1": found 600000 removable, 3000000 nonremovable row versions in 21835 out of 22072 pages

DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 499

There were 132 unused item identifiers.

Skipped 0 pages due to buffer pins, 237 frozen pages.

0 pages are entirely empty.

CPU: user: 0.75 s, system: 0.36 s, elapsed: 5.07 s.

INFO: vacuuming "pg_toast.pg_toast_16392"

INFO: index "pg_toast_16392_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.

INFO: "pg_toast_16392": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages

DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 499

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