合 PG 13新特性之索引并行vacuum
简介
由于PostgreSQL的MVCC机制的原因,需要清理old/dead记录,这些动作由vacuum完成。
PostgreSQL 12为止,vacuum还是一个表一个表,一个索引一个索引的进行。有一系列针对自动vacuum的参数对其进行调优。但是只有一个参数autovacuum_max_workers对表并行vacuum进行调优,对于索引并行vacuum仍不支持。PostgreSQL 13即将改变这种现状。
通过帮助信息可以看到vacuum新增了一个选项:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 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 integer and table_and_columns is: table_name [ ( column_name [, ...] ) ] URL: https://www.postgresql.org/docs/devel/sql-vacuum.html |
通过新增的“PARALLEL”选项,告诉vacuum使用多少后台进程针对给定表并行vacuum索引[0表示禁止并行处理]。