使用 pg_repack 扩展减少表和索引的膨胀

• 初始创建日志表 - 创建日志表以记录在数据初始复制期间发生的更改，如以下示例所示：

  postgres=>\dt+ repack.log_*
  List of relations
  -[ RECORD 1 ]-+----------
  Schema        | repack
  Name          | log_16490
  Type          | table
  Owner         | postgres
  Persistence   | permanent
  Access method | heap
  Size          | 65 MB
  Description   |

• 最后的交换和删除阶段。

当您使用 pg_repack 扩展从表和索引中移除膨胀内容时，以下建议适用：

• 在非工作时间或在维护时段内执行重新打包，以最大限度地减少它对其它数据库活动性能的影响。

• 在重建活动期间，密切监视阻止会话，并确保原始表上不存在可能阻止 pg_repack 的活动，特别是在最后的交换和删除阶段，此时它需要对原始表进行独占锁定。有关更多信息，请参阅识别阻止查询的内容。

  当您看到阻止会话时，经慎重考虑后，可以使用以下命令将其终止。这有助于继续执行 pg_repack 以完成重建：

  SELECT pg_terminate_backend(pid);

• 在事务速率非常高的系统上应用 pg_repack's 日志表中的应计更改时，应用过程可能无法跟上更改速率。在这种情况下，pg_repack 将无法完成应用过程。有关更多信息，请参阅 在重新打包期间监控新表。如果索引严重膨胀，另一种解决方案是执行仅限索引的重新打包。这还有助于 VACUUM 的索引清理周期更快地完成。

  您可以使用 PostgreSQL 版本 12 中的手动 VACUUM 跳过索引清理阶段，在 PostgreSQL 版本 14 中的紧急 autovacuum 期间会自动跳过索引清理阶段。这有助于在不消除索引膨胀的情况下更快地完成 VACUUM，并且仅适用于紧急情况，例如防止重叠 VACUUM。有关更多信息，请参阅《Amazon Aurora 用户指南》中的避免索引膨胀。

• 该表必须具有 PRIMARY KEY 或非 null UNIQUE 约束。

• 客户端和服务器的扩展版本必须相同。

• 确保 RDS 实例的 FreeStorageSpace 超过表的总大小，而不会出现膨胀。例如，假设表（包括 TOAST 和索引）的总大小为 2TB，表中的总膨胀为 1TB。所需 FreeStorageSpace 必须大于以下计算返回的值：

  2TB (Table size) - 1TB (Table bloat) = 1TB

  您可以使用以下查询来检查表的总大小，并使用 pgstattuple 来派生膨胀。有关更多信息，请参阅《Amazon Aurora 用户指南》中的诊断表和索引膨胀

  SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;

  活动完成后，将回收此空间。

• 确保 RDS 实例有足够的计算和 IO 容量来处理重新打包操作。您可以考虑纵向扩展实例类以实现性能的最佳平衡。

• 在重新打包的交换和删除阶段之前，数据库的大小增加量为表的总大小减去膨胀。您可以监控数据库大小的增长率，计算重新打包的速度，并粗略估计完成初始数据传输所需的时间。

  例如，假设表的总大小为 2TB，数据库的大小为 4TB，表中的总膨胀为 1TB。重新打包操作结束时计算返回的数据库总大小值如下：

  2TB (Table size) + 4 TB (Database size) - 1TB (Table bloat) = 5TB

  您可以通过对两个时间点之间的增长率（以字节为单位）进行采样来粗略估计重新打包操作的速度。如果增长率为每分钟 1GB，则可能需要 1000 分钟或大约 16.6 小时才能完成初始表构建操作。除了初始表构建外，pg_repack 还需要应用应计更改。所需时间取决于应用持续更改以及应计更改的速率。

  注意

  您可以使用 pgstattuple 扩展来计算表中的膨胀。有关更多信息，请参阅 pgstattuple。

• 重新打包架构下 pg_repack's 日志表中的行数表示在初始加载后待应用于新表的更改量。

  您可以检查 pg_stat_all_tables 中的 pg_repack's 日志表以监控应用于新表的更改。pg_stat_all_tables.n_live_tup 表示待应用于新表的记录数。有关更多信息，请参阅 pg_stat_all_tables。

  postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%';
          
  -[ RECORD 1 ]---------
  relname    | log_16490
  n_live_tup | 2000000
          

• 您可以使用 pg_stat_statements 扩展来找出重新打包操作中每个步骤所花费的时间。这有助于准备在生产环境中应用相同的重新打包操作。您可以调整 LIMIT 子句以进一步扩展输出。

  postgres=>SELECT
       SUBSTR(query, 1, 100) query,
       round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes
   FROM
       pg_stat_statements
   WHERE
       query ILIKE '%repack%'
   ORDER BY
       total_exec_time DESC LIMIT 5;
          
   query                                                                 | total_exec_time_in_minutes
  -----------------------------------------------------------------------+----------------------------
   CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) |                     6.8627
   INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1           |                     6.4150
   SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)                    |                     0.5395
   SELECT repack.repack_drop($1, $2)                                     |                     0.0004
   SELECT repack.repack_swap($1)                                         |                     0.0004
  (5 rows)