主页 / dba / 问题 / 301749
Accepted
rdbmsNoob
Asked: 2021-10-28 02:16:28 +0800 CST

我应该分批将数据复制到另一个表并删除旧数据，我应该删除索引和 FK 约束吗？

  • 772

我希望从包含 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 亿行。

需要保留旧数据，以便可以访问。非常感谢任何建议。

postgresql delete

1 个回答

  1. Best Answer

    第一步是升级到当前版本。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 必须检查链接表中可能引用的行。）

    有关的：

    本手册相关章节中的基础知识：填充数据库

    • 2

相关问题