POSTGRESQL 1000行数据的表，110G的存储没有dead tuple 引出的问题

来源：AustinDatabases

群里有一个同学问了一个问题，没有死元祖，表的行数1000行，存储容量110G

我们来完成这个小目标

PG 13 版本，然后我们灌入1000万的数据

然后我们，添加索引，删除数据99%，剩下1000行，然后在进行vacuum 的操作，最后看这表，还是 1710MB .提问同学的“ 1 个亿的小目标”达到了，也就是说我们还原的，那位客官所提到的部分问题。

然后我们继续往里面灌入数据，只是插入，不进行任何的UPDATE 操作，然后我们不断查看表空间的扩展情况， 并未如我们所愿插入的数据使用了原有我们认为可以利用的数据清理后的空间。

那么提出的问题是，在什么状态下，PG 在数据的操作中，会出现无法利用原有空间的情况。

1   原始插入的数据小，UPDATE 后的数据大，并且填充因子设置上并未考虑此问题的情况下，会产生此问题。但基于PG 支持TOAST 功能，所以在处理这个问题方面上，超大的字段，并不会引起页面无法重复利用的问题。

我们创建一个表，并且在表中塞入大型的数据

test=# create table test (id int,name text);

CREATE TABLE

test=# SELECT lp,lp_off,t_ctid FROM heap_page_items(get_raw_page('test',0));

ERROR:  block number 0 is out of range for relation "test"

test=# insert into test (id,name) values (1,'677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888679999999999997777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888677777788888888888888888888888888888888888');

从上面看，我们的数据占用了两个页面不到的样子，那么现在我们针对 ID  1  2 的数据进行UPDATE，并且每个更新了2次 但是在UPDATE 中的数据量明显比之前插入的数据量要大。

在我们进行UPDATE 后，整体的分布变化了

我们在连接插入了新的数据后，相关的第一个页面的空出的位置被重复了利用。

那么我们在删除 1  2号记录后，我们在重新插入 1  2 号记录

数据分别在第一页和第二页插入，说明删除的数据空间并不能马上被重复利用。而我们继续插入数据，则此时，发现空出的空间不能被使用，即使你做了vacuum 的工作，新的数据已经创建了新的页面。

为什么在删除了数据，并且进行了VACUUM 还不能使用那些空间，还要占据新的空间？在重新对表进行vacumm full后，整体

这里关于无法利用数据页面空间的主要常规解释有如下的部分：这里简单的在重复一下

1   长事务

SELECT pid, datname, usename, state, backend_xmin, backend_xid

FROM pg_stat_activity

WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL

ORDER BY greatest(age(backend_xmin), age(backend_xid)) DESC;

2   逻辑复制槽，或自建的复制槽在使用中接收端失效或出现问题的

SELECT slot_name, slot_type, database, xmin

FROM pg_replication_slots

ORDER BY age(xmin) DESC;




3   在PG中没有提交事务，或者OPT的，如何查询IOPT的方式在下方

SELECT gid, prepared, owner, database, transaction AS xmin

FROM pg_prepared_xacts

ORDER BY age(transaction) DESC;

4  主从库中从库开启了hot_standby_feedback=on 的情况  为了减少复制冲突，可以在备用服务器上设置hot_standby_feedback = on。然后备用服务器将把最旧的打开事务告知主服务器，主服务器上的VACUUM将不会删除备用服务器上仍然需要的旧行版本。

SELECT application_name, client_addr, backend_xmin

FROM pg_stat_replication

ORDER BY age(backend_xmin) DESC;

除此以外，还有什么情况下是会产生PG 的表虽然被 VACUUM 了，但是还是空间重复利用率低。

1  delete 的数据，比插入的数据占用的空间小，导致新插入的数据无法放置到已经空出的空间中，只能新开空间。当然处理这类问题，可以对表的填充因子进行变化，但是问题是实际上这是背着抱着的问题，我一开始设置的填充率低，则后期UPDATE ,DELETE 后，新的数据库可以进入的可能性高，但是一开始你因为设置的填充率的问题，会导致一开始占用的磁盘空间高，所以是背着抱着的问题。但是还是建议针对经常UPDATE 的表，DELETE 的表，请将你的填充率可以降低到 85%  80%

2  BUG 问题

大部分的问题都与 old_snapshot_threshold 参数有关，这个参数开启后，有运行 select ... for update 将所有的 vacuum操作都卡死的情况（在PG 13)

以及在早期的版本中开启后，无法在删除表中大量数据后，还无法进行磁盘空间归还给操作系统的问题。所以我们建议不要针对old_snapshot_threshold 参数进行设置，默认就好。

Thread: BUG #17196: old_snapshot_threshold is not honored if there is a transaction : Postgres Professional

Re: [HACKERS] Document that vacuum can't truncate if old_snapshot_threshold >= 0 (mail-archive.com)

https://github.com/EnterpriseDB/zheap/blob/master/src/backend/access/table/vacuumblk.c

具体上面的代码可以从上面的连接找到。

另外在我测试中，使用了PG14.7 的版本，在打开了参数后，并设置了超过1分钟的配置后，曾发生过一次，删除数据，VACUUM 中明明发现有死的元组，但无法进行回收的问题。（没有在操作中通过事务，霸占这个表的行，导致VACUUM 无法回收，只是就打开这个参数，然后删除数据，然后就无法回收），但后面在操作就无法复现了。（所以把这个归为一个未知不确定的问题）

最后，删除数据无法空间回收，还是可以通过heap_page_items 函数来分析你整体最后页面中是否有数据，如果有，那么空间是无法释放给系统的。

https://dba.stackexchange.com/questions/320395/significant-size-differences-between-size-calculations-for-tables-in-postgresq

另外此次的学习中，还发现了一个较好的分析表磁盘空间利用的语句，可以从上面的网址获得，但是不建议在大表和生产繁忙的系统中运行，有可能有性能风险。