pg数据库性能隐形杀手表膨胀

xbsaki

已于 2024-07-03 17:47:39 修改

阅读量1.4k

点赞数 28

文章标签：数据库

于 2024-07-02 23:08:24 首次发布

本文链接：https://blog.csdn.net/n2_bnxf/article/details/140137775

版权

在数据库管理中，表膨胀是一个常见但容易被忽视的问题。

什么是表膨胀？

表膨胀指的是数据表中存在大量无用或过时的数据，导致表的物理大小远超实际需要。这通常由频繁的数据更新、删除操作引起，数据库未能及时回收释放的空间。

比如，公司一直保持着大约1000名员工。在5年的时间里，可能有500名新员工加入，500名老员工离职。理论上，表中应该只有1000条记录。但实际上，由于删除的记录占用的空间并没有立即被回收，表的物理大小可能会增长到存储1500条记录的大小。

表膨胀的危害

存储需求增加，膨胀的表占用更多磁盘空间，增加存储成本。
查询性能下降，数据库需要在更大的数据集中搜索，导致查询执行时间延长。
表变大后相应的备份恢复时间延长。
索引效率降低，表膨胀也可能导致索引结构效率下降。
系统资源消耗增加，处理膨胀的表需要更多的CPU、内存和I/O资源。
数据碎片化，表膨胀可能导致数据碎片化，进一步影响性能并增加数据库管理的复杂性。
总而言之，就是性能变差了。

应对策略

简单有效策略1

定期监控，及时清理或重组表。使用数据库工具定期检查表的大小和膨胀程度，对膨胀严重的表进行重组或重建（当然要在业务不忙的时候操作）。

--1. 监控表大小
SELECT pg_size_pretty(pg_total_relation_size('mytable_name'));

--2. 清除表的膨胀
VACUUM FULL mytable;
VACUUM (PARALLEL 4) mytable;--或者使用并行VACUUM

--3. 使用CLUSTER重组数据
CLUSTER mytable USING mytable_pkey;--mytable_pkey可以是主键，唯一索引
pg_repack -t mytable; --或者使用pg_repack在线重组表

执行 VACUUM FULL mytable 时，数据库会进行以下操作

创建一个新的物理文件。
将mytable中的有效数据复制到这个新文件中。
删除旧的文件。
将新文件重命名为原来的表名。

其实pg会自动VACUUM（Autovacuum守护进程），可以通过pg 配置表查看Autovacuum的设定参数。

SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%';

/*
主要查看下面 4 个参数

autovacuum_vacuum_scale_factor
控制触发VACUUM操作的已更改行百分比，决定何时运行Autovacuum以回收已删除或更新行的空间

autovacuum_vacuum_threshold
设置触发Autovacuum对表进行清理的最小更新或删除行数，与autovacuum_vacuum_scale_factor一起决定何时应运行Autovacuum

autovacuum_vacuum_cost_delay
设置Autovacuum使用的成本延迟值，控制Autovacuum可以使用的CPU和I/O资源，以避免影响常规数据库操作

autovacuum_max_workers
设置同时运行的Autovacuum工作进程的最大数量，决定Autovacuum执行维护任务时使用的并行度
*/

对于大型表频繁删除/更新，自动VACUUM可能跟不上，这就需要手动VACUUM回收空间

VACUUM、CLUSTER、pg_repack比较

VACUUM 这种方式只会回收已删除或过时数据占用的空间，它将表的物理大小减小了。对查询性能有提升，但不直接。
而CLUSTER和pg_repack会物理重新排序表中的行，使其与指定索引的物理存储顺序匹配。重组后相关数据在物理上更接近，所以可以提高查询性能，但它不直接释放空间。
因此，使用CLUSTER和pg_repack优化表结构以提高查询性能，而VACUUM用来空间管理和维护。
另外，CLUSTER会在操作期间锁定表，可能影响正在进行的操作。pg_repack允许在线重组，对正在进行的操作影响较小。VACUUM对正在进行的操作影响较小，数据量大时耗时就很长。千万在不影响业务情况进行操作！