postgresql 数据库 INSERT 或 UPDATE 大量数据时速度慢的原因分析

postgresql 数据库 INSERT 或 UPDATE 大量数据时速度慢的原因分析

前言

最近这段时间一直使用pg 数据库插入更新大量的数据，发现pg数据库有时候插入数据非常慢，这里我对此问题作出分析，找到一部分原因，和解决办法。

一 死元祖过多

提起pg数据库，由于他的构造，就不得不说他的元祖。

1.1 什么是元祖？

在Postgresql做delete操作时，数据集(也叫做元组 (tuples))是没有立即从数据文件中移除的，仅仅是通过在行头部设置xmax做一个删除标记。update操作也是一样的，在postgresql中可以看作是先delete再insert;

这是Postgresql MVCC的基本思想之一，因为它允许在不同进程之间只进行最小的锁定就可以实现更大的并发性。这个MVCC实现的缺点当然是它会留下被标记删除的 元组（ dead tuples），即使在这些版本的所有事务完成之后。

1.2 死元祖过多的危害

如果不清理掉那些dead tuples（对任何事务都是不可见的）将会永远留在数据文件中，浪费磁盘空间，对于表来说，有过多的删除和更新，dead tuples很容易占绝大部分磁盘空间。而且dead tuples也会在索引中存在，更加加重磁盘空间的浪费。这是在PostgreSQL中常说的膨胀（bloat）。自然的，需要处理的数据查询越多，查询的速度就越慢。

1.3 查询死元祖情况

1.3.1 查询那些表的死元祖过多
1、查询当前数据库表已经达到自动清理条件的表及相关信息

SELECT
    c.relname 表名,
    (current_setting('autovacuum_analyze_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_analyze_scale_factor')::NUMERIC(12,4))*reltuples AS 自动分析阈值,
    (current_setting('autovacuum_vacuum_threshold')::NUMERIC(12,4))+(current_setting('autovacuum_vacuum_scale_factor')::NUMERIC(12,4