不可忽视的PG表膨胀优化

最新推荐文章于 2025-07-06 16:34:07 发布

SpringXCW

最新推荐文章于 2025-07-06 16:34:07 发布

阅读量702

点赞数

CC 4.0 BY-SA版权

分类专栏：运一点点维文章标签：数据库 postgresql

本文链接：https://blog.youkuaiyun.com/qq_38871652/article/details/133064700

运一点点维专栏收录该内容

7 篇文章

订阅专栏

我是一个目录

案例
1. 什么是表膨胀？
2. 表膨胀危害是什么？
3. 表膨胀是怎么产生的？
4. 如何优化膨胀表？

案例

先来看一例公有云服务登录web页面端卡住案例：
故障现象：WEB端登录非常缓慢，需要耗时5~10分钟才能登录，登录成功之后，就不卡了；
排查过程：网络正常，服务器资源正常，数据库层发现慢SQL
一条简单的查询select 居然耗时了150s
故障原因：根据慢SQL定位到功能点是Web端在登录时权限校验用户组织权限表t_pm_user引起的缓慢，查询该表数据量很小，只有1600行，查询表大小，发现表居然有4GB左右；表膨胀率达1526倍。
表中死元组过多，现在就明确了问题就是该表膨胀导致的。经过了解业务发现：客户配置了每小时一次人员增量同步，每次同步都update表，由于同步频率高，导致表膨胀厉害。

解决办法:
vacuum full 表并设置该表的回收阈值，并反馈研发优化这个功能点

1. 什么是表膨胀？

表膨胀是指表的数据和索引所占文件系统的空间，在有效数据量并未发生大的变化的情况下，不断增大。表膨胀不仅浪费了大量的磁盘空间，甚至某些查询场景下，导致表扫描耗时长，影响查询语句效率。就如上述例子中的表t_pm_user，仅有1600多行数据，但是表对应的物理文件已经4GB。

2. 表膨胀危害是什么？

空间持续上涨，影响查询效率。当dead tuple到达某一个临界点后，需要执行一个高额代价的vacuum full清理膨胀表，但vacuum full又是AccessExclusiveLock，8级锁，会阻塞一切访问，意味着在完成清理重组之前，都无法访问该表。
扫描的效率变低，即使所有记录都是dead状态，PostgreSQL的顺序扫描也会扫描对象所有的老版本，直到执行vacuum将dead tuple的记录删除；

3. 表膨胀是怎么产生的？

PG数据库的表膨胀是基于MVCC的存储机制：

插入很简单，就是将元组插入到页面的空闲空间中；
删除则是将元组标记为旧版本，但是即使这个旧版本对所有事务都不可见了，这个元组占用的空间也不会归还给文件系统。
UPDATE相当于DELETE + INSERT，等于是占用了两条元组的位置，类似DELETE，旧版本的元组依然占用着物理空间。

很明显，如上图所示，在一通增删改（insert、update、delete）操作之后，页面上的旧版本元组（dead tuple）势必是占有一定比重的，这就导致了物理文件大小明显高于实际的数据量。
为此，PostgreSQL引入了vacuum的机制，去清理那些不再需要的死元组。
autovacuum;
vacuum tablename;
vacuum full tablename;

普通的vacuum会做大概如下几件事情：

清除UPDATE或DELETE操作后留下的"死元组"
跟踪表块中可用空间，更新free space map
更新visibility map，index only scan以及后续vacuum都会利用到
"冻结"表中的行，防止事务ID回卷
配合ANALYZE，定期更新统计信息

可以看到，普通的vacuum只是清理死元组,"腾出"空间，在今后有新数据插入时，将优先使用该空间，直到所有被重用的空间用完时，再考虑新增页面。
在这里插入图片描述
如上图所示，普通vacuum只是标记了dead tuple，并未重组页面，导致上图3个页面使用率特别低，每个页面仅存放1个tuple，而且在某些情况下，每个页面的空闲空间又不是固定大小的，所以如果要利用这些空间空间，就需要遍历一遍数据页面来找到它们，这样会造成比较大的I/O开销，表膨胀越大，开销就越大。

因此，PG就引入vacuum full这个操作，vacuum full会对表进行重组，也就意味着表的oid会变，所以不能我们在日常操作中，因此它需要lock table，而且要定位表的oid的时候，不能通过pg_class的oid来找，得通过pg_class的relfilenode来找，这样才精确。而且，vacuum full最大会占据原来磁盘空间的两倍，所以请预留好磁盘空间。
在这里插入图片描述
既然已经了解了PG的膨胀原理，PG数据库也引入了autovacuum，为什么还是会有膨胀表存在呢？有以下几种可能性导致膨胀表不能被清理：

autovacuum不给力
服务器IO差
数据库长期存在长事务,数据库中存在长SQL或带XID的长事务。之后产生的死元组都无法被清理
autovacuum launcher process 的调度周期太长
存在失效的复制槽,复制槽通常用于逻辑复制、CDC和物理复制等场景下
开发游标未及时关闭,游标未关闭会导致backend_xmin一直持续到事务结束，产生的死元组无法回收；

4. 如何优化膨胀表？

步骤1：获取膨胀表及膨胀索引数据
使用上面这2个视图可以获取当前数据库里的膨胀表及索引情况；可以根据膨胀率进行筛查，及时处理优化膨胀表。
步骤2：处理表膨胀
常用手段：

使autovacuum加快速度干活
使用场景：根据服务器配置调整PG参数，加快autovacuum效率
相关参数默设置参考：
autovacuum_max_workers：=3
一次可以运行的autoacuum进程数量
autovacuum_vacuum_cost_limit = -1 (So, it defaults to vacuum_cost_limit) = 200
(清理作业总成本控制)
autovacuum_vacuum_cost_delay = 20ms
(当一个清理工作达到autovacuum_vacuum_cost_limit指定的成本限制时，autovacuum将休眠数毫秒)
vacuum_cost_page_hit = 1
(读取已在共享缓冲区中且不需要磁盘读取的页的成本)
vacuum_cost_page_miss = 10s
(获取不在共享缓冲区中的页的成本)
vacuum_cost_page_dirty = 20
(在每一页中发现死元组时写入该页的成本，清理成本)
监控长事务
单独小表设置vacuum阈值
使用场景：数据变化快、增、删、改频繁的小表；
设置：例如：表T被更新的元组数阈值超过1000或10%，并进行cost评估，低于1000，就执行 vacuum 和 analyze；

ALTER TABLE t SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0.1);--表大小的缩放系数；
ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000);---对 vacuum 执行 cost 进行评估，限制在1000，超过就延迟；
ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10);--延迟的时间即为10ms

手动vacuum full 表
根据上一步骤找出来的膨胀表，在非业务时段进行vacuum full tablename; 此操作需要lock table，8级别锁，针对特别大的表，评估是否需要做此类操作。对于大表，建议使用分区，可以加快vacuum的速度。
膨胀索引在线重建
在比较繁忙的系统或者大表上执行该语句的话，可以使用concurrently 参数：
create index concurrently idx_table_name_x1 on table_name(col_name);
插件处理
pg_repack
pg_sequeeze
优化应用程序设计
应用程序设计时，尽量避免如下：
- LONG SQL(包括查，增，删，改，DDL所有的SQL)
- 打开游标后不关闭
- 在不必要的场景使用repeatable read或serializable事务隔离级别
- 对大的数据库执行pg_dump进行逻辑备份(隐式repeatable read隔离级别的全库备份)，长时间不关闭申请了事务号的事务(增，删，改，DDL的SQL)，还包括2PC