为什么pg_stat_get_dead_tuples返回0时仍然浪费空间？

Question

使用Postgres 11.1，我一直在试图确定/估计表中“浪费”空间的数量，这些空间将通过真空填充来回收。我的计划是使用pg_stat_get_live_tuples (L)、pg_stat_get_dead_tuples (D)和pg_total_relation_size ( S )，然后估计浪费的空间为(D / (L+D)) *S。

然而，这似乎不起作用。在调查原因时，我做了以下设置：

CREATE TABLE sam_silly(txt TEXT);
INSERT INTO sam_silly VALUES('one');
INSERT INTO sam_silly VALUES('two');
INSERT INTO sam_silly VALUES('three');
INSERT INTO sam_silly VALUES('four');
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;
INSERT INTO sam_silly SELECT txt || '+' FROM sam_silly;

然后，我根据这些函数对空间进行了初步计算。结果如预期(有100万行，即2^20行)：

select pg_total_relation_size(c.oid) AS size, pg_stat_get_live_tuples(c.oid) AS live, pg_stat_get_dead_tuples(c.oid) AS dead
FROM pg_class c where relname='sam_silly';

• 47308800,1048576，0

然后我删除了一半的桌子：

delete from sam_silly where txt like 'one%' or txt like 'three%';

立即重复上面的查询，得到了我预期的结果：

• 47308800,524288,524288

一半的数据是活的，一半是死的。

然后我跑了“分析”，这就是事情变得奇怪的地方：

• 47316992,524288，0

空间略有上升，但现在没有死胡同了！

我也试过“真空”：

• 47316992,524264，0

变化不大。

最后，在真空充满之后，它的行为又如我所料：

• 23519232,524264，0

现在没有死的元组，也有空间已经恢复。

真正的数据库有自动真空，所以它可能会真空我感兴趣的表。但是看起来，空分会导致它将死元组的数量设置为0，尽管仍然存在大量的“浪费”空间。

在这种情况下，当pg_stat_get_dead_tuples返回零时，即使它还没有收回以前报道过的死元组所使用的空间，有什么方法可以找出/估计出在这种情况下“浪费”了多少空间？

与案例相关的确切版本：“PostgreSQL 11.1 on x86_64-pc-linux-gnu，由gcc (GCC) 4.8.5 20150623 (RedHat4.8.5-28)，64位汇编”

Answer 1

分析不排除死元组。它看起来像是在你进行分析的同时自动启动并移除了死元组，这就是为什么死元组计数下降到零的原因。如果您小心防止这种情况发生，您将发现分析不会重置死元组计数。

由自动真空释放的空间可供内部重用，可以使用pg_自由地图扩展查看。

select avail,count(*) from pg_freespace('sam_silly') group by 1 order by 1;
 avail | count 
-------+-------
 3,648 |    22
 3,680 | 1,232
 3,712 | 1,153
 3,744 | 1,749
 3,776 | 1,233
 3,808 |   360
 3,840 |    20
 4,384 |     1
(8 rows)

这张表的每一页都是半满的，可供内部重复使用。如果您不打算在不久的将来重新使用这个空间来创建新的元组(包括更新旧元组，从而创建旧元组的新版本)，这才是膨胀。

死元组计数的目的是让autovac发射器知道什么时候可以启动autovac。根据这一目标，它记录了死去但尚未消失的元组的数量。当它们被真空吸走时，真空就会向下计数。它并不是要向你展示它的膨胀，这就是为什么它在被迫达到这个目的时做得很差的原因。

如果您想查询膨胀，可以在https://wiki.postgresql.org/wiki/Show_数据库_浮肿查看建议。