在PostgreSQL中删除临时文件

Question

我有一个大约800GB的数据库。当我尝试运行一个对某些变量进行分组并聚合结果的查询时，它在运行几个小时后停止了。Postgres提示磁盘空间已满。在查看统计数据后，我发现数据库有大约400GB的临时文件。我相信这些临时文件是在我运行查询时创建的。我的问题是如何删除这些临时文件。此外，我应该如何避免这样的问题-使用游标或for循环以不一次处理所有数据吗？谢谢。

我正在使用Postgres 9.2

- let_there_be_light

---

你是如何“查看统计数据”的？你所说的“临时文件”是什么？临时表在会话结束时被删除，因此关闭当前会话并打开新会话即可解决问题。 - Patrick

以上内容可能应该移动到答案中。 - d1ll1nger

最有可能的原因是（无意中的）笛卡尔积。 - joop

Answer 1

在查询执行期间创建的base/pgsql_tmp中的临时文件将在查询完成时被删除。不应该手动删除这些文件。
这些文件与临时表无关，它们用于存储大型哈希或排序操作的数据，以便它们不会适合于work_mem。

确保查询已完成或取消，尝试连续两次运行CHECKPOINT并查看文件是否仍然存在。如果是，则表示存在错误。当PostgreSQL服务器因磁盘空间用尽而崩溃时会出现此问题。

如果你真的有旧文件未能自动删除，则可以安全地手动删除它们。但在这种情况下，建议向PostgreSQL报告问题。

如果你的执行计划需要对大型结果集进行排序或哈希处理，那么无法避免生成大型临时文件。在这种情况下，游标无法帮助你解决问题。我猜你使用"for-loop"指的是将处理从数据库移动到应用程序代码-这通常是一种错误，并且只会将问题从数据库移动到另一个效率低下的地方。

改变查询方式，使其不必对大型结果集进行排序或哈希处理（使用EXPLAIN检查）。我知道这听起来并没有太大帮助，但没有更好的方法。你可能无论如何都必须这样做，否则运行几个小时的时间对你来说是否可以接受？

- Laurenz Albe

---

我今天学到了，在PostgreSQL14之前，PostgreSQL只在启动时清除pgsql_tmp。如果有人遇到这个问题，可以通过重新启动数据库来清除pgsql_tmp。 - Yavuz Selim

@YavuzSelim 注意，这只有在服务器崩溃后才可能相关，否则不会留下任何临时文件。 - Laurenz Albe

@AlexiTheodore 任何早于您最早的交易的文件都可以删除。例外情况是，如果您正在使用“WITH HOLD”游标，则其会保留事务。 - Laurenz Albe

@LaurenzAlbe 感谢您的信任和帮助。我本来就是这么想的，但不想冒险。以后参考一下，有没有办法获取正在使用的临时文件列表（v13），以消除所有可能的疑虑？ - Alexi Theodore

@AlexiTheodore 我不知道。 - Laurenz Albe