使用 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   |

• 最終 swap-and-drop階段。

當您使用pg_repack延伸模組從資料表和索引中移除 bloat 時，下列建議適用：

• 在非營業時間或維護時段執行重新封裝，以將其對其他資料庫活動效能的影響降至最低。

• 在重建活動期間密切監控封鎖工作階段，並確保原始資料表上沒有可能封鎖 的活動pg_repack，特別是在需要 swap-and-drop鎖定原始資料表的最終階段。如需詳細資訊，請參閱識別封鎖查詢的項目。

  當您看到封鎖工作階段時，您可以在仔細考慮後使用下列命令來終止它。這有助於繼續pg_repack完成重建：

  SELECT pg_terminate_backend(pid);

• 在交易速率非常高的系統上套用pg_repack's日誌表中的累積變更時，套用程序可能無法跟上變更速率。在這種情況下， pg_repack 將無法完成申請程序。如需詳細資訊，請參閱在重新封裝期間監控新資料表。如果索引嚴重突出，替代解決方案是僅執行索引重新封裝。這也有助於 VACUUM的索引清除週期更快完成。

  您可以使用 PostgreSQL 第 12 版VACUUM的手動略過索引清除階段，並在 PostgreSQL 第 14 版的緊急自動清空期間自動略過。這有助於在不移除索引印記的情況下更快VACUUM完成，而且僅適用於緊急狀況，例如防止包裝 VACUUM。如需詳細資訊，請參閱 Amazon Aurora 使用者指南中的避免在索引中使用 bloat。

• 資料表必須具有PRIMARYKEY或不為空UNIQUE的限制。

• 用戶端和伺服器的延伸版本必須相同。

• 確保RDS執行個體具有FreeStorageSpace超過無 bloat 之資料表的總大小。例如，請考慮資料表的總大小，包括 TOAST和 索引為 2TB ，資料表中的 bloat 總計為 1TB 。所需的值FreeStorageSpace必須大於下列計算傳回的值：

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

  您可以使用下列查詢來檢查資料表的總大小，並使用 pgstattuple衍生 bloat。如需詳細資訊，請參閱 Amazon Aurora 使用者指南中的診斷資料表和索引 bloat

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

  此空間會在活動完成後回收。

• 確保RDS執行個體有足夠的運算和 IO 容量來處理重新封裝操作。您可以考慮擴展執行個體類別，以獲得最佳效能平衡。

• 資料庫的大小會由資料表的總大小減去 bloat 增加，直到 swap-and-drop重新封裝階段為止。您可以監控資料庫大小的成長率、計算重新封裝的速度，並大致估計完成初始資料傳輸所需的時間。

  例如，請將資料表的總大小視為 2TB ，將資料庫的大小視為 4TB ，將資料表中的 bloat 總數視為 1TB 。重新封裝操作結束時，計算傳回的資料庫總大小值如下：

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

  您可以透過取樣兩個時間點之間的位元組成長率，大致估計重新封裝操作的速度。如果成長率為每分鐘 1GB，則可能需要 1000 分鐘或 16.6 小時左右才能完成初始資料表建置操作。除了初始資料表建置之外， pg_repack也需要套用累積的變更。所需時間取決於套用進行中變更和累積變更的速率。

  注意

  您可以使用pgstattuple延伸功能來計算資料表中的 bloat。如需更多詳細資訊，請參閱 pgstattuple。

• pg_repack's 日誌資料表中的資料列數目，在重新封裝結構描述下，代表初始載入後待套用至新資料表的變更量。

  您可以在 中檢查pg_repack's日誌資料表pg_stat_all_tables，以監控套用至新資料表的變更。 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)