如何从PostgreSQL中删除大量行？

Question

我们在AWS RDS中托管了一张拥有很多行数据（1亿行）的表格。

1. 如果这个表格存在不间断的读/写/更新查询，如何高效地删除50%的行数据？
2. 如何每天删除1%的数据？

表格结构：

• created_at（创建时间）
• user_id（用户ID）
• 其他数据

我们曾尝试按天逐条删除数据，但这样会触发自动清理（autovacuum），导致队列增长。

- Alex Tonkonozhenko

---

将表进行分区，必要时删除“最旧”的分区。 - Panagiotis Kanavos

@PanagiotisKanavos 我已经在问题中添加了表结构。我需要查询用户的所有时间的所有数据。我能用分区实现这个吗？此外，我现在有这个表，但我无法为当前数据添加分区，只能为新数据添加分区。 - Alex Tonkonozhenko

如果您的系统无法处理自动清理，则可能是配置不正确或资源不足。无论是什么触发了自动清理。 - jjanes

@jjanes 或者说它有很多数据和很多流量。1亿行是很多数据。如果您跟随分区文档的链接，您将看到其中一个主要好处是避免了vacuum操作。如果您分离一个分区而不是删除它，几乎没有停机或阻塞，因为这基本上是一个元数据操作。 - Panagiotis Kanavos

AWS关于Postgres 11的公告介绍了Postgres RDS分区的简短教程以及其一般性好处。Postgres 12的公告解释了最新版本中如何改进分区修剪（在运行查询时避免无关分区）。 - Panagiotis Kanavos

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亿行数据需要执行250万个批处理。但是没关系，这就是编程存在的原因。这种批处理方法在我工作的地方非常有效，适用于最初设计不易进行清理的表格。

一旦删除了大量旧记录，每天保持更新就容易多了。

然而，如果您每天需要删除大量行，则应使用分区（如评论中所提到的）。但是我怀疑您需要停机时间来转换表格布局以使用它们。这不是一个小任务。

- O. Jones

---

这取决于在 created_at 上是否有索引，以避免全表扫描。虽然有几种类似的处理技术 - 例如将要删除的主键复制到一个临时表中，并将其与 USING 一起使用。不过这仍然很昂贵，特别是在 RDS 上。 - Panagiotis Kanavos