由于pg的MVCC的实现机制与oracle存在较大差异,pg的undo空间和数据空间共用,导致数据空间膨胀严重,长时间不清理这种膨胀的垃圾数据,导致sql性能下降非常严重。
常规,数据库级调整
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsISPrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdsATOfd3bkFGazxCMx8VesATMfhHLlN3XnxCMwEzX0xiRGZkRGZ0Xy9GbvNGLpZTY1EmMZVDUSFTU4VFRR9Fd4VGdsYTMfVmepNHLrJXYtJXZ0F2dvwVZnFWbp1zczV2YvJHctM3cv1Ce-cmbw5yMhNDNykjY5Y2YwgTY4kzY0YDOzQDZlVDOxU2YhVzNm9CXxMzLcdDMxIDMy8CXn9Gbi9CXzV2Zh1WavwVbvNmLvR3YxUjL2M3Lc9CX6MHc0RHaiojIsJye.png)
常规调整
对于频繁更新的表,设置填充因子fillfactor
语法:
若是分区表,需在分区子表设置。
alter table table_xx set (fillfactor=80);
功能:
让每个数据块预留一定的空间用于记录的更新,也可以提升update的效率。
缺点:
Insert效率有所降低,同时加大磁盘开销。
对于超大表,调低vacuum触发阈值
alter table table_xx set (autovacuum_vacuum_threshold=100);
或
alter table table_xx set (autovacuum_vacuum_scale_factor =0.001);
使超大表更积极触发vacuum机制。
定时任务,通过工具周期性维护
推荐2款工具,均为开源软件,建议通过定时任务在业务闲时,每周维护一次。
以下为2款工具的详细对比数据:
垃圾数据回收工具对比
从工具稳定性和易维护角度,推荐使用pg_repack。
pg_repack
wget https://github.com/reorg/pg_repack/archive/ver_1.4.5.zip
make USE_PGXS=1
make install USE_PGXS=1
pg_squeeze
git clone https://github.com/cybertec-postgresql/pg_squeeze.git
pgcompacttable
github已经不维护,不建议使用