Postgresql实验验证__update、delete产生死亡元组及标准vacuum也会释放表文件磁盘空间的场景_pg delete后什么时候释放空间
赞
踩
已经验证
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 d e c l a r e v i d x i n t e g e r : = 1 ; b e g i n w h i l e v i d x < 2000000 l o o p v i d x = v i d x + 1 ; i n s e r t i n t o t 1 v a l u e s ( v i d x , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ ) ; e n d l o o p ; e n d 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 declarevidxinteger:=1;beginwhilevidx<2000000loopvidx=vidx+1;insertintot1values(vidx,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′);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
(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 d e c l a r e v i d x i n t e g e r : = 1 ; b e g i n w h i l e v i d x < 2000000 l o o p v i d x = v i d x + 1 ; i n s e r t i n t o t 1 v a l u e s ( v i d x , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ ) ; e n d l o o p ; e n d 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 declarevidxinteger:=1;beginwhilevidx<2000000loopvidx=vidx+1;insertintot1values(vidx,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′);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
(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 d e c l a r e v i d x i n t e g e r : = 1 ; b e g i n w h i l e v i d x < 2000000 l o o p v i d x = v i d x + 1 ; i n s e r t i n t o t 2 v a l u e s ( v i d x , v i d x , v i d x , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ ) ; e n d l o o p ; e n d 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 declarevidxinteger:=1;beginwhilevidx<2000000loopvidx=vidx+1;insertintot2values(vidx,vidx,vidx,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′);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 d e c l a r e v i d x i n t e g e r : = 1 ; b e g i n w h i l e v i d x < 2000000 l o o p v i d x = v i d x + 1 ; i n s e r t i n t o t 3 v a l u e s ( v i d x , v i d x , v i d x , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ , ′ e e e e e e z h a n g ′ ) ; e n d l o o p ; e n d 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 declarevidxinteger:=1;beginwhilevidx<2000000loopvidx=vidx+1;insertintot3values(vidx,vidx,vidx,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′);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
d
e
c
l
a
r
e
v
i
d
x
i
n
t
e
g
e
r
:
=
1
;
b
e
g
i
n
w
h
i
l
e
v
i
d
x
<
2000000
l
o
o
p
v
i
d
x
=
v
i
d
x
+
1
;
i
n
s
e
r
t
i
n
t
o
t
1
v
a
l
u
e
s
(
v
i
d
x
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
,
′
e
e
e
e
e
e
z
h
a
n
g
′
)
;
e
n
d
l
o
o
p
;
e
n
d
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
declarevidxinteger:=1;beginwhilevidx<2000000loopvidx=vidx+1;insertintot1values(vidx,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′,′eeeeeezhang′);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)
