手动清理和分析表格 - AWS Prescriptive Guidance
并行运行 vacuum 和清理操作使用 VACUUM FULL 重写整个表

手动清理和分析表格

如果您的数据库通过 autovacuum 过程进行清理的，则最佳实践是避免过于频繁地对整个数据库进行手动清理操作。手动清理操作可能会导致不必要的 I/O 负载或 CPU 峰值，并且还可能无法移除任何死元组。只有在确实必要的情况下才应逐张表格地进行手动清空操作，比如当活元组与死元组的比例低，或者 autovacuum 操作之间存在较长间隔时。此外，在用户活动极少的情况下，您应当进行手动清理操作。

Autovacuum 还能确保表格的统计信息始终保持最新状态。当您手动运行 ANALYZE 命令时，它会重建这些统计信息，而不是更新它们。在常规的 autovacuum 过程已更新统计信息的情况下，再次重建统计信息可能会导致系统资源占用。

我们建议您在以下情况下手动运行 VACUUM 和 ANALYZE 命令：

  • 在繁忙表格的低峰时段，autovacuum 操作可能还不够的时候。

  • 在将数据批量加载到目标表之后。在这种情况下，手动运行 ANALYZE 可以完全重建统计信息，这比等待 autovacuum 操作开始更好。

  • 要清理临时表（autovacuum 无法访问这些表）。

要在对并发数据库活动运行 VACUUM 和 ANALYZE 命令时减少 I/O 影响，可以使用 vacuum_cost_delay 参数。在许多情况下，诸如 VACUUM 和 ANALYZE 之类的维护命令不必很快完成。但是，这些命令不应影响系统执行其他数据库操作的能力。为防止出现这种情况，您可以使用 vacuum_cost_delay 参数启用基于成本的 vacuum 延迟。对于手动发出的 VACUUM 命令，此参数默认处于禁用状态。要启用它，请将其设置为非零值。

并行运行 vacuum 和清理操作

VACUUM 命令 PARALLEL 选项在索引 vacuum 和索引清理阶段使用并行 Worker，其默认情况下处于禁用状态。并行 Worker 的数量（即并行度）由表中的索引数量决定，并且可以由用户指定。如果您在运行并行 VACUUM 操作时没有整数参数，则并行度根据表中的索引数量计算。

以下参数可帮助您在 Amazon RDS for PostgreSQL 和 Aurora PostgreSQL 版本中配置并行 vacuum 操作：

注意

PARALLEL 选项仅用于 vacuum 操作目的。它不影响 ANALYZE 命令。

以下示例说明了对数据库使用手动 VACUUM 和 ANALYZE 时的数据库行为。

以下是禁用 autovacuum 的示例表（仅用于说明目的；不建议禁用 autovacuum）：

create table t1 ( a int, b int, c int );
alter table t1 set (autovacuum_enabled=false);
apgl=> \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | integer | | | | plain | |
Access method: heap
Options: autovacuum_enabled=false

在表 t1 中添加 100 万行：

apgl=> select count(*) from t1;
count
1000000
(1 row)

表 t1 的统计信息：

select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+--------
relid         | 914744
schemaname    | public
relname       | t1
seq_scan      | 0
seq_tup_read  | 0
idx_scan      |
idx_tup_fetch |
n_tup_ins     | 1000000
n_tup_upd     | 0
n_tup_del     | 0
n_tup_hot_upd | 0
n_live_tup    | 1000000
n_dead_tup    | 0
n_mod_since_analyze | 1000000
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |
vacuum_count     | 0
autovacuum_count | 0
analyze_count    | 0
autoanalyze_count | 0

添加索引：

create index i2 on t1 (b,a);

运行 EXPLAIN 命令（计划 1）：

Bitmap Heap Scan on t1 (cost=10521.17..14072.67 rows=5000 width=4)
Recheck Cond: (a = 5)
→ Bitmap Index Scan on i2 (cost=0.00..10519.92 rows=5000 width=0)
Index Cond: (a = 5)
(4 rows)

运行 EXPLAIN ANALYZE 命令（计划 2）：

explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.023..0.024 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=4
→ Bitmap Index Scan on i2 (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.054 ms
Execution Time: 0.076 ms
(9 rows)

EXPLAIN 和 EXPLAIN ANALYZE 命令显示不同的计划，因为对表格的 autovacuum 操作被禁用，并且 ANALYZE 命令不是手动执行的。现在让我们更新表中的一个值并重新生成 EXPLAIN ANALYZE 计划：

update t1 set a=8 where b=5;
explain (analyze,buffers,costs off) select a from t1 where b = 5;

EXPLAIN ANALYZE 命令（计划 3）现在显示：

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.075..0.076 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=5
→ Bitmap Index Scan on i2 (actual time=0.017..0.017 rows=2 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.053 ms
Execution Time: 0.125 ms

如果您将计划 2 和计划 3 的成本进行比较，您就会发现两者在计划和执行时间上的差异，因为我们尚未收集统计信息。

现在让我们对表格运行手动 ANALYZE 操作，然后检查统计信息并重新生成计划：

apgl=> analyze t1
apgl→ ;
ANALYZE
Time: 212.223 ms

apgl=> select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+------------------------------
relid          | 914744
schemaname     | public
relname        | t1
seq_scan       | 3
seq_tup_read   | 1000000
idx_scan       | 3
idx_tup_fetch  | 3
n_tup_ins      | 1000000
n_tup_upd      | 1
n_tup_del      | 0
n_tup_hot_upd  | 0
n_live_tup     | 1000000
n_dead_tup     | 1
n_mod_since_analyze | 0
last_vacuum       |
last_autovacuum   |
last_analyze      | 2023-04-15 11:39:02.075089+00
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 1
autoanalyze_count | 0

Time: 148.347 ms

运行 EXPLAIN ANALYZE 命令（计划 4）：

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Index Only Scan using i2 on t1 (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: (b = 5)
Heap Fetches: 1
Buffers: shared hit=4
Planning Time: 0.056 ms
Execution Time: 0.068 ms
(6 rows)

Time: 138.462 ms

如果您在手动分析表格并收集统计信息后对所有计划结果进行比较，您就会发现优化程序的“计划 4”比其他计划更好，并且还能缩短查询执行时间。这个示例说明，对数据库进行维护活动是多么重要。

使用 VACUUM FULL 重写整个表

使用 FULL 参数运行 VACUUM 命令会将表格的全部内容重写到一个新的磁盘文件中，且不会占用额外空间，同时还会将未使用的空间归还给操作系统。此操作要慢得多，需要对每个表进行 ACCESS EXCLUSIVE 锁定。此外，它还需要额外的磁盘空间，因为其会创建表格的新副本，并且在操作完成之前不会释放旧副本。

VACUUM FULL 在以下情况下很有用：

  • 当您想从表格中回收大量空间时。

  • 当您想要回收非主键表中的膨胀空间时。

我们建议，当您有非主键表且您的数据库能够承受停机时间时，请使用 VACUUM FULL。

由于 VACUUM FULL 所需的锁定资源比其他操作更多，所以在关键数据库上运行该操作的成本会更高。要替换此方法，您可以使用 pg_repack 扩展程序，下一部分将对此进行介绍。此选项与 VACUUM FULL 相似但需要的锁定最少，且受 Amazon RDS for PostgreSQL 和 Aurora PostgreSQL 版本的支持。