主页 / dba / 问题 / 187044
Accepted
Hassan Baig
Asked: 2017-09-28 04:36:08 +0800 CST

从 PG 表中清理行后未释放的磁盘空间

  • 772

我知道空间不会突然释放（除非有TRUNCATE）。但是这个对我来说看起来很不正常。

postgresql (9.3.10) 表在删除 70% 的记录后没有释放空间的原因是什么？

我有一张桌子叫user_sessions_session. 顾名思义，它为 Web 应用程序的每个用户存储会话数据。

它的原始大小是：

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

此后，我删除了 3 个月前的所有用户会话。这是表中的大多数行。这是 3 天前的事。我刚刚再次检查了表格大小，这就是我所看到的：

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

此外，select * from pg_stat_activity where query like 'autovacuum:%';表明此时没有进行吸尘。

顺便说一句，我之前在同一张会话表上也遇到过同样的问题——这不是一次性的。

万一这很重要，这是我用来获取表大小的 SQL（user_sessions_session 在列表中显示为第 1 位）：

SELECT                                                         
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
postgresql postgresql-9.3

1 个回答

  1. Best Answer

    从文档中，

    在 PostgreSQL 中，行的 UPDATE 或 DELETE 不会立即删除该行的旧版本。这种方法对于获得多版本并发控制（MVCC，参见第 13 章）的好处是必要的：行版本在其他事务仍然可能可见时不能被删除。但最终，任何事务都不再对过时或删除的行版本感兴趣。然后必须回收它占用的空间以供新行重用，以避免磁盘空间需求的无限增长。这是通过运行来完成的VACUUM。

    完全没有。绝不。没有例外。每当您删除一行时，您实际上将其标记为对您的快照不活动。由先前事务启动的其他快照仍然可以看到它。这意味着它仍在磁盘上。

    行不存储在磁盘上它们自己的文件中。为了删除该行，即使您需要，您也必须在没有该行的情况下重写存储在磁盘上的文件。DELETE不这样做，在正常情况下这很好，因为事务不可见的死行可以通过简单的内部VACUUM回收。在被标记为重用之后，新数据可以存储在堆中。

    所以本质上，只有在内部导致表的堆被重写的操作才能回收空间。这些操作是：

    • CLUSTER
    • VACUUM FULL
    • TRUNCATE
    • ALTER TABLE（重写表格的表格）

    所有这些操作都需要一个ACCESS EXCLUSIVE锁。而且，TRUNCATE是这样的大锤，它甚至违反了 MVCC。

    @EvanCarroll：从技术上讲，即使在这里删除了大量行之后，我实际上也可以用完磁盘空间？– Hassan Baig 1 分钟前

    是的，真的，技术上和其他一切，但让我们对上述内容提出一个特别的警告。如果您DELETE有一堆行并提交，它们会被标记为不活动。当您VACUUM回收表的堆空间时。从那时起，要耗尽磁盘空间，您可以

    • 用尽该表堆上回收的空间，因为 PostgreSQL 只会在该空间中存储东西。
    • 或者，用尽不同表的堆。

    但是，是的，当您耗尽磁盘空间时，可能会发生非常糟糕的事情。而且，它们会发生在每个MVCC 数据库供应商身上。简单地说，不要运行磁盘空间。

    • 18

相关问题