聊聊PostgreSql table和磁盤文件的映射關(guān)系

更新時(shí)間：2021年01月28日 11:10:55 作者：MyySophia

這篇文章主要介紹了聊聊PostgreSql table和磁盤文件的映射關(guān)系，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧

在postgresql中 Drop table會(huì)不會(huì)釋放磁盤空間，今日以實(shí)操來見證

--2019-01-11 09:49:21 drop table 會(huì)不會(huì)釋放空間
 create table tab_todrop(id int,cname varchar(50),remark text);
 insert into tab_todrop select generate_series(1,10000000),'wx good boy',md5('wx good boy');
 insert into tab_todrop select generate_series(1,10000000),'wx good boy',md5('wx good boy');
--查看表大小
qmstst=# select pg_size_pretty(pg_relation_size('tab_todrop'));
 pg_size_pretty 
----------------
 2410 MB
(1 row)
qmstst=#

每張數(shù)據(jù)表放在datap*下。postgresql集群是通過hash分布到dataap*這種文件下。

[root@P1QMSTST01 ~]# df -h
Filesystem   Size Used Avail Use% Mounted on
/dev/mapper/rootvg-rootlv
      35G 4.8G 28G 15% /
tmpfs     63G  0 63G 0% /dev/shm
/dev/sda2    477M 33M 419M 8% /boot
/dev/sda1    500M 272K 500M 1% /boot/efi
/dev/mapper/rootvg-homelv
      4.8G 1.6G 3.1G 34% /home
/dev/mapper/rootvg-optlv
      20G 8.4G 11G 46% /opt
/dev/mapper/rootvg-tmplv
      4.8G 402M 4.2G 9% /tmp
/dev/mapper/rootvg-usrlv
      9.8G 3.6G 5.8G 39% /usr
/dev/mapper/rootvg-locallv
      52G 25G 25G 51% /usr/local
/dev/mapper/rootvg-varlv
      15G 5.2G 8.8G 37% /var
/dev/mapper/datavg-gpmasterlv
      100G 50G 51G 50% /gpmaster
/dev/mapper/datavg-datap1lv
      150G 43G 108G 29% /datap1
/dev/mapper/datavg-datap2lv
      150G 42G 109G 28% /datap2
/dev/mapper/datavg-datap3lv
      150G 42G 109G 28% /datap3
/dev/mapper/datavg-datap4lv
      150G 42G 109G 28% /datap4
/dev/mapper/datavg-datap5lv
      150G 43G 108G 29% /datap5
/dev/mapper/datavg-datap6lv
      150G 42G 108G 28% /datap6
/dev/mapper/rootvg-redislv

在dataap*下的base目錄下存儲(chǔ)的是數(shù)據(jù)表

select 
relname, --表/視圖/索引等的名字
relowner, --關(guān)系所有者
relfilenode --這個(gè)關(guān)系在磁盤上的文件的名稱，如果沒有則為0
from pg_class
where relname = 'tab_todrop';
qmstst=# select relname, relowner, relfilenode from pg_class where relname = 'tab_todrop';
 relname | relowner | relfilenode 
------------+----------+-------------
 tab_todrop | 17088 | 15997062
(1 row)
 
ls -lh 17089/15997006*
-rw------- 1 gpadmin gpadmin 268M Jan 11 13:56 17089/15997006
[root@P1QMSTST01 base]# pwd
/datap2/gpseg1/base
[root@P1QMSTST01 base]#

drop table 后，base目錄下的該文件就被刪除了，因此可以斷定 “在postgresql中drop table會(huì)釋放空間”

補(bǔ)充：postgresql 的table、index物理存儲(chǔ)

postgresql 是使用文件系統(tǒng)存儲(chǔ)數(shù)據(jù)的，有時(shí)需要找表及索引對(duì)應(yīng)的磁盤文件，就必須了解以下知識(shí)點(diǎn)。

非toast情況

zabbix=# create table tmp_t0(c0 varchar(100),c1 varchar(100), c2 varchar(100));
CREATE TABLE
zabbix=# 
zabbix=# create index idx_tmp_t0 on tmp_t0(c0);
CREATE INDEX
zabbix=# 
zabbix=# insert into tmp_t0 select id::varchar,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,100000) as id ;
INSERT 0 100000
zabbix=# 
zabbix=# delete from tmp_t0 where c0 > '1';
DELETE 99999

