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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2793304/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- lsof |grep deleted 釋放磁碟空間delete
- MySQL 5.7的表刪除資料後的磁碟空間釋放MySql
- Linux檔案刪除空間未釋放Linux
- 解決刪除檔案後 WSL2 磁碟空間不釋放的問題
- RM刪除檔案空間釋放詳解
- PostgreSQL在不同的表空間移動資料檔案SQL
- 技術應用丨DWS 空間釋放(vacuum full) 最佳實踐
- PostgreSQL 表空間SQL
- PostgreSQL：表空間SQL
- win10怎麼釋放空間_win10釋放磁碟空間的方法Win10
- [待整理]oracle10g刪除(釋放)資料檔案/表空間流程Oracle
- Mysql InnoDB刪除資料後釋放磁碟空間的方法MySql
- Laravel 的 表單驗證，多場景Laravel
- Linux檔案刪除但空間不釋放問題篇Linux
- SQLServer如何釋放tempdb臨時表空間SQLServer
- 檢視磁碟使用空間和檔案大小
- 處理Linux刪除檔案後空間未釋放的問題Linux
- （轉載）刪除檔案後硬碟空間不釋放的問題硬碟
- 如何釋放Mac空間？釋放Mac系統空間小技巧Mac
- Oracle 表空間增加檔案Oracle
- MySQL 磁碟空間滿導致表空間相關資料檔案損壞故障處理MySql
- PostgreSQL,SQLServer邏輯增量(通過邏輯標記update,delete)同步到Greenplum,PostgreSQLSQLServerdelete
- Postgresql表空間詳解SQL
- webpack watch模式產生*.hot-update.json檔案Web模式JSON
- PostgreSQL:表空間-->資料庫-->表SQL資料庫
- 表空間和資料檔案的管理
- linux中如何解決檔案已刪除但空間不釋放的案例Linux
- ORACLE ASM磁碟組空間溢位OracleASM
- 實踐場景：解決Spark流處理產生的小檔案Spark
- MySQL 系統表空間檔案解析MySql
- Laravel 驗證類 實現 路由場景驗證 和 控制器場景驗證Laravel路由
- 新建的表空間(或資料檔案)丟失以及控制檔案丟失，有新建表空間(或資料檔案)前的控制文
- MySQL innodb表使用表空間物理檔案複製表MySql
- C++標準庫名字和標頭檔案--表C++
- PostgreSQL的vacuum流程SQL
- 【趙渝強老師】PostgreSQL的表空間SQL
- 10、MySQL Case-釋放ibtmp表空間正確姿勢MySql
- 關於Laravel的表單驗證分層設計以及驗證場景的應用Laravel