使用 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 版的緊急自動清空期間自動略過。這有助於 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)