删除 PostgreSQL 中的行并释放空间

Question

我不太熟悉PostgreSQL。

使用了简单的命令。

DELETE FROM table_name where some_condition;

涉及数千行数据。但是在执行该命令后，磁盘空间甚至变得更小了。

有什么想法出了问题吗？我启用了autovacuum = on，并尝试执行“VACUUM FULL;”，但这消耗了我整个磁盘空间。

我想做的事情很简单。删除行并释放空间。涉及大量空间，而机器上剩余的空间并不多。有方法可以解决吗？

- MichaelRazum

Answer 1

在现代Postgres数据库中，除非你确切知道自己在做什么，否则应该运行autovacuum。但是，除了极少数情况下，autovacuum不会收缩表。它会安排定期的VACUUM和ANALYZE作业，但永远不会执行VACUUM FULL，因为这会在处理的表和索引上获取独占锁。通过清理死元组（以及其他各种好事），VACUUM可以帮助避免表和索引膨胀，从而为同一对象的未来写操作释放空间 - 但尚未将空间返回给操作系统。

你写道：

试图执行'VACUUM FULL;'，但这消耗了我整个磁盘空间。

你的意思是临时？因为这就是VACUUM FULL的作用。但是当它完成后，表和相关索引将被减小到最小值 - 这通常是不可取的，除非你急需磁盘空间或者行从不更新。

所以，VACUUM FULL 是适合你的正确工具，除非你已经没有磁盘空间 - 最好一开始就不要这样。所以你需要创造一些余地让它发挥作用。

还有一个社区工具pg_repack，可以在不使用独占锁的情况下执行与VACUUM FULL相同的操作。但是它也需要一些可用的磁盘空间才能正常工作。相关答案中有更多细节：

• VACUUM returning disk space to operating system

---

要删除表的所有行并立即释放磁盘空间，请使用TRUNCATE。它实际上会在磁盘上写入一个新文件并删除旧文件。这也是为什么它通常不能与DB上的并发加载一起使用。

TRUNCATE table_name;

- Erwin Brandstetter

---

“TRUNCATE（截断）......不能在数据库上进行并发加载”？这是不是意味着 TRUNCATE 在 MVVC（多版本并发控制）方面存在问题？ - TmTron

@TmTron：是的，就是那样。 - Erwin Brandstetter

Answer 2

VACUUM FULL 的工作原理是创建每个表的一个新版本并复制所有数据。在新表完成之前，旧表不会被删除，因此 VACUUM FULL 操作在处理时会暂时增加磁盘空间。请参阅 PostgreSQL 文档中的这个说明（强调是我的）：

提示：当一个表包含大量死行版本（由于大规模更新或删除活动而导致）时，普通的 VACUUM 可能不够满意。如果您有这样的表，并且需要回收它占用的额外磁盘空间，则需要使用 VACUUM FULL，或者 CLUSTER 或 ALTER TABLE 的一个重写表变体。这些命令将为表重写一个全新的副本，并为其构建新索引。所有这些选项都需要排他锁定。请注意，它们还会暂时使用额外的磁盘空间，大约等于表的大小，因为旧的表和索引副本只有在新副本完成后才能释放。

另一方面，普通的 VACUUM 只会从数据库文件中修剪已删除的行和索引条目。

如果您首先运行常规的 VACUUM，然后再尝试运行 VACUUM FULL，可能会更成功。如果您尝试在特定的表上运行 VACUUM FULL，也可能会更成功。如果您以正确的顺序运行它们，可能能够恢复足够的磁盘空间，使数据库中剩余的表可以运行 VACUUM FULL。否则，您需要找到一些方法来增加可用的磁盘空间，以便 VACUUM FULL 可以完成。

- Bacon Bits

---

有什么变通方法，或者在执行 SQL 删除操作后为什么磁盘空间没有被释放？据我所了解，如果我想运行此命令，我需要至少与数据库消耗的空间一样多的空间。将删除语句分块，比如分成 1000 个部分，然后运行 VACUUM 是否有意义？ - MichaelRazum

出于设计考虑，磁盘空间不会立即释放。我在上面链接的同一页中提到：“这种方法是为了获得多版本并发控制（MVCC，请参见第13章）的好处而必需的：在行版本仍然可能对其他事务可见时，不能删除该行版本。” - Bacon Bits

只有 VACUUM FULL 需要磁盘空间。VACUUM 不需要磁盘空间。我猜批量删除后再定期运行 VACUUM 在您当前的情况下并没有帮助，但也许有些东西我没有考虑到。我不是 PostgreSQL 的专家。 - Bacon Bits

在PostgreSQL中，有一个自动清理守护进程，可以定期清理过时的已删除行和索引。建议启用它。我不知道它是否默认启用。 - Bacon Bits

我明白了。那么，如果启用了它，我只需等待即可。有没有办法找出守护进程是否正在运行以及它现在在做什么？ - MichaelRazum

运行 SHOW autovacuum; 命令可以告诉你是否正在运行。我不记得它是否会将活动记录到文本文件中。通过查询 SELECT * FROM "pg_catalog"."pg_stat_all_tables"，您可以查看表上次被访问的时间。其中有一些列，如 last_autovacuum、last_autoanalyze、autovacuum_count 等。 - Bacon Bits