PostgreSQL 为什么vacuum空表却无法回收空间？

开源软件联盟PostgreSQL分会 2022-01-08

1766

文章转载自公众号：晟数学苑

在PostgreSQL的表的空间管理中，有种类似Oracle “高水位”的概念。即如果一张表尾部的page为空，那么可以通过vacuum去回收这部分的空间。

因为在PostgreSQL中索引指向的是每行数据的ctid,这个是物理地址,假如我们删除page中在最前面的数据,这个时候如果表大小变小的话,是不是就意味着后面的数据的ctid要往前移动,那此时就会出现一个问题,索引中key对应的ctid不准确了。

这里顺便分享个比较实用的脚本，我们都知道使用vacuum full进行磁盘空间回收时需要有2倍的磁盘空间，那么如果磁盘空间不足，而我们又需要去通过vacuum full清理空间该怎么办呢？

可以使用下列脚本：

其本质就是利用了我们前面说的这个原理，将文件末尾的行删掉重新插入到头部空闲的空间中，然后vacuum便会释放掉位于文件末尾的页，可以返还一部分的空间。

WITH a AS (
    DELETE FROM t
    WHERE ctid = ANY (
            SELECT
                ctid
            FROM
                t
            ORDER BY
                ctid::text DESC
            LIMIT 10000)
    RETURNING
        *)
    INSERT INTO t
    SELECT
        *
    FROM
        a;

VACUUM t;

复制

那么是不是只要尾部空间为空，vacuum就一定能回收呢？

下面是一张空表，但是当我们进行vacuum后，可以发现表的大小并没有变化。

bill=# delete from t4;
DELETE 1000000
bill=# select count(*) from t4;
 count
-------
     0
(1 row)

bill=# vacuum t4;
VACUUM
bill=# \dt+ t4
                                 List of relations
 Schema | Name | Type  | Owner | Persistence | Access method | Size  | Description
--------+------+-------+-------+-------------+---------------+-------+-------------
 public | t4   | table | bill  | permanent   | heap          | 65 MB |
(1 row)

复制

这是因为我们设置了old_snapshot_threshold参数的原因，当我们设置了该参数后，使用vacuum并不会truncate尾部的空间。

这个是通过should_attempt_truncation函数去进行判断的：

这里将判断条件修改下，便可以不受该参数的影响了：

bill=# show old_snapshot_threshold ;
 old_snapshot_threshold
------------------------
 3h
(1 row)

bill=# delete from t3;
DELETE 1000000
bill=# vacuum t3;
VACUUM
bill=# \dt+ t3
                         List of relations
 Schema | Name | Type  | Owner | Persistence | Size  | Description
--------+------+-------+-------+-------------+-------+-------------
 public | t3   | table | bill  | permanent   | 24 kB |
(1 row)

复制

PostgreSQL12之后我们可以通过在表上设置vacuum_truncate参数来控制vacuum是否尝试截断此表末尾的任何空页，当然这个参数的设置并不会影响should_attempt_truncation函数的判断，即如果你使用了old_snapshot_threshold，那么在表级别设置vacuum_truncate也不会起作用。