View a markdown version of this page

避免 RDS for PostgreSQL 中 REPLICA IDENTITY FULL 的效能問題 - Amazon Relational Database Service
REPLICA IDENTITY FULL 造成問題的原因如何使用 REPLICA IDENTITY FULL 識別資料表解決方法和最佳實務建議摘要

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

避免 RDS for PostgreSQL 中 REPLICA IDENTITY FULL 的效能問題

PostgreSQL 邏輯複寫需要每個發佈的資料表具有複本身分，以便訂閱者可以找到要更新或刪除的正確資料列。根據預設，主索引鍵可做為複本身分。當資料表沒有主索引鍵或適當的唯一索引時，您可以將複本身分設定為 FULL，這會導致 PostgreSQL 使用整列做為索引鍵。

雖然 REPLICA IDENTITY FULL 解決了在沒有主索引鍵的情況下複寫資料表的立即問題，但可能會對發佈者和訂閱者造成嚴重的效能問題。了解這些影響對於搭配 RDS for PostgreSQL 使用邏輯複寫的任何人都很重要，包括內部依賴邏輯複寫的功能，例如藍/綠部署。

REPLICA IDENTITY FULL 造成問題的原因

發佈者的 WAL 磁碟區增加

REPLICA IDENTITY 設定會控制 PostgreSQL 寫入預寫日誌 (WAL) 的資訊，以識別已更新或刪除的資料列。使用預設複本身分 （主索引鍵），只有索引鍵資料欄會記錄為舊的資料列身分。透過 FULL，PostgreSQL 會記錄每個 UPDATE和 每欄的舊值DELETE。這有幾個後果：

  • WAL 大小大幅增加。對於更新，每個 WAL 記錄的大小大約會加倍，因為每個資料欄的舊值和新值都會記錄。如果資料表包含使用 TOAST 存放的大型值，則增加可能會更大，因為即使更新未修改 TOASTed 值，也必須擷取並寫入 WAL。

  • 發佈者上的 I/O 和 CPU 使用量較高。額外的 WAL 寫入會耗用更多磁碟 I/O 頻寬和 CPU 週期，特別是對於高寫入量的工作負載。

  • 傳送給訂閱者的更多資料。發佈者必須透過網路將較大的 WAL 記錄傳輸給每個訂閱者，以增加頻寬消耗。

訂閱者的資料列查詢緩慢

當訂閱者收到 UPDATE或 DELETE日誌記錄時，必須在其資料表的本機複本中找到相符的資料列。使用 REPLICA IDENTITY FULL，訂閱者會搜尋符合舊資料列影像中所有資料欄值的資料列。

PostgreSQL 如何執行此搜尋會因 PostgreSQL 主要版本而有所不同：

  • 在 PostgreSQL 16 之前：如果資料表沒有主索引鍵且未明確設定複本身分索引，訂閱者會針對每個單一 UPDATE 或 DELETE操作執行整個資料表的循序掃描。在大型資料表上，這會使套用效能非常慢。

  • PostgreSQL 16 和更新版本：訂閱者可以使用 Btree 或雜湊索引進行資料列查詢，即使該索引未明確設定為複本身分。不過，訂閱者不會評估哪個索引最有效率。從第 16 版開始，PostgreSQL 會選取第一個適合的索引，而使用者無法控制此選項。如果選取的索引具有低選擇性 （例如，布林值或狀態欄上的索引），資料列查詢的速度幾乎與循序掃描一樣慢。因此，依賴含 的隱含索引選擇REPLICA IDENTITY FULL是不可靠的，應該被視為備用，而不是建議的組態。

REPLICA IDENTITY FULL 如何導致複寫延遲

上述兩個問題：發佈者的 WAL 較大，訂閱者的資料列查詢較慢，結合起來會導致複寫延遲。

