Postgresql实验验证__update、delete产生死亡元组及标准vacuum也会释放表文件磁盘空间的场景

已经验证
1、一张表t1，插入20万行，表文件58425总计大概1GB，再delete这20万行，死亡记录20万行，表文件58425总计大概1GB，再插入20万行，表文件58425总计大概2GB，死亡记录还是20万行，再删除这这20万行，表文件58425总计大概2GB，死亡记录40万行
2、一张表t2，插入20万行，表文件58431总计大概1GB，再update这20万行，死亡记录20万行，表文件58431总计大概2GB
3、一张表t3，插入20万行，表文件58434总计大概1GB，再truncate这表，死亡记录0，表文件58434总计0GB

得出结论：
1、delete都会对老行做一个标记，虽然死亡记录会增加，但是表文件大小并没有增加
2、update对老行做一个标记，再新建一行新行，所以update的时候，死亡记录增加，表文件大小也增加
3、truncate后死亡记录为0，表文件大小也变成0，truncate就是重建表一样，因为表对应的pg_class.relfilenode都变了
4、标准vacuum(不带full)后，表对应的pg_class.relfilenode不变
5、vacuum full 表的情况下，select没法查询表，select会被vacuum full堵塞
6、vacuum full 表会释放表文件对应的磁盘空间，因为表对应的pg_class.relfilenode都变了
7、官方文档说vacuum full会释放磁盘空间，标准VACUUM(即不带FULL)不会，标准VACUUM也能把该表文件空间交还给操作系统的情况：表尾部有空页就能释放这个空页的空间，页包含页头部和行数据，也就是说如果每行数据都是死元组，那么整个这个页就是空闲的，实验场景，当我们insert一张新建的表或全是死元组的表时，insert的时候都是往尾部插入(类似oracle的insert /*+ append */往高水位线以上插入)，而一旦delete整表后，则整表的所有页里面都是死元组，直接VACUUM 不带FULL也能把这个页就给回收空间了，见本文的实验“delete后，执行vacuum，表的大小从1GB变成了0KB”

delete的案例t1表
create table t1(hid int, hid1 char(50),hid2 char(50),hid3 char(50),hid4 char(50),hid5 char(50),hid6 char(50),hid7 char(50),hid8 char(50),hid9 char(50),hid10 char(50));

do $$declare{v}_{i}dxinteger:=1;beginwhile{v}_{i}dx<2000000loop{v}_{i}dx=v_{i}dx+1;insertintot1values(v_{i}dx{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime });endloop;end$$;

select count(*) from t1;
count

1999999
(1 row)

查询文件路径
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t1’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58425 | 0

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.8G /var/lib/pgsql/pg/PG_11_201809051/58424/58425
93M /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
320K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname=‘t1’ ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------±--------±-----------±-----------
public | t1 | 2000005 | 0
(1 row)

delete from t1;
DELETE 1999999

select count(*) from t1;
count

 0

(1 row)

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname=‘t1’ ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------±--------±-----------±-----------
public | t1 | 13 | 1999999
(1 row)

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.8G /var/lib/pgsql/pg/PG_11_201809051/58424/58425
93M /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
320K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
8.0K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm

do $$declare{v}_{i}dxinteger:=1;beginwhile{v}_{i}dx<2000000loop{v}_{i}dx=v_{i}dx+1;insertintot1values(v_{i}dx{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime });endloop;end$$;

select count(*) from t1;
count

1999999
(1 row)

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.8G /var/lib/pgsql/pg/PG_11_201809051/58424/58425
1.0G /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
185M /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
584K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
32K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname=‘t1’ ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------±--------±-----------±-----------
public | t1 | 1999999 | 1999999
(1 row)

delete from t1;
DELETE 1999999

select count(*) from t1;
count

 0

(1 row)

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.8G /var/lib/pgsql/pg/PG_11_201809051/58424/58425
1.0G /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
185M /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
584K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
32K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname=‘t1’ ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------±--------±-----------±-----------
public | t1 | 0 | 3999997
(1 row)

update的案例t2表
create table t2(hid int, hid1 int,hid2 int,hid3 char(50),hid4 char(50),hid5 char(50),hid6 char(50),hid7 char(50),hid8 char(50),hid9 char(50),hid10 char(50));

do $$declare{v}_{i}dxinteger:=1;beginwhile{v}_{i}dx<2000000loop{v}_{i}dx=v_{i}dx+1;insertintot2values(v_{i}dx,v_{i}dx,v_{i}dx{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime });endloop;end$$;

select count(*) from t2;
count

1999999
(1 row)

