PGSQL数据膨胀问题排查

背景

不知道从何时开始，数据库空载时的性能消耗越来越高，当业务高峰期，CPU 和内存都处于高负载的情况下，观看 AWS 的监控，发现负载空载时占用很高。

并且占用较高的 Top5 分为为：

autovacuum: VACUUM ANALYZE pg_catalog.pg_attribute
autovacuum: VACUUM ANALYZE pg_catalog.pg_type
autovacuum: VACUUM ANALYZE pg_catalog.pg_depend
autovacuum: VACUUM ANALYZE pg_catalog.pg_class
autovacuum: VACUUM ANALYZE pg_catalog.pg_index

由上面的 Top5 可以得知，目前数据库的负载较高，都是由于 PgSql 系统表的 VACUUM 导致的，但是，为什么会出现这种情况呢？我们一起来慢慢解决这个问题吧！

问题排查

排查方向

VACUUM 是 PgSql 的一种垃圾回收机制，主要用于清理数据库中的不再需要的行（也称为“死亡行”或“dead tuples”），并且它回收这些行占用的空间。VACUUM操作还更新数据库的统计信息，这对于查询优化器来说非常重要。

如果 VACUUM 太慢，则有可能有以下几个原因：

• 大容量数据：如果数据库非常大，VACUUM操作可能需要很长时间来完成，这会增加系统负载。
• 高事务量：在高事务量的系统中，频繁的更新、插入和删除操作会产生许多不再需要的行，导致VACUUM工作量增加。
• 不频繁的清理：如果长时间没有运行VACUUM，会导致数据库中积累大量的死亡行，需要更长时间来清理。

因为当前数据库集群都处于业务的低谷期，所以基本上没有啥负载，所以高事务量 可以排除，而数据库一直在频繁的执行清理任务，所以问题基本上可以锁定到大容量数据！

谁占用了空间？

因此，我们首先从数据表的空间占用上面进行问题排查！

我们可以使用 [[维护相关#数据表统计#数据表空间占用大小 | 数据表空间占用大小统计]] 相关 SQL：

SELECT  
    relname,
    PG_SIZE_PRETTY(PG_TOTAL_RELATION_SIZE(oid)) AS total_size,
    PG_SIZE_PRETTY(PG_RELATION_SIZE(oid)) AS relation_size,
    *  
FROM  
    pg_class  
WHERE  
    relname LIKE 'pg_%'
		AND relname NOT LIKE 'pg_toast_%'
ORDER BY  
    PG_TOTAL_RELATION_SIZE(oid) DESC  
LIMIT 100  
;

下面是返回的结果信息：

上面 pg_attribute 总大小（数据表+索引）为：32 GB，因此，基本上可以判断，数据库的负载变高，是由于 pg_attribute 系统表导致的！

pg_attribute

那么问题来了，pg_attribute 到时候是什么数据表呢？

以下是 AI 助手 Kimi 给出的解答：

在PostgreSQL数据库中，pg_attribute是一个系统目录表，它存储了关于表列的信息。每个表的列都有相应的行记录在这个表中，包括系统表和用户定义的表。

pg_attribute表是PostgreSQL中众多系统目录表之一，它们为数据库中的各种对象提供了元数据。通过查询这些系统表，你可以获得关于数据库结构和配置的详细信息。

简而言之，它是一张 pgsql 的元数据信息表，存储 pg 数据库中的各个表字段的定义信息。

理论上，一个数据库中的数据表即使再多，它的元数据信息也不可能超过 30 GB，因此，我们接下来的排查方向，是要找出为什么 pg_attribute 数据表这么大！

数据膨胀

在我将此问题在网络上进行搜索的时候，一个新的名词出现在我的眼前：数据膨胀

PostgreSQL 数据膨胀是指数据库中的数据文件大小超出了实际存储数据所需的大小。这可能会导致存储空间的浪费，并且可能影响数据库的性能。以下是一些可能导致 PostgreSQL 数据膨胀的原因以及相应的解决方法：

• 索引膨胀
  • 原因：随着数据的更新和删除，索引可能会变得“膨胀”，特别是对于B树索引。
  • 解决方法：定期重建索引可以减少膨胀。可以使用REINDEX命令来实现。
• 表膨胀
  • 原因：表中的数据更新和删除操作可能导致表变得碎片化，从而增加表的物理大小。
  • 解决方法：使用VACUUM FULL命令来压缩表并回收空间。但请注意，这将创建一个新的表并重置统计信息。
• 长事务
  • 原因：长时间运行的事务会锁定行，导致VACUUM不能回收空间。
  • 解决方法：避免长时间运行的事务，或者使用事务 ID 来限制事务的持续时间。
• 未及时清理删除的数据
  • • 原因：删除数据后，如果VACUUM没有运行，那么空间不会被回收。
  • 解决方法：定期运行VACUUM（或使用autovacuum）来清理不再需要的行。
• 系统表膨胀
  • 原因：系统表，如pg_attribute，可能会因为存储了大量的元数据而变得庞大。
  • 解决方法：审查系统表的使用，移除不必要的元数据。

通过对上面造成数据膨胀的原因进行排查，最后锁定了最重要的两条：

• 重复创建临时表：公司的很多业务涉及到 批量更新 ，为了使批量更新的速度更快，因此使用了 临时表方案 方案，重复执行 CREATE TEMP TABLE AS SELECT * FROM TABLE ，导致 pg_attribute 的数据一直处于上升的阶段。
• 长事务：数据库的数据需要实时同步至数据仓库，同步是采用的 Flink-CDC 实时同步至 StarRocks，Flink-CDC 在同步阶段，会默认打开一个复制槽，当复制槽的 WAL 日志未被订阅者消费的时候，会存在一个长事务！

问题源头

下面，我来进行一个复盘，来说明为什么 pg_attribute 为什么会发生数据膨胀！

业务系统时时刻刻在执行 CREATE TEMP TABLE 命令，因此 pg_attribute 的数据会频繁的进行数据的插入和删除，正常情况下，及时 PgSql 的 VACUUM 会自动将删除的数据从磁盘中移除。

但是，因为数据库的数据需要同步至 数据仓库 ，因此使用了 Flink-CDC 进行同步，Flink-CDC 会开启一个复制槽，正常运行的情况下， Flink-CDC 会实时消费复制槽中的 WAL 日志，如果 Flink-CDC 同步任务关闭，但是又未关闭复制槽，复制槽为了保证订阅者数据完整性，会自动记录 FLink-CDC 上一次消费的位置，方便 Flink-CDC 下一次重启，重新监听 WAL 日志。

为了保证 WAL 日志的完整性，因此 PgSql 需要开启一个长事务，防止 PgSql 数据库的 VACUUM 把数据从磁盘清除。

所以，pg_attribute 数据表的数据因为长事务的原因，一直未被 VACUUM，所以导致数据表一直处于膨胀阶段，而因为 pg_attribute 是系统表，假如执行 VACUUM FULL pg_attribute ，将会锁全库，因此即使当后续事务关闭了，pg_attribute 的空间也无法得到正常的释放！

解决方案

停止创建临时表

因为系统一直在创建临时表，所以系统一直在对 pg_attribute 表进行 VACUUM ，假如 pg_attribute 的数据不再更新，那么就不会触发数据库的 VACUUM 了！

此方法治标不治本，只是避免了频繁执行 vacuum pg_attribute，从而减小系统负载

执行 VACUUM FULL

执行 vacuum full pg_attribute，但是此方案会锁全库，因此执行的时候，必须停服执行，对业务的影响较大。