根據預設，PostgreSQL 邏輯複寫會使用每個訂閱的單一套用工作者程序，從發佈者接收變更，並將其套用至訂閱者的資料表。套用工作者程序會依遞交順序依序變更，一次變更一列。這表示訂閱者的輸送量會受到套用每個個別變更的速度限制。

在沒有適當索引的資料表上REPLICA IDENTITY FULL設定 時，每個 UPDATE和 DELETE需要循序掃描整個資料表，才能尋找相符的資料列。如果資料表有數百萬個資料列，則每個操作可能需要 秒或更長的時間。結果是串聯問題：

  1. 發佈者產生變更的速度比訂閱者套用變更的速度更快。發佈者的寫入工作負載會以正常速度繼續，但訂閱者的套用工作者會在每次資料列查詢的循序掃描或選擇不良索引時遇到瓶頸。

  2. WAL 會累積在發佈者上，並可能耗盡儲存體。PostgreSQL 無法回收這些區段。隨著訂閱者進一步落後，發佈者會在磁碟上累積 WAL。在 RDS for PostgreSQL 上，這在 CloudWatch OldestReplicationSlotLag中顯示為增長。在嚴重情況下，這可能會消耗所有可用的儲存體，並導致發佈者停止接受寫入。

  3. 延遲是自我強化。當訂閱者落後時，訂閱者的資料表會從複寫的插入繼續成長，使得每次循序掃描速度更慢。如果沒有介入，延遲就會無限制地成長。

對於經常接收 UPDATE或 DELETE操作的資料表而言，此問題特別嚴重。 INSERT操作不會受到影響，因為它們不需要對訂閱者進行資料列查詢。

注意

從 PostgreSQL 16 開始，套用工作者可以使用平行套用至大型串流交易，這有助於處理輸送量。不過，REPLICA IDENTITY FULL沒有索引的基本資料列查詢瓶頸仍然存在，因為每個個別資料列仍需要掃描才能找到。

對藍/綠部署的影響

Amazon RDS 中的藍/綠部署會在內部使用邏輯複寫，透過為每個資料庫設定單一訂閱，讓綠色環境與藍色環境保持同步。在綠色環境中邏輯複寫套用程序為單執行緒。單一套用工作者程序會從藍色環境接收所有變更，並依遞交順序一次套用一個變更。藍/綠複寫路徑中沒有平行套用。

這種單執行緒設計意味著綠色環境跟上藍色環境的能力，完全取決於一個套用工作者可以處理每個個別變更的速度。當資料表REPLICA IDENTITY FULL在沒有主索引鍵或適當索引的情況下使用 時，對套用工作者的影響取決於 PostgreSQL 版本。在 16 之前的版本中，這些資料表DELETE上的每個 UPDATE和 都會強制套用工作者對整個資料表執行循序掃描，以尋找相符的資料列。在版本 16 和更新版本中，如果有可用的索引，PostgreSQL 將使用適當的索引，但如果沒有合格的索引，則套用的工作者仍會回到循序掃描。 https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=89e46da5e當套用工作者掃描大型資料表的一列時，所有資料表中所有其他待定的變更都會排入佇列並等待。

藍/綠部署的後果很重要：

  • 複寫延遲會持續成長。如果藍色環境產生寫入流量的速度比單一套用工作者可以處理的速度快，則綠色環境會進一步落後。由於套用工作者是單執行緒，因此無法平行處理補上。

  • 切換可以封鎖。藍色/綠色切換需要綠色環境與藍色環境完全同步。如果複寫延遲太高，則切換無法在逾時期間內完成。

  • 綠色環境可能永遠不會追上進度。對於使用 REPLICA IDENTITY FULL和沒有索引的大型資料表的寫入密集型工作負載，套用率可能會太慢，以致於綠色環境永久落後，因此在未先解析複本身分組態的情況下，無法切換。

  • WAL 會累積在藍色環境中。當綠色環境落後時，藍色環境會保留複寫槽的 WAL 區段。這會增加藍色 （生產） 環境的儲存用量，並可能影響生產效能。