查看表對(duì)應(yīng)的操作系統(tǒng)文件.

zabbix=# select pg_relation_filenode('tmp_t0'),pg_relation_filepath('tmp_t0');
 pg_relation_filenode | pg_relation_filepath 
----------------------+----------------------
  24583 | base/24579/24583
(1 row)

查看索引對(duì)應(yīng)的操作系統(tǒng)文件.

zabbix=# select pg_relation_filenode('idx_tmp_t0'),pg_relation_filepath('idx_tmp_t0');
 pg_relation_filenode | pg_relation_filepath 
----------------------+----------------------
  24588 | base/24579/24588
(1 row)

使用 pg_class 查看

zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ('tmp_t0','idx_tmp_t0');
 oid | relname | relfilenode 
-------+------------+-------------
 24583 | tmp_t0 | 24583
 24588 | idx_tmp_t0 | 24588
(2 rows)

操作系統(tǒng)查看

$ ls -l |grep -i 24583; ls -l |grep -i 24588;
-rw------- 1 postgres postgres 10117120 Sep 19 11:18 24583
-rw------- 1 postgres postgres 24576 Sep 19 11:18 24583_fsm
-rw------- 1 postgres postgres 8192 Sep 19 11:20 24583_vm
-rw------- 1 postgres postgres 2260992 Sep 19 11:25 24588

這個(gè)時(shí)候做個(gè)truncate操作

zabbix=# truncate table tmp_t0;
TRUNCATE table
zabbix=#
zabbix=# insert into tmp_t0 select id::varchar,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,100000) as id ;
INSERT 0 100000

依次查看

zabbix=# select pg_relation_filenode('tmp_t0'),pg_relation_filepath('tmp_t0');
 pg_relation_filenode | pg_relation_filepath 
----------------------+----------------------
  24589 | base/24579/24589
(1 row)
 
zabbix=# select pg_relation_filenode('idx_tmp_t0'),pg_relation_filepath('idx_tmp_t0');
 pg_relation_filenode | pg_relation_filepath 
----------------------+----------------------
  24590 | base/24579/24590
(1 row)
zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ('tmp_t0','idx_tmp_t0');
 oid | relname | relfilenode 
-------+------------+-------------
 24583 | tmp_t0 | 24589
 24588 | idx_tmp_t0 | 24590
(2 rows)
 
$ ls -l |grep -i 24583; ls -l |grep -i 24588;
-rw------- 1 postgres postgres 0 Sep 19 11:33 24583
-rw------- 1 postgres postgres 0 Sep 19 11:33 24588
$ ls -l |grep -i 24589; ls -l |grep -i 24590;
-rw------- 1 postgres postgres 10117120 Sep 19 11:35 24589
-rw------- 1 postgres postgres 24576 Sep 19 11:35 24589_fsm
-rw------- 1 postgres postgres 3932160 Sep 19 11:35 24590

之后再查看 old relfilenode 時(shí)已經(jīng)消失不見了

$ ls -l |grep -i 24583; ls -l |grep -i 24588;
$

總結(jié)如下：

1、create table、create index 時(shí)，pg_class 的 oid 與 relfilenode 相同。

1、truncate table 后，table與index的oid均沒有發(fā)生變化，但是 relfilenode 發(fā)生了變化。

toast 情況

插入4千萬行數(shù)據(jù)，讓tmp_t0在磁盤的大小大于1G

zabbix=# insert into tmp_t0 select id::varchar,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,40000000) as id ;
INSERT 0 40000000
zabbix=#
zabbix=# create index idx_tmp_t0_1 on tmp_t0(c1);
CREATE index
zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ('tmp_t0','idx_tmp_t0','idx_tmp_t0_1');
 oid | relname | relfilenode 
-------+--------------+-------------
 24583 | tmp_t0 | 24589
 24588 | idx_tmp_t0 | 24590
 24599 | idx_tmp_t0_1 | 24599
