不可忽视的PG表膨胀优化

---

案例

先来看一例公有云服务登录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. 表膨胀危害是什么？

1. 空间持续上涨，影响查询效率。当dead tuple到达某一个临界点后，需要执行一个高额代价的vacuum full清理膨胀表，但vacuum full又是AccessExclusiveLock，8级锁，会阻塞一切访问，意味着在完成清理重组之前，都无法访问该表。
2. 扫描的效率变低，即使所有记录都是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会做大概如下几件事情：

1. 清除UPDATE或DELETE操作后留下的"死元组"
2. 跟踪表块中可用空间，更新free space map
3. 更新visibility map，index only scan以及后续vacuum都会利用到
4. "冻结"表中的行，防止事务ID回卷
5. 配合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
1
2
3
4

• 手动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