為了避免這些問題，請確保在建立藍/綠部署ALTER TABLE ... REPLICA IDENTITY USING INDEX之前，所有資料表都有一個主索引鍵或明確設定為複本身分的合適唯一索引。請勿在 PostgreSQL 16+ REPLICA IDENTITY FULL 中使用隱含索引選擇來依賴 ，因為訂閱者可能會選擇選擇性不佳的索引或回復循序掃描。使用代表性的寫入工作負載測試部署，以確認綠色環境可以跟上進度。

如需藍/綠部署限制的詳細資訊，請參閱 Amazon RDS 藍/綠部署的限制和考量事項。如需最佳實務做法，請參閱「RDS for PostgreSQL 藍/綠部署的最佳實務」。

如何使用 REPLICA IDENTITY FULL 識別資料表

執行下列查詢以尋找具有 的所有資料表REPLICA IDENTITY FULL：

SELECT n.nspname AS schema, c.relname AS table_name, c.relreplident
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relreplident = 'f'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;

relreplident 資料欄值為：

  • d — 預設 （主索引鍵）

  • n — 無

  • f — full （整個資料列）

  • i — 特定索引

解決方法和最佳實務

盡可能新增主索引鍵

最有效的解決方案是將主索引鍵新增至缺少主索引鍵的資料表。當主索引鍵存在時，PostgreSQL 會使用它做為預設複本身分，這會為訂閱者提供有效的資料列查詢，並將發佈者的 WAL 額外負荷降至最低。

ALTER TABLE my_table ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY;
重要

此陳述式會取得ACCESS EXCLUSIVE鎖定並重新寫入整個資料表，因為預設值表達式使用 nextval()揮發性。所有對資料表的讀取和寫入都會在重寫期間遭到封鎖。在大型資料表上，這可能會導致大量停機時間。在維護時段規劃此變更，或考慮替代方法，例如先將資料欄建立為 nullable，然後以個別步驟回填和新增限制條件。

如果因為應用程式限制而無法新增主索引鍵，請考慮在一組NOT NULL資料欄上新增唯一索引，並將其設定為複本身分：

CREATE UNIQUE INDEX my_table_replica_idx ON my_table (col1, col2);
ALTER TABLE my_table REPLICA IDENTITY USING INDEX my_table_replica_idx;
注意

若要避免在建立索引時封鎖寫入，請使用 CONCURRENTLY子句： CREATE UNIQUE INDEX CONCURRENTLY my_table_replica_idx ON my_table (col1, col2);

注意

用於複本身分的索引必須是唯一的、不得部分、不得延遲，且只能包含有NOT NULL限制的資料欄。

不要依賴隱含索引選擇 (PostgreSQL 16+)

從 PostgreSQL 16 開始，當複本身分設定為 時，訂閱者的套用工作者可以使用 btree 或雜湊索引進行資料列查詢FULL，即使這些索引未明確設定為複本身分。雖然這可防止在某些情況下進行循序掃描，但由於下列原因，依賴此隱含行為是反模式：

  • 您無法控制選擇哪個索引。PostgreSQL 會依目錄順序選取其找到的第一個合格索引，而非最具選擇性或效率的索引。如果資料表具有多個合格索引，則所選索引的選擇性可能較低，導致查詢效能不佳。

  • 行為很脆弱。新增、捨棄或重建索引可能會變更套用工作者使用的索引，這可能會導致複寫時發生非預期的效能迴歸。

  • 它會遮罩基礎問題。沒有主索引鍵或明確複本身分的資料表本質上具有邏輯複寫的風險。依賴隱含索引選擇會延遲問題，而不是解決問題。

反之，請明確設定每個複寫資料表的複本身分：

  • 最佳選項：新增主索引鍵。這是最可靠且最有效率的複本身分。

  • 替代方案：使用 ALTER TABLE ... REPLICA IDENTITY USING INDEX來指定只有資料NOT NULL欄的特定唯一、非部分、不可延遲的索引。這可讓您明確控制哪些資料欄用於資料列識別。