(3 rows)
$ ls -l |grep -i 24589; ls -l |grep -i 24590;ls -l|grep -i 24599;
-rw------- 1 postgres postgres 1073741824 Sep 19 12:15 24589
-rw------- 1 postgres postgres 1073741824 Sep 19 12:17 24589.1
-rw------- 1 postgres postgres 1073741824 Sep 19 12:19 24589.2
-rw------- 1 postgres postgres 1073741824 Sep 19 12:23 24589.3
-rw------- 1 postgres postgres 81788928 Sep 19 12:25 24589.4
-rw------- 1 postgres postgres 1097728 Sep 19 12:14 24589_fsm
-rw------- 1 postgres postgres 1073741824 Sep 19 12:14 24590
-rw------- 1 postgres postgres 332496896 Sep 19 12:14 24590.1
-rw------- 1 postgres postgres 1073741824 Sep 19 12:24 24599
-rw------- 1 postgres postgres 1073741824 Sep 19 12:24 24599.1
-rw------- 1 postgres postgres 220487680 Sep 19 12:24 24599.2

下面是查看表及索引對(duì)應(yīng)的存儲(chǔ)文件

select pt.schemaname||'.'||pt.tablename,pg_relation_filepath(pt.schemaname||'.'||pt.tablename),
 pg_table_size(pt.schemaname||'.'||pt.tablename),
 pg_relation_size(pt.schemaname||'.'||pt.tablename),
 pg_total_relation_size(pt.schemaname||'.'||pt.tablename),
 pi.schemaname||'.'||pi.indexname,pg_relation_filepath(pi.schemaname||'.'||pi.indexname),
 pg_relation_size(pi.schemaname||'.'||pi.indexname),--指定的單個(gè)索引
 pg_indexes_size(pi.schemaname||'.'||pi.tablename) --表上的所有索引
 from pg_tables pt
 left outer join pg_indexes pi 
   on pt.schemaname||'.'||pt.tablename = pi.schemaname||'.'||pi.tablename
where 1=1
 and pt.schemaname='public'
 and pt.tablename='tmp_t0'
;
 
 ?column? | pg_relation_filepath | pg_table_size | pg_relation_size | pg_total_relation_size | ?column? | pg_relation_filepath | pg_relation_size | pg_indexes_size 
---------------+----------------------+---------------+------------------+------------------------+---------------------+----------------------+------------------+-----------------
 public.tmp_t0 | base/24579/24589 | 4377853952 | 4376756224 |  8152064000 | public.idx_tmp_t0 | base/24579/24590 | 1406238720 | 3774210048
 public.tmp_t0 | base/24579/24589 | 4377853952 | 4376756224 |  8152064000 | public.idx_tmp_t0_1 | base/24579/24599 | 2367971328 | 3774210048
(2 rows)

參考文檔：

表 9-83. 數(shù)據(jù)庫對(duì)象尺寸函數(shù)

名稱      返回類型  描述 
pg_column_size(any)  int 存儲(chǔ)一個(gè)特定值（可能壓縮過）所需的字節(jié)數(shù) 
pg_database_size(oid)    bigint 指定 OID 的數(shù)據(jù)庫使用的磁盤空間 
pg_database_size(name)    bigint 指定名稱的數(shù)據(jù)庫使用的磁盤空間 
pg_indexes_size(regclass)   bigint 附加到指定表的索引所占的總磁盤空間 
pg_relation_size(relation regclass, fork text) bigint 指定表或索引的指定分叉（'main'、'fsm'、'vm'或'init'）使用的磁盤空間 
pg_relation_size(relation regclass)  bigint pg_relation_size(..., 'main')的簡(jiǎn)寫 
pg_size_bytes(text)    bigint 把人類可讀格式的帶有單位的尺寸轉(zhuǎn)換成字節(jié)數(shù) 
pg_size_pretty(bigint)    text  將表示成一個(gè) 64位整數(shù)的字節(jié)尺寸轉(zhuǎn)換為帶尺寸單位的人類可讀格式 
pg_size_pretty(numeric)   text  將表示成一個(gè)數(shù)字值的字節(jié)尺寸轉(zhuǎn)換為帶尺寸單位的人類可讀格式 
pg_table_size(regclass)   bigint 被指定表使用的磁盤空間，排除索引（但包括 TOAST、空閑空間映射和可見性映射） 
pg_tablespace_size(oid)   bigint 指定 OID 的表空間使用的磁盤空間 
pg_tablespace_size(name)   bigint 指定名稱的表空間使用的磁盤空間 
pg_total_relation_size(regclass)  bigint 指定表所用的總磁盤空間，包括所有的索引和TOAST數(shù)據(jù)

