当前位置：首页 > article >正文

【PostgreSQL数据库表膨胀的一些原因】

article 2024/9/23 12:30:57

PostgreSQL表膨胀的原因主要有两个：一个是垃圾数据，即dead tuple行数太多未及时清理，导致不能及时提供能重用的空间，二是数据页之间存在空闲空间。

1.表的填充因子设置

表的填充因子是个很神奇的东西，因为设置太大或者太小，都可能因不同原因引起表膨胀。

较低的填充因子（例如 70%）意味着每个数据页中会留出 30% 的空闲空间。这样可以减少页面因更新而频繁分裂的可能性。这通常会导致表的实际磁盘使用量增加，因为每个页面上的有效数据量较少。所以造成表的膨胀。
而表的 fillfactor 设置也可能会影响 VACUUM 的效果。如果 fillfactor 设置得过高，会导致表在插入新行时未能有效利用空间，增加了死元组的存在。（默认100）。

PostgreSQL 的表默认填充因子为 100，这意味着默认情况下，PostgreSQL 会尽量将每个页面填满数据，不留空间给未来的数据更新。假设一张表的填充因子设定为 70%。这样在 PostgreSQL 插入数据到页面时，会故意留下 30% 的空间空着，以便未来对现有数据行的更新。当表的填充因子更高（接近 100%）时，每个数据页面的空余空间更少，这可能导致数据行更新时空间不足，需要重新分配页面。

postgres=# alter table t1 set (fillfactor = 70);
ALTER TABLE

postgres=# SELECT
    relname AS table_name,
    reloptions
FROM
    pg_class
WHERE
    relname = 't1';
+------------+-----------------+
| table_name |   reloptions    |
+------------+-----------------+
| t1         | {fillfactor=70} |
+------------+-----------------+
(1 row)

2. VACUUM和VACUUM FULL本身机制

VACUUM有时候只是标记了空间为可用，但磁盘空间可能不会立即反映出来。

3. AUTOVACUUM参数不合理死元组不能及时清理

调整 autovacuum 配置参数，特别是 autovacuum_vacuum_scale_factor 和 autovacuum_vacuum_threshold。例如，降低 autovacuum_vacuum_scale_factor 的值，可以让 autovacuum 更频繁地运行。

4.一些原因导致vacuum后没清理死元组

主要有如下几种方式

失效复制槽
长事务导致
存在未提交的prepare事务
idle in transaction状态的事务
函数等内部结构涉及到表的访问
hot_standby_feedback参数问题
索引状态问题
表和索引的并发访问

可以参考这篇文章：https://blog.csdn.net/weixin_47308871/article/details/142226606?spm=1001.2014.3001.5502

5.表的统计信息问题

如果表的统计信息有问题，可能会影响 VACUUM 的效果。

6.maintenance_work_mem参数设置太小死元组不能及时清理

maintenance_work_mem 设置得太低可能会限制 VACUUM 的效率，因为VACUUM过程需要在maintenance_work_mem里缓存dead tuple的tupleid，如果太小则可能分多次清理，每次在maintenance_work_mem缓存满之后就会触发一次清理，除非之外还可能涉及到多次扫描索引的问题。正常情况下，每满一次，就会重新扫描一遍所有的索引。

v11-v13引入的一个GUC参数vacuum_cleanup_index_scale_factor,但是在v14取消了，对于大量insert，没有update、delete操作的表的vacuum，或者常规静态表的vacuum会快很多，因为不需要scan index了。

而v12版本也在表级别增加了vacuum_index_cleanup参数，可以在创建表的时候设置，也可以alter table设置。参数可以控制VACUUM在是否禁用索引清理的情况下运行，默认值为true。v12版本的VACUUM引入了一个新的选项INDEX_CLEANUP，可以跳过索引的垃圾回收。

除此之外PostgreSQL-17版本之前，maintenance_work_mem虽然可以设置很大，但是对于vacuum本身的使用，有一个1GB的最大值限制，也就是不管你设置的多大，最多一个vacuum能用到的最多也就1GB。这个在PostgreSQL数据库的文档里也有相应的记录。从PostgreSQL-17版本取消了这个限制。

tupleid为6字节长度。1GiB可存储1.7亿条左右dead tuple的tupleid。默认垃圾记录约等于表大小的20%时触发垃圾回收, 8.9亿条记录的表20%的垃圾即1.7亿条dead tuple,因此超过8.9亿, 该表的垃圾回收就可能要多次扫描index了。所以以这个方向来看的话，PostgreSQL单表不建议超过8.9亿条记录。

7.磁盘性能问题导致死元组不能及时清理

磁盘 I/O 性能问题或数据文件系统的配置也可能影响 VACUUM 的效果，如果磁盘性能不好，可能VACUUM的效率比较低，死元组不能及时清理。可以使用fio、iostat 或 vmstat来检查磁盘IO情况。

8.PostgreSQL 的事务 ID (XID) Wraparound

PostgreSQL 使用事务 ID (XID) 来追踪事务。长时间运行的事务或频繁的事务生成可能导致 XID Wraparound 问题，这会影响 VACUUM 的效果。如果 XID 接近其最大值，数据库会进行 VACUUM 来防止 XID Wraparound，但这个过程可能不会完全清理死元组。