PG中的一例简单的update看表膨胀

PostgreSQL数据库的MVCC机制中，一个明显的特点是，在执行DELETE操作，它一般并不立即擦除要删的数据，通常只是做一个标记，留给VACUUM进程去做相应的清理，并且是有条件的清理。

而UPDATE操作，则更是一个DELETE操作和一个INSERT操作的组合。INSERT操作则通常会APPEND一条记录在后边。这样的结果是，通常情况下，这种INSERT, UPDATE操作应该是很快的。但如果用的不当，也有一些反例：

下面看一则很普通的示例：

create table t3 (id int);
insert into t3 values(1);
begin;
\set VERBOSE verbose
do language plpgsql $$  
declare  
  tcount int = 100000;
begin
  for i in 1..tcount loop  
    update t3 set id = id+1; 
  end loop;  
end;  
$$;
commit;

DO
Time: 120762.618 ms (02:00.763)
mydb=*# commit;

我们可以明显的看到，一条普通的记录，在更新100000次的操作下，居然花了整整两分钟。

我们用下边的SQL来看看该表的元组分布情况：(将其中的public.t3改为你想要的表名，就可以查目标表的相应情况)

WITH cteTableInfo AS 
(
    SELECT 
        COUNT(1) AS ct
        ,SUM(length(t::text)) AS TextLength  
        ,'public.t3'::regclass AS TableName  
    FROM public.t3 AS t  
)
,cteRowSize AS 
(
   SELECT ARRAY [pg_relation_size(TableName)
               , pg_relation_size(TableName, 'vm')
               , pg_relation_size(TableName, 'fsm')
               , pg_table_size(TableName)
               , pg_indexes_size(TableName)
               , pg_total_relation_size(TableName)
               , TextLength
             ] AS val
        , ARRAY ['Relation Size'
               , 'Visibility Map'
               , 'Free Space Map'
               , 'Table Included Toast Size'
               , 'Indexes Size'
               , 'Total Relation Size'
               , 'Live Row Byte Size'
             ] AS Name
   FROM cteTableInfo
)
SELECT 
    unnest(name) AS Description
    ,unnest(val) AS Bytes
    ,pg_size_pretty(unnest(val)) AS BytesP