Postgres 無法添加外鍵約束

我有一個包含大約 2.2 億條記錄的表 :( 我需要添加一個外鍵約束。

我的命令看起來像這樣：

ALTER TABLE events 
ADD CONSTRAINT events_visitor_id_fkey 
FOREIGN KEY (visitor_id) 
REFERENCES visitors(id) 
ON DELETE CASCADE;

它已經執行了大概一個小時了。

我事先執行了這個：

set maintenance_work_mem='1GB';

最快的方法是什麼，大約需要多長時間。它引用的表格只有2500萬。

我在 db.r3.large（15 GB RAM）的 RDS 實例上執行它。

編輯：

剛剛取消了命令並得到了這個：

ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "SELECT fk."visitor_id" FROM ONLY "public"."events" fk LEFT OUTER JOIN ONLY "public"."visitors" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."visitor_id") WHERE pk."id" IS NULL AND (fk."visitor_id" IS NOT NULL)"

關於需要多長時間

這取決於數據量、索引和 IO 子系統的速度（後者尤其是在沒有支持索引的情況下）。

由於子表中已經有 2.2 億行，它必鬚根據父表檢查每一行，以確保它們都是有效值。如果沒有有用的索引，events.visitor這將意味著掃描整個表。

請注意，創建外鍵不會自動創建支持索引，因為根據您的訪問模式，可能不需要這樣的索引，因此會浪費空間。

最快的方法是什麼，

您可以通過添加告訴數據庫不要驗證現有行的外鍵約束（有關與此相關的詳細資訊和警告，請參閱https://www.postgresql.org/docs/current/sql-altertable.htmlNOT VALID上的文件） . 這是最快的方法，但可能會導致您有無效的行，直到它們引起問題才知道。（是 postgres 特定的語法，其他提供該選項的數據庫可能會以不同的方式命名，例如 MS SQL Server 的等價物是）

NOT VALID``WITH NOCHECK

第二種最快的方法是在定義外鍵之前確保該列上有一個索引，儘管在包含那麼多行的表上創建該索引本身會花費大量時間。

我又試了一次，半小時左右就完成了。不知道之前發生了什麼。

可能是在稍後的嘗試中，大部分錶在之前中止的創建過程中被讀取後都在記憶體中，因此第二次掃描以驗證現有行會更快，因為需要的磁碟讀取更少。

我又試了一次，半小時左右就完成了。不知道之前發生了什麼。

引用自：https://dba.stackexchange.com/questions/125702