合 PG 13新特性之索引并行vacuum
👉 本文共约816个字，系统预计阅读时间或需4分钟。
简介
由于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表示禁止并行处理]。
前提条件：
- 需要设置合适的
MAX_PARALLEL_MAINTENANCE_WORKERS参数值，默认值为2 - 需要该表上有多个index（即：一个parallel worker对应一个索引）
- 需要该表的index的大小大于
min_parallel_index_scan_size参数值，默认为512KB
vacuum的parallel 选项与full选项不能同时使用
测试
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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 postgres=# create index i13 on t1(a,c); CREATE INDEX postgres=# \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+------+-------+----------+-------------+---------------+--------+------------- public | t1 | table | postgres | permanent | heap | 175 MB | (1 row) postgres=# \di+ List of relations Schema | Name | Type | Owner | Table | Persistence | Access method | Size | Description --------+------+-------+----------+-------+-------------+---------------+-------+------------- public | i1 | index | postgres | t1 | permanent | btree | 68 MB | public | i13 | index | postgres | t1 | permanent | btree | 76 MB | public | i2 | index | postgres | t1 | permanent | btree | 68 MB | public | i3 | index | postgres | t1 | permanent | btree | 24 MB | (4 rows) |
这表中有4个索引，如果指定4，则会有4个后台进程在这个表的索引上进行并行vacuum。
1 2 3 4 5 | postgres=# update t1 set a=5,b='ccc',c=now() where mod(a,5)=0; UPDATE 600000 postgres=# vacuum (parallel 4) t1; VACUUM |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
