我應該分批將數據複製到另一個表並刪除舊數據，我應該刪除索引和 FK 約束嗎？

我希望從包含 8.89 億行數據的表中刪除舊數據。

我有一個腳本，但我試圖讓它更健壯，因為準確地說，它刪除了大約 4.18 億行數據。

我在 Postgres 9.6 中執行，帶有表格和 FK 約束

         Column          |           Type           | Collation | Nullable |   
            Default                
--------------------------+--------------------------+-----------+----------+---
------------------------------------
game_id                  | integer                  |           | not null | ne
xtval('game_game_id_seq'::regclass)
game_id                  | integer                  |           | not null | 
session_id               | integer                  |           |          | 
game_created_on          | timestamp with time zone |           | not null | 
currency_code            | character(3)             |           | not null | 
game_cash_staked         | numeric(12,2)            |           |          | 
game_cash_won            | numeric(12,2)            |           |          | 
game_bonus_staked        | numeric(12,2)            |           |          | 
game_bonus_won           | numeric(12,2)            |           |          | 
game_created_by_id       | integer                  |           | not null | 
game_remote_ref          | character varying(50)    |           |          | 
game_description         | text                     |           |          | 
game_batch_id            | integer                  |           |          | 
game_rejection_code_id   | integer                  |           |          | 
game_rejection_message   | character varying(255)   |           |          | 
game_transfer_remote_ref | character varying(128)   |           |          | 

Indexes:
   "game_pkey" PRIMARY KEY, btree (game_id)
   "idx_game_created_on_rejection_code" btree (game_created_on) WHERE game_rejection_code_id IS NULL
   "idx_game_game_created_on" btree (game_created_on)
   "idx_game_session_id" btree (session_id)
   "game_idx_01" btree (game_remote_ref)
   "game_idx_game_id" btree (game_id)
Foreign-key constraints:
   "ref_game_to_currency" FOREIGN KEY (currency_code) REFERENCES currency(currency_code)
   "ref_game_to_game" FOREIGN KEY (game_id) REFERENCES game(game_id)
   "ref_game_to_game_rejection_code" FOREIGN KEY (game_rejection_code_id) REFERENCES game_rejection_code(game_re
jection_code_id)

Scipt 已經使用：

begin;
CREATE TABLE gamearchived AS
SELECT t.*
FROM  game t
where t.game_created_on < NOW() - interval '1 year'; -- this grabs stuff Older than 1 year
delete from game t
where  t.game_id in (select gamearchived.game_id from gamearchived);
select count (*) from gamearchived
COMMIT;

我想知道這是否是從主表中刪除舊數據或分批執行的最安全方法。另外，我將從中刪除數據的目前表具有索引和外鍵約束，最好在刪除之前先刪除索引，然後在完成後將它們添加回來。刪除的數據量約為 4.5 億行。

需要保留舊數據，以便可以訪問。非常感謝任何建議。

第一步是升級到目前版本。Postgres 9.6 在 2021 年 11 月 11 日達到 EOL - 在三週內。Postgres 13 或 14 在處理大數據時要快得多。在這種情況下，刪除並重新創建所有索引還有額外的好處：這樣您就可以利用 Postgres 13 或更高版本的新功能索引重複數據刪除：將具有重複數據的索引縮小到其大小的一小部分。

假設沒有並發訪問。

在刪除之前先刪除索引會更好，然後在完成後將它們添加回來。

因為你刪除了一半的表，通常是的。額外的好處是重新創建的索引處於原始狀態而不會膨脹。

這會快得多：

BEGIN;
CREATE TABLE public.gamearchived (LIKE public.game);

-- DROP all indexes on table game here (be sure to remember the DDL!)

WITH del AS (
  DELETE FROM game
  WHERE  game_created_on < NOW() - interval '1 year';  -- older than 1 year
  RETURNING *
  )
, ins AS (
  INSERT INTO public.gamearchived
  SELECT * FROM del
  ORDER  BY game_created_on;  -- optional, only if it helps future queries
   )
SELECT count(*) FROM del;  -- get your count

-- run checks if you are not sure; last chance.

COMMIT;

如果出現任何問題，事務將回滾。所以這是安全的。

在同一個事務中創建新表可以節省大量成本：不需要編寫額外的 WAL。

主要區別：這只需要對大表進行*一次順序掃描。*您的原件做了很多額外的（無意義的）工作。

此外，我們不需要大的任何索引DELETE。在之前刪除它們並在之後重新創建它們比逐步更新它們更便宜。

之後我至少會執行它：

VACUUM ANALYZE game;
VACUUM ANALYZE gamearchived;

為了釋放空間（雖然你沒有並發訪問），甚至：

VACUUM FULL ANALYZE game;

（在 . 上執行沒有意義VACUUM FULL。gamearchived）

理想情況下，現在重新創建表上的所有索引game。

此外，在 table 上創建您想要的任何約束或索引（PK？）gamearchived。不必在同一個事務中。

重要的是在大插入之後進行，這更便宜並且通常會產生更好的結果（索引平衡而不會膨脹）。

表中的傳出FK 約束game無關緊要，不要對DELETE​​ . （傳入的 FK 約束很重要，因為 Postgres 必須檢查連結表中可能引用的行。）

有關的：

• VACUUM 將磁碟空間返回給作業系統
• 在大表中填充新列的最佳方法？
• 按 ID 刪除數百萬行的最佳方法

本手冊相關章節中的基礎知識：填充數據庫

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