诊断表和索引膨胀 - Amazon Aurora
在不中断应用程序的情况下观察膨胀避免临时表出现膨胀避免索引膨胀

诊断表和索引膨胀

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

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

  • 在 VACUUM 进程之间的相对较短的时间内处理大量事务。

  • 性能不佳，存储空间不足。

首先，收集最准确的信息，了解失效元组占用了多少空间，以及通过清理表和索引膨胀可以恢复多少空间。为此，请使用 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 扩展而不必中断繁忙的应用程序。为此，请执行以下步骤：

  1. 克隆您的 Aurora 实例。

  2. 修改参数文件以在克隆中关闭 AUTOVACUUM。

  3. 在使用示例工作负载或 pgbench（一个用于在 PostgreSQL 上运行基准测试的程序）测试克隆时执行 pgstattuple 查询。有关更多信息，请参阅 pgbench。

运行应用程序并查看结果后，在还原的副本上使用 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