主页 / dba / 问题 / 247478
Accepted
Chessbrain
Asked: 2019-09-11 15:41:16 +0800 CST

在不影响生产的情况下将数据插入存档表的最有效方法？

  • 772

例如，假设我们有表 A：

create table if not exists T(
column1 int,
column2 varchar,
column3 date
);

和归档表 TArchive：

create table if not exists TArchive(
column1 int,
column2 varchar,
column3 date
);

将早于x日期的数据插入 TArchive 而不在生产中锁定表 T 的最佳方法是什么？假设表 T 有大量的行。

我已经研究了好几个小时了。在 SQL Server 中，您有不同的方法，例如：https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/ 但在 PostgreSQL我几乎找不到任何东西。

您是否应该直接从 T 表中获取数据并将其导入 TArchive？

您是否应该先将数据导入临时表，然后再将其导入存档表？如果是这样，当您为相同的数据执行 2 倍的插入时，为什么这种方法会更好。

你应该做多少个功能？一个功能来统治他们？还是一个归档功能和另一个删除旧数据的功能？

还有其他方法吗？

postgresql performance

3 个回答

  1. Best Answer

    您可能找不到任何关于该主题的文章，因为它非常简单：

    WITH old_rows AS (
   DELETE FROM t
   WHERE ...
   RETURNING *
)
INSERT INTO tarchive
SELECT * FROM old_rows;

    这不会t非常锁定表——并发SELECT和数据修改不会受到影响（除非他们试图修改已删除的行之一）。

    你的主要问题是：

    • 该语句可能需要很长时间，在运行时会持有锁并阻塞 autovacuum。

    • 之后，桌子不会更小，而是更空。这会损害顺序扫描并浪费缓存空间。

    特别是第二个问题很痛苦，补救措施都需要让表暂时不可用：

    • VACUUM (FULL)将重新组织表，但在运行时阻止对表的任何访问。

    • 用不同的表替换表将阻止数据修改一段时间：

      BEGIN;
LOCK TABLE bloated IN SHARE ROW EXCLUSIVE MODE;
CREATE TABLE bloated_copy (LIKE bloated INCLUDING ALL);
INSERT INTO bloated_copy SELECT * FROM bloated;
DROP TABLE bloated;
ALTER TABLE bloated_copy RENAME TO bloated;
COMMIT;

    正因为如此，在设计系统时计划好如何摆脱旧数据是个好主意，但根据我的经验，这几乎总是被遗忘。摆脱旧数据最不痛苦的方法是按时间对表进行分区。

    • 2

  2. 对我有用的方法是让档案脚本分批工作，中间有中断。所以它看起来像这样：

    • 查找需要归档的前 5000 行
    • 将它们复制到存档
    • 从源表中删除它们
    • 睡眠（30 秒）
    • 不断重复，直到没有更多行需要归档

    这样，您的服务器在运行时不会承受沉重的负载，并且数据库永远不会被锁定很长时间。缺点是这将比其他一些方法花费更长的时间。

    • 2

  3. 在进一步挖掘之前，您是否考虑过逻辑复制？在 Postgres 中它非常可爱。它可能符合您的要求，也可能不符合您的要求，但在您考虑选项时，它值得一试。例如，您可以在第二个 Postgres 实例中拥有存档表，并使用逻辑复制来复制所有 INSERT 和 UPDATE 操作，但不进行删除。不需要触发器。（如果您想要一个基于触发器的解决方案，那里有全面的解决方案。）如果您需要搜索存档，它位于另一台服务器上的另一个表中，但您可以使用外部数据包装器使其在您的主服务器上可见.

    一些问题：

    • 您对归档数据有什么样的查询要求？

    • 您将如何删除主表中的行？在循环中批量删除可能会更好？

    • 您在谈论一次删除什么样的数字？AUTOVACUUM 不是您一定会期望来自 SQL Server 的东西。

    • 0

相关问题