使用大型索引管理 autovacuum - Amazon Relational Database Service
尽快对表执行 vacuum 操作

使用大型索引管理 autovacuum

作为其操作的一部分，在对表运行 autovacuum 时会执行多个 vacuum 阶段。在清理表之前，首先对所有索引执行 vacuum 操作。删除多个大型索引时，此阶段会消耗大量的时间和资源。因此，作为最佳实践，请务必控制表上的索引数量并消除未使用的索引。

对于此过程，请先检查总体索引大小。然后，确定是否存在可以删除的潜在未使用索引，如以下示例所示。

检查表及其索引的大小

postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));
pg_size_pretty
6404 MB
(1 row)
postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));
pg_size_pretty
11 GB
(1 row)

在此示例中，索引的大小大于表的大小。这种差异可能会导致性能问题，因为索引膨胀或未使用，这会影响 autovacuum 以及插入操作。

检查是否有未使用的索引

使用 pg_stat_user_indexes 视图，您可以检查 idx_scan 列使用索引的频率。在以下示例中，未使用索引的 idx_scan 值为 0。

postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
    
relid  | indexrelid | schemaname | relname          | indexrelname          | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------------+-----------------------+----------+--------------+---------------
16433  | 16454      | public     | pgbench_accounts | index_f               | 6        | 6            | 0
16433  | 16450      | public     | pgbench_accounts | index_b               | 3        | 199999       | 0
16433  | 16447      | public     | pgbench_accounts | pgbench_accounts_pkey | 0        | 0            | 0
16433  | 16452      | public     | pgbench_accounts | index_d               | 0        | 0            | 0
16433  | 16453      | public     | pgbench_accounts | index_e               | 0        | 0            | 0
16433  | 16451      | public     | pgbench_accounts | index_c               | 0        | 0            | 0
16433  | 16449      | public     | pgbench_accounts | index_a               | 0        | 0            | 0
(7 rows)        
    
postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
    
schemaname  | relname          | indexrelname          | idx_scan
------------+------------------+-----------------------+----------
public      | pgbench_accounts | index_f               | 6
public      | pgbench_accounts | index_b               | 3
public      | pgbench_accounts | pgbench_accounts_pkey | 0
public      | pgbench_accounts | index_d               | 0
public      | pgbench_accounts | index_e               | 0
public      | pgbench_accounts | index_c               | 0
public      | pgbench_accounts | index_a               | 0
(7 rows)
注意

这些统计数据自统计数据重置之时起开始递增。假设您的索引仅用于业务季度末或仅用于特定报告。自从统计数据重置后，此索引可能就没有使用过。有关更多信息，请参阅统计数据函数。用于强制唯一性的索引不会执行扫描，也不应被标识为未使用的索引。要识别未使用的索引，您应该对应用程序及其查询有深入的了解。

要检查数据库上次重置统计数据的时间，请使用 pg_stat_database

postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';
    
datname   | stats_reset
----------+-------------------------------
postgres  | 2022-11-17 08:58:11.427224+00
(1 row)

尽快对表执行 vacuum 操作

RDS for PostgreSQL 12 及更高版本

如果大型表中的索引过多，则您的数据库实例可能接近事务 ID 重叠（XID），也就是 XID 计数器变为零时。如果不进行检查，这种情况可能导致数据丢失。但是，您可以在不清理索引的情况下快速对表执行 vacuum 操作。在 RDS for PostgreSQL 12 及更高版本中，可以将 VACUUM 与 INDEX_CLEANUP 子句结合使用。

postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;
        
INFO: vacuuming "public.pgbench_accounts"
INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.

如果 autovacuum 会话已在运行，则必须将其终止才能开始手动 VACUUM。有关执行手动 vacuum 冻结的信息，请参阅 执行手动 vacuum 冻结。

注意

定期跳过索引清理可能会导致索引膨胀，从而影响整体扫描性能。作为一种最佳实践，请仅使用前面的过程来防止事务 ID 重叠。

RDS for PostgreSQL 11 及更低版本

但是，在 RDS for PostgreSQL 11 及更低版本中，让 vacuum 过程更快地完成的唯一方法是减少表上的索引数量。删除索引可能会影响查询计划。我们建议您先删除未使用的索引，然后在 XID 重叠非常接近时删除索引。vacuum 过程完成后，您可以重新创建这些索引。