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_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t1 values ( v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
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_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t1 values ( v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
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_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t2 values ( v_idx,v_idx,v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
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_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t3 values ( v_idx,v_idx,v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
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_idx integer := 1;
begin
  while v_idx < 2000000 loop
  v_idx = v_idx+1;
insert into t1 values ( v_idx,'eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang','eeeeeezhang');
  end loop;
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)