REPLICA IDENTITY FULL 僅針對兩個選項都不可行的資料表進行預留，並了解效能取決於您直接控制的因素。

監控複寫延遲

使用 時REPLICA IDENTITY FULL，請密切監控複寫延遲，以偵測訂閱者在速度變慢之前套用速度變慢。

在發佈者上，檢查目前 WAL 位置與訂閱者已確認內容之間的延遲：

SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(),
       (pg_current_wal_lsn() - confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

穩定成長lag_bytes的值表示訂閱者落後。pg_stat_replication_slots 檢視提供每個複寫槽用量的其他統計資料。

在訂閱者上，pg_stat_subscription檢視會顯示每個套用工作者的狀態，包括收到和報告的最後一個 WAL 位置：

SELECT subname, received_lsn, latest_end_lsn,
       last_msg_send_time, last_msg_receipt_time
FROM pg_stat_subscription;
注意

在 PostgreSQL 16 和更新版本上，您也可以選取 worker_type來區分主要套用工作者和平行套用工作者。

received_lsn 和 之間的大型差距latest_end_lsn，或 中的過時時間戳記last_msg_send_time，可能表示申請工作者正努力跟上進度。pg_stat_subscription_stats 檢視也會追蹤套用可能導致延遲的錯誤和衝突。

對於 RDS for PostgreSQL，您也可以監控 OldestReplicationSlotLag CloudWatch 指標，以最落後複寫槽的位元組追蹤延遲。上升值是複寫延遲的早期警告訊號。

檢查哪些資料表可能在套用期間使用次佳索引

在訂閱者上，您可以識別套用工作者執行過多堆積讀取的資料表，這可能表示資料表在套用期間沒有資料列查詢的有效索引。在訂閱者上執行下列查詢：

SELECT relname, heap_blks_read, heap_blks_hit,
       idx_blks_read, idx_blks_hit,
       heap_blks_read + heap_blks_hit AS total_heap_access
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 10;

heap_blks_read 相對於 值較高的資料表idx_blks_read可能表示套用工作者未使用有效的索引來尋找 UPDATE和 DELETE操作的資料列。這是使用 REPLICA IDENTITY FULL 時常見的複寫延遲來源。

注意

此查詢需要在訂閱者上啟用 track_counts 參數。此參數預設為開啟。

評估是否需要 REPLICA IDENTITY FULL

在設定 之前REPLICA IDENTITY FULL，請考慮您是否真的需要它。使用它的常見原因包括：

  • 資料表沒有主索引鍵或唯一索引。

  • 您需要完整的資料列前影像，以供變更資料擷取 (CDC) 取用者使用。

  • 您需要複寫事件中包含的 TOASTed 資料欄值，才能進行未修改這些資料欄的更新。

如果您的唯一原因是缺少主索引鍵，則新增一個索引鍵幾乎總是更好的路徑。如果您需要 CDC 的完整預先映像，請考慮您的 CDC 取用者是否可以透過在外部維護狀態來重建完整資料列，以避免 的 WAL 和訂閱者額外負荷REPLICA IDENTITY FULL。

建議摘要

案例 建議
資料表具有主索引鍵 使用預設複本身分 （不需要任何動作）
資料表具有唯一的 NOT NULL 索引 使用 將該索引設定為複本身分 ALTER TABLE ... REPLICA IDENTITY USING INDEX
資料表沒有適當的金鑰 (PostgreSQL 16+) 新增主索引鍵或唯一索引。使用 REPLICA IDENTITY FULL搭配隱含索引選擇是不可靠的，應該是最後的手段
資料表沒有適當的索引鍵 (PostgreSQL 16 之前） 新增主索引鍵或唯一索引；REPLICA IDENTITY FULL盡可能避免
具有大型/TOASTed資料欄的大量寫入工作負載 避免 REPLICA IDENTITY FULL WAL 磁碟區擴增