PostgreSQL - 如何从500GB的数据库中删除大量数据/自动清理？

Question

我需要删除大约80%的500Gb PostgreSQL数据库。

到目前为止，我已成功运行了一条删除约50Gb行的命令，并在继续操作之前暂停。（这可能需要很长时间，可能需要一个小时）

我注意到，在删除约50Gb数据后，没有额外的磁盘空间被释放，但是当我运行“htop”时可以观察到一些内存密集型的Postgres进程。如果我正确地假设这是由于死行导致的，那么在释放磁盘空间之前需要进行清理吗？

第二部分问题是，如果我对第一部分的理解不错，是不是最好删除所有行，然后允许自动清理发生？似乎自动清理（或其他密集的后台进程）已经在我有机会继续我的行删除命令列表之前自行启动。我只需继续还是应该优雅地告诉它先停止？

- corinjg

---

自动清理（autovacuum）不执行“vacuum full”操作 - 因此它不一定会释放已删除元组的磁盘空间，但它会将它们标记为可重用。是的- 这是正常情况。不- 您不应该关心它- 继续您的工作，让自动清理执行其任务即可。 - Vao Tsun

Answer 1

大量删除后，自动清理程序autovacuum会运行。这是设计上的考虑，并不会干扰你继续删除更多的行。

虽然autovacuum会释放表中的死空间，但它不会将空间返回给操作系统。相反，它仍然作为表中的空闲空间，可用于未来的插入。

如果您想缩小表，请对其运行VACUUM（FULL），但请注意，这会重写该表，因此它会暂时使用额外的存储空间并阻止所有并发活动在该表上进行。

如果您经常需要进行大量删除，请考虑对表进行分区。这可以使批量删除变得容易。

- Laurenz Albe

Answer 2

更好的解决方案是使用TRUNCATE表格。

在我的情况下，我删除了一个占用大量磁盘空间的特定行，但这是太多数据让VACUUM在合理的时间内清除。

最终我复制了这个表：

CREATE table dupe_table AS (SELECT * FROM table);

截断原始表格：

TRUNCATE table

最终将数据移回：

INSERT INTO table(column1, column2, column3)
SELECT column1, column2, column3
FROM dupe_table

注意：如果在创建副本表和清空原始表之间发生事务，您可能会丢失数据。

- ricks