postgresql - 如何从 PostgreSQL 中删除大量行？

Question

我们有一个包含很多行 (100m) 的表托管在 AWS RDS 中。

1. 如果表具有不断的读取、写入和更新查询，我们如何才能有效地删除 50% 的行？
2. 如何每天删除 1% 的数据？

表结构:

• 创建于
• 用户编号
• 其他数据

我们尝试一天一天地删除，但随后 autovacuum 启动并且队列深度增加。

Answer 1

我猜您想根据 created_at 值删除很多行。而且，我猜你的表有一个 id 列，它是唯一的主键。

您所做的是一次删除一个行数有限的批处理，就像这样。

DELETE FROM yourtable
 WHERE id IN (
     SELECT id
       FROM yourtable
      WHERE created_at < '2020-11-01'
      LIMIT 1000
    )

(在我的示例中，我们将删除表中在 2020 年 10 月结束之前的任何时间创建的所有行。)

这将删除一批 1000 行。您继续运行此查询，直到它不删除任何行。

之所以可行，是因为删除每个批处理不需要很长时间，并且每个批处理都不会过多地干扰您的生产工作量或真空维护。如果您在 created_at 列上有一个索引，它将特别有效。

在批处理之间延迟几百毫秒也是明智的，因为您更不可能干扰您的生产工作流程。

一次删除 25 亿行一千次将需要 25 万个批处理。但没关系，这就是发明编程的原因。这种批处理方法在我工作过的地方非常有效，适用于最初设计不是为了便于清理的表格。

一旦您删除了大量积压的旧行，那么每天跟上它就容易多了。

如果您每天都必须删除大量行，那么分区是可行的方法(如评论中所述)。但我怀疑您需要停机时间来转换您的表格布局以使用它们。这不是一项小工作。