原文
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