查询文件路径
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t2’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58431 | 0

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58431*
1.3G /var/lib/pgsql/pg/PG_11_201809051/58424/58431
256K /var/lib/pgsql/pg/PG_11_201809051/58424/58431_fsm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname=‘t2’ ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------±--------±-----------±-----------
public | t2 | 1999999 | 0
(1 row)

update t2 set hid=0;
UPDATE 1999999

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58431*
1.3G /var/lib/pgsql/pg/PG_11_201809051/58424/58431
1.2G /var/lib/pgsql/pg/PG_11_201809051/58424/58431.1
516K /var/lib/pgsql/pg/PG_11_201809051/58424/58431_fsm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname=‘t2’ ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------±--------±-----------±-----------
public | t2 | 2000016 | 1999999
(1 row)

truncate的案例t3表
create table t3(hid int, hid1 char(50),hid2 char(50),hid3 char(50),hid4 char(50),hid5 char(50),hid6 char(50),hid7 char(50),hid8 char(50),hid9 char(50),hid10 char(50));

do $$declare{v}_{i}dxinteger:=1;beginwhile{v}_{i}dx<2000000loop{v}_{i}dx=v_{i}dx+1;insertintot3values(v_{i}dx,v_{i}dx,v_{i}dx{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime });endloop;end$$;

select count(*) from t3;
count

1999999
(1 row)

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t3’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58434 | 142858
(1 row)

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58434*
1.0G /var/lib/pgsql/pg/PG_11_201809051/58424/58434
93M /var/lib/pgsql/pg/PG_11_201809051/58424/58434.1
304K /var/lib/pgsql/pg/PG_11_201809051/58424/58434_fsm

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname=‘t3’ ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------±--------±-----------±-----------
public | t3 | 2000012 | 0
(1 row)

truncate table t3;

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58434*
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58434

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables where relname=‘t3’ ORDER BY n_dead_tup;
schemaname | relname | n_live_tup | n_dead_tup
------------±--------±-----------±-----------
public | t3 | 0 | 0
(1 row)

delete后，执行vacuum，表的大小从1GB变成了0KB
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t1’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58425 | 285715
(1 row)

lukes0818=# delete from t1;
DELETE 3999998

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
1.0G /var/lib/pgsql/pg/PG_11_201809051/58424/58425
1.1G /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
185M /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
584K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm

lukes0818=# vacuum t1;
VACUUM

[root@FRSPGSQLDEV2 ~]# du -sh /var/lib/pgsql/pg/PG_11_201809051/58424/58425*
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425.1
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425.2
16K /var/lib/pgsql/pg/PG_11_201809051/58424/58425_fsm
0 /var/lib/pgsql/pg/PG_11_201809051/58424/58425_vm

执行vacuum full的同时，无法执行select，select会被堵塞
会话1
lukes0818=# do $$declare{v}_{i}dxinteger:=1;beginwhile{v}_{i}dx<2000000loop{v}_{i}dx=v_{i}dx+1;insertintot1values(v_{i}dx{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime }{,}^{\prime }eeeeeezhan{g}^{\prime });endloop;end$$;
DO
lukes0818=# delete from t1;
DELETE 1999999
lukes0818=# vacuum full t1;

会话2
lukes0818=# select * from t1 limit 1;

会话3
lukes0818=# select a.locktype,b.datname,a.pid,a.mode,a.granted,regclass(a.relation),regclass(a.classid) from pg_locks a join pg_database b on a.database=b.oid and a.granted<>‘t’;
locktype | datname | pid | mode | granted | regclass | regclass
----------±----------±------±----------------±--------±---------±---------
relation | lukes0818 | 26820 | AccessShareLock | f | t1 |

lukes0818=# select query from pg_stat_activity where pid=26820;
query
----------+
select * from t1 limit 1; | client backend

每次vacuum full都会重新生成relfilenode
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t1’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58464 | 142858
(1 row)

lukes0818=# vacuum full t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t1’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58470 | 142858
(1 row)

lukes0818=# truncate table t1;
TRUNCATE TABLE
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t1’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58476 | 0
(1 row)

lukes0818=# vacuum t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t1’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58476 | 0
(1 row)

lukes0818=# vacuum full t1;
VACUUM
lukes0818=# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘t1’;
pg_relation_filepath | relpages
---------------------------------------------±---------
pg_tblspc/50003/PG_11_201809051/58424/58480 | 0
(1 row)

lukes0818=# select oid,relname,relfilenode from pg_class WHERE relname = ‘t1’;
oid | relname | relfilenode
-------±--------±------------
58425 | t1 | 58480
(1 row)