诊断表和索引膨胀

可以使用 PostgreSQL 多版本并发控制（MVCC）来帮助保持数据的完整性。PostgreSQL MVCC 的工作原理是保存已更新或已删除行（也称为元组）的内部副本，直到事务提交或回滚为止。这个保存的内部副本对用户不可见。但是，当 VACUUM 或 AUTOVACUUM 实用程序未定期清理这些不可见副本时，可能会出现表膨胀。如果不加以控制，表膨胀可能会增加存储成本并降低处理速度。

在许多情况下，Aurora 上的 VACUUM 或 AUTOVACUUM 的原定设置足以处理不必要的表膨胀。但是，如果您的应用程序遇到以下情况，则可能需要检查是否存在膨胀：

首先，收集最准确的信息，了解失效元组占用了多少空间，以及通过清理表和索引膨胀可以恢复多少空间。为此，请使用 pgstattuple 扩展来收集 Aurora 集群的统计数据。有关更多信息，请参阅 pgstattuple。使用 pgstattuple 扩展的权限仅限于 pg_stat_scan_tables 角色和数据库超级用户。

要在 Aurora 上创建 pgstattuple 扩展，请将客户端会话连接到集群，例如 psql 或 pgAdmin，然后使用以下命令：

CREATE EXTENSION pgstattuple;

在要分析的每个数据库中创建此扩展。创建扩展后，使用命令行界面（CLI）来衡量可以回收多少不可用的空间。在收集统计数据之前，通过将 AUTOVACUUM 设置为 0 来修改集群参数组。设置为 0 会阻止 Aurora 自动清理应用程序留下的任何失效元组，这可能会影响结果的准确性。输入以下命令以创建简单表：

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

在以下示例中，我们在为数据库集群开启 AUTOVACUUM 的情况下运行查询。dead_tuple_count 为 0，这表明 AUTOVACUUM 已经从 PostgreSQL 数据库中删除了过时的数据或元组。

要使用 pgstattuple 收集有关表的信息，请在查询中指定表的名称或对象标识符（OID）：

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

在以下查询中，我们关闭 AUTOVACUUM 并输入从表中删除 25000 行的命令。结果，dead_tuple_count 增加到 25000。

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

要回收那些失效的元组，请启动 VACUUM 进程。

在不中断应用程序的情况下观察膨胀

Aurora 集群上的设置经过优化，可为大多数工作负载提供最佳实践。但是，您可能需要优化集群以更好地适合您的应用程序和使用模式。在这种情况下，可以使用 pgstattuple 扩展而不必中断繁忙的应用程序。为此，请执行以下步骤：

运行应用程序并查看结果后，在还原的副本上使用 pg_repack 或 VACUUM FULL 并比较差异。如果您看到 dead_tuple_count、dead_tuple_len 或 dead_tuple_percent 显著下降，请调整生产集群的 vacuum 时间表以最大限度地减少膨胀。

避免临时表出现膨胀

如果您的应用程序会创建临时表，请确保应用程序在不再需要这些临时表时将其删除。Autovacuum 进程无法找到临时表。如果不加以控制，临时表会迅速造成数据库膨胀。此外，膨胀可能扩展到系统表，这些表是跟踪 PostgreSQL 对象和属性的内部表，如 pg_attribute 和 pg_depend。

当不再需要临时表时，可以使用 TRUNCATE 语句清空该表并释放空间。然后，手动对 pg_attribute 表和 pg_depend 表执行 vacuum 操作。对这些表执行 vacuum 操作可确保持续创建和截断/删除临时表不会增加元组和导致系统膨胀。

在创建临时表时，可以通过包含以下语法来避免此问题，这些语法用于在提交内容时删除新行：

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

提交事务时，ON COMMIT DELETE ROWS 子句会截断临时表。

避免索引膨胀

当您更改表中已编制索引的字段时，索引更新会导致该索引中出现一个或多个无效元组。原定设置情况下，autovacuum 进程会清理索引中的膨胀，但这种清理会消耗大量的时间和资源。要在创建表时指定索引清理首选项，请包括 vacuum_index_cleanup 子句。原定设置情况下，在创建表时，该子句设置为 AUTO，这意味着服务器在对表执行 vacuum 操作时决定您的索引是否需要清理。您可以将该子句设置为 ON 以开启特定表的索引清理，或者将该子句设置为 OFF 以关闭该表的索引清理。请记住，关闭索引清理可能会节省时间，但可能会导致索引膨胀。

在命令行中对表执行 VACUUM 时，可以手动控制索引清理。要对表执行 vacuum 操作并从索引中删除无效元组，请包括值为 ON 的 INDEX_CLEANUP 子句和表名称：

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

要在不清理索引的情况下对表执行 vacuum 操作，请将值指定为 OFF：

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM