PostgreSQL中如何透過ID高效率刪除數百萬資料庫行？

按 ID 高效能刪除數百萬行 PostgreSQL

刪除數百萬資料庫行會嚴重影響效能。本文探討了使用 ID 清單從 PostgreSQL 資料庫中刪除約 200 萬行的有效策略，解決常見瓶頸。

挑戰：

該任務涉及根據提供的 ID 清單刪除大型資料集。 批次刪除和 IN 子句查詢等標準方法對於這種規模通常效率低下。

最優解：

最佳方法取決於幾個因素：

• 並發存取：沒有並發寫入大大簡化了過程。
• 索引：暫時刪除不必要的索引（不包括對刪除至關重要的索引）並在之後重建它們可以提高速度。
• 觸發器：在刪除過程中停用或刪除觸發器可以顯著提高效能。
• 外鍵：精心管理外鍵關係；考慮暫時停用或修改以方便刪除。
• Autovacuum：提前運作VACUUM ANALYZE可以最佳化效能。
• 記憶體中方法（對於較小的剩餘資料集）：如果刪除後剩餘的資料遠小於原始表並且適合 RAM，請考慮這種高效的方法：

BEGIN;
SET LOCAL temp_buffers = '1000MB';
CREATE TEMP TABLE tmp AS
SELECT t.*
FROM   tbl t
LEFT   JOIN del_list d USING (id)
WHERE  d.id IS NULL;  -- copy remaining rows
TRUNCATE tbl;             -- clear the table
INSERT INTO tbl SELECT * FROM tmp;        -- re-insert remaining data
COMMIT;

這保留了外鍵、視圖和其他依賴項，從而產生乾淨且最佳化的表。

• DELETE 與 TRUNCATE: 對於較小的表，DELETE 可能比 TRUNCATE 更快，因為它維護觸發器和外鍵約束。

主要考慮因素：

• TRUNCATE 不能用於具有外鍵引用的表，除非所有引用表也同時被截斷。
• TRUNCATE 不會觸發 ON DELETE 觸發器。
• 刪除後VACUUM（或VACUUM FULL ANALYZE）對於回收磁碟空間和最佳化表大小至關重要。

以上是PostgreSQL中如何透過ID高效率刪除數百萬資料庫行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！