PostgreSQL教程（6）VACUUM碎片清理与并行查询特性

一、VACUUM 介绍

PostgreSQL 的并发控制和大多数数据库一样，都是基于 MVCC 实现，但是区别在于它并不是把历史版本存在类似 MySQL Undo Log中，再由后台线程自动清理。PG是将被 DELETE 或 UPDATE 的数据标记为 dead tuple（死元组），然后插入一行新的数据。这样一样如果数据更新频繁就会产生大量的死元组，导致资源浪费，所以需要进行定期的清理工作，而完成这个清理工作的就是VACUUM。

另外在进行标记时使用到的事务标记 XID 是有限的，XID是一个包含42亿取值范围的环形，前面约 20 亿算已发生的事务，后面约 20 亿算尚未发生的事务，当XID溢出后就会导致数据不可见。举例说明：假设某行数据 xmin = 100（xmin表示最早通过 INSERT 或 UPDATE 语句产生这行数据的事务 ID）而当前XID已经达到了 21 亿，那么这行数据的就会被 PG 判定为尚未发生的事务，直接判它不可见。而为了防止XID回卷导致的数据丢失，PG会通过VACUUM把满足条件的数据 xmin 进行冻结，冻结后的数据对所有人可见，并且以后不再参与环形回卷。

1、VACUUM方式

1.1.1 自动触发

PostgreSQL 默认已启用 autovacuum 守护进程，它会根据表的更新比例、dead tuples 数量自动触发 VACUUM。在配置文件中相关参数如下：

autovacuum = on    #默认开启autovacuum
autovacuum_naptime = 60  #默认每分钟执行一次
autovacuum_max_workers = 5 #默认是启用3个线程工作

autovacuum_vacuum_threshold = 100000    #当产生了多少个死元祖才触发vacuum，默认 50
autovacuum_vacuum_scale_factor = 0.3  #表中产生了多少比例死元组（UPDATE + DELETE）会触发autovacuum，默认0.2表示20%

autovacuum_analyze_threshold = 100000   #触发analyze的最小行变更数，默认 50
autovacuum_analyze_scale_factor = 0.2  #INSERT/UPDATE/DELETE这些所有改动达到多少比例触发autovacuum，默认0.1表示10%

vacuum_cost_limit = 2000  #手动 VACUUM 的成本上限，达到后将进行休眠
autovacuum_vacuum_cost_delay = 100  #VACUUM休眠时间，默认2ms，如果发现某张表 vacuum 跑了几个小时还没完,且 I/O 并不紧张,通常就是此处配置过高
autovacuum_vacuum_cost_limit = -1 #单次 vacuum 允许消耗的成本，-1表示使用vacuum_cost_limit的值

1.1.2 手动触发

大部分情况下通过自动 VACUUM 就可以解决日常资源回收问题，当大表发生大量更新后也可以手动触发 VACUUM 进行清理。手动 触发又分为普通VACUUM 和FULL VACUUM

· 普通 VACUUM

该操作不锁表，只需要 SHARE UPDATE EXCLUSIVE 锁。会把死元组标记为可用空间，但是不会把空间真正还给操作系统，而是直接复用，在生产环境下通常都是通过 autovacuum 在做普通VACUUM

VACUUM table_name;

· VACUUM FULL

会对表加 ACCESS EXCLUSIVE 锁，操作期间表不可读写，其原理是重写整张表到新的物理文件，然后把空间还给操作系统，除了回收块内的碎片，还会重组数据块，大量减少数据块，加快数据扫描效果。通常在表膨胀特别严重、磁盘空间紧张时才手动执行。配合 pg_repack / pg_squeeze 等第三方工具可以在不锁表的情况下达到类似效果

VACUUM FULL table_name;         -- 彻底重写表,回收磁盘空间

1.1.3 表级VACUUM

针对每个表也可以单独设置 VACUUM 策略，避免统一阈值导致大表 VACUUM 不及时或者小表频繁VACUUM

ALTER TABLE xxx SET (autovacuum_vacuum_scale_factor = 0.05)

2、VACUUM示例

下面通过插入数据、删除数据、回收碎片的一系列操作来看看VACUUM的效果：

1、建库建表并插入数据

create database pgstudy

\c pgstudy

pgstudy=# CREATE TABLE t1 (
    id SERIAL PRIMARY KEY,
    info TEXT
);

pgstudy=# INSERT INTO t1 (info)
SELECT 'row ' || g
FROM generate_series(1, 1000000) g;

2、查看当前表大小

pgstudy=# SELECT pg_size_pretty(pg_relation_size('t1')) AS size;
 size  
-------
 42 MB
(1 row)

3、删除表中数据

pgstudy=# delete from t1

4、再次查看表大小，可以发现表大小没有变化，资源被浪费

pgstudy=# SELECT pg_size_pretty(pg_relation_size('t1')) AS size;
 size  
-------
 42 MB
(1 row)

5、查看表的状态信息

select * from pg_stat_user_tables where relname = "t1"
n_live_tup           #表中有效数据行
n_dead_tup         #表中没有回收的数据行

6、进行碎片清理

pgstudy=# vacuum t1

7、再次查看表中数据状态和表大小，已经有效的回收了碎片

pgstudy=# SELECT pg_size_pretty(pg_relation_size('t1')) AS size;
  size   
---------
 0 bytes
(1 row)

二、并行查询

PostgreSQL 从 9.6 版本开始支持并行查询特性，并且在之后的版本不断优化，在 15、16 版本中已经比较成熟。通过并行查询可以将一个复杂的 SQL 同时交给多个后台 worker 进程一起完成，加快查询速度，特别是对大表全表扫描、大量聚合、复杂 join 的场景。在 PostgreSQL 中并行查询是由优化器来决定是否使用的，不需要人为干涉，当优化器认为需要并行查询时会通过 Gather节点来启动并管理多个worker进程，由这些 worker 进程去进行扫描表或索引、做 join、聚合等操作。最后 Gather 节点再把这些 worker 进程返回的数据进行汇总，交给上层计划节点继续处理。

1、并行查询配置

主要参数都在 postgresql.conf 或 session 中设置

max_parallel_workers_per_gather = 4     #每个 Gather 节点最多能用的 worker 数，默认 2，通常建议设置 4-8
max_parallel_workers = 8    #全局最大并行 worker 数（默认 8）
parallel_setup_cost = 1000  #启动并行 worker 的固定开销，默认 1000
parallel_tuple_cost = 0.1   #每个 tuple 通过 Gather 节点传递的成本，默认 0.1
min_parallel_table_scan_size = 1024M  #表体积至少要达到多大才进行并行查询，默认8M
min_parallel_index_scan_size = 128M  #索引体积至少要达到多大才进行并行查询，默认512K

2、查看并行查询

执行计划里如果出现如下关键词代表使用到了并行查询

Gather  (cost=1000.00..20000.00 rows=1000000 width=8)
  Workers Planned: 4
  ->  Parallel Seq Scan on big_table ...

· Workers Planned：在 EXPLAIN 中看到的 “Workers Planned: N”，表示预计使用多少 worker

数据库postgresqlpgsql