pg_column_size 顯示用于存儲(chǔ)任意獨(dú)立數(shù)據(jù)值的空間。

pg_total_relation_size 接受一個(gè)表或 TOAST 表的 OID 或名稱，并返回該表所使用的總磁盤空間，包括所有相關(guān)的索引。這個(gè)函數(shù)等價(jià)于pg_table_size + pg_indexes_size。

pg_table_size 接受一個(gè)表的 OID 或名稱，并返回該表所需的磁盤空間，但是排除索引（TOAST 空間、空閑空間映射和可見性映射包含在內(nèi)）

pg_indexes_size 接受一個(gè)表的 OID 或名稱，并返回附加到該表的所有索引所使用的全部磁盤空間。

pg_database_size 和 pg_tablespace_size 接受一個(gè)數(shù)據(jù)庫或表空間的 OID 或名稱，并且返回它們所使用的全部磁盤空間。要使用pg_database_size，你必須具有在指定數(shù)據(jù)庫上的 CONNECT權(quán)限（默認(rèn)會(huì)被授予）。要使用pg_tablespace_size，你必須具有指定表空間上的CREATE權(quán)限，除非它是當(dāng)前數(shù)據(jù)庫的默認(rèn)表空間。

pg_relation_size 接受一個(gè)表、索引或 TOAST 表的 OID 或者名稱，并且返回那個(gè)關(guān)系的一個(gè)分叉所占的磁盤空間的字節(jié)尺寸（注意對(duì)于大部分目的，使用更高層的函數(shù)pg_total_relation_size或者pg_table_size 會(huì)更方便，它們會(huì)合計(jì)所有分叉的尺寸）。如果只得到一個(gè)參數(shù)，它會(huì)返回該關(guān)系的主數(shù)據(jù)分叉的尺寸。提供第二個(gè)參數(shù)可以指定要檢查哪個(gè)分叉：

'main'返回該關(guān)系主數(shù)據(jù)分叉的尺寸。

'fsm'返回與該關(guān)系相關(guān)的空閑空間映射（見第 65.3 節(jié)）的尺寸。

'vm'返回與該關(guān)系相關(guān)的可見性映射（見第 65.4 節(jié)）的尺寸。

'init'返回與該關(guān)系相關(guān)的初始化分叉（如果有）的尺寸。

pg_size_pretty 可以用于把其它函數(shù)之一的結(jié)果格式化成一種人類易讀的格式，可以根據(jù)情況使用 KB、MB、GB 或者 TB。

pg_size_bytes 可以被用來從人類可讀格式的字符串得到其中所表示的字節(jié)數(shù)。其輸入可能帶有的單位包括字節(jié)、kB、MB、GB 或者 TB，并且對(duì)輸入進(jìn)行解析時(shí)是區(qū)分大小寫的。如果沒有指定單位，會(huì)假定單位為字節(jié)。

注意：

函數(shù) pg_size_pretty 和 pg_size_bytes 所使用的單位 kB、MB、GB 和 TB 是用 2 的冪而不是 10 的冪來定義，因此 1kB 是 1024 字節(jié)， 1MB 是10242 = 1048576字節(jié)，以此類推

上述操作表和索引的函數(shù)接受一個(gè) regclass 參數(shù)，它是該表或索引在 pg_class系統(tǒng)目錄中的 OID。你不必手工去查找該 OID，因?yàn)?regclass數(shù)據(jù)類型的輸入轉(zhuǎn)換器會(huì)為你代勞。只寫包圍在單引號(hào)內(nèi)的表名，這樣它看起來像一個(gè)文字常量。為了與普通SQL名稱的處理相兼容，該字符串將被轉(zhuǎn)換為小寫形式，除非其中在表名周圍包含雙引號(hào)。

如果一個(gè) OID 不表示一個(gè)已有的對(duì)象并且被作為參數(shù)傳遞給了上述函數(shù)，將會(huì)返回 NULL。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: