PostgreSQL在线表整理是由autovacuum进程来完成,不过每次autovacuum执行周期内是有资源管控限制的。有些业务场景(例如批量更新)下,需要使用手工的vacuum命令或vacuumdb工具来进行一些更为灵活的操作。
本文整理了vacuum操作的如下8个功能场景
- 同时指定多个表
- 跳过被锁住的表
- 跳过索引清理
- 并行清理索引
- 配置内存用量
- 快捷清理TOAST表
- 解藕更新统计信息
- schema级按需清理
一、同时指定多个表
从PostgreSQL 11开始,vacuum命令支持同时指定多个表进行操作:
postgres=# VACUUM tab1, tab2;
VACUUM
二、跳过被锁住的表
从PostgreSQL 12开始,vacuum命令可以跳过被锁住的表,使操作能够执行完成而不会一直处于等待状态。
postgres=# VACUUM (SKIP_LOCKED) foo;
WARNING: skipping vacuum of "foo" --- lock not available
VACUUM
使用SKIP_LOCKED选项后,可看到一条表被锁住跳过执行的警告信息。
三、跳过索引清理
很多场景下索引的清理往往占大头,PostgreSQL 12里表和索引的清理操作支持解耦,允许vacuum命令显式设置INDEX_CLEANUP选项,语法参考如下:
VACUUM (VERBOSE, INDEX_CLEANUP off) foo;
假如有一个带有很多索引的表,我们计划对该表重建索引。操作之前,可以先单独清理表,否则常规操作会同时清理索引。
四、并行清理索引
从PostgreSQL 13开始,索引清理在单表上支持并行操作,每个索引都可以分配一个工作清理进程。它的使用语法参考如下:
VACUUM (PARALLEL 8) foo;
并行度可以简单的按CPU的核数进行设置,充分利用CPU,提高清理效率。
五、配置内存用量
在PostgreSQL 16里,vacuum命令可以使用BUFFER_USAGE_LIMIT选项控制内存使用量。参考语法如下:
VACUUM (BUFFER_USAGE_LIMIT 0) foo;
针对大表的清理操作使用该选项,设置为0,允许使用所有shared buffer,最大化利用buffer cache。
六、快捷清理TOAST表
数据行受限于不能跨page存储,因而每个table可能会有一个与之关联且隐藏的TOAST表。
在PostgreSQL 16里,vacuum命令可以使用PROCESS_MAIN选项只处理主表数据,或者设置为false只处理附属的TOAST表。参考语法如下:
VACUUM (PROCESS_MAIN false) foo;
PROCESS_MAIN选项设置为false,可以通过主表来快捷清理TOAST表,否则需要从pg_class表进行关联查询获取TOAST表的名称。
七、解藕更新统计信息
vacuum命令如果操作的表很多, 每次清理更新数据库维度的统计信息性能会很差。
PostgreSQL 16里可以使用SKIP_DATABASE_STATS选项暂不更新统计信息,等所有表清理完成之后再使用ONLY_DATABASE_STATS选项统一操作。
例如下面在清理表时都使用SKIP_DATABASE_STATS选项跳过更新统计信息。
VACUUM (SKIP_DATABASE_STATS) tab1;
VACUUM (SKIP_DATABASE_STATS) tab2;
...
最后再只做一次更新统计信息的操作。
VACUUM (ONLY_DATABASE_STATS)...
八、schema级按需清理
多租户场景下,在PostgreSQL 16里,vacuumdb工具可指定schema或排除schema来进行黑白名清理。
使用–schema选项只处理某些模式下的对象,例如:
$ vacuumdb --schema schema1 --schema schema2
使用–exclude-schema选项跳过处理某些模式下的对象,例如:
$ vacuumdb --exclude-schema schema1 --exclude-schema schema2
最后再跟大家同步一下新书<<快速掌握PostgreSQL版本新特性>>的进度,出版社目前在终审阶段。书稿简介可以参考我写的这篇文章:PostgreSQL版本新特性顺利完稿