聊聊PostgreSql table和磁盘文件的映射关系
在postgresql中Droptable会不会释放磁盘空间，今日以实操来见证
--2019-01-1109:49:21droptable会不会释放空间
createtabletab_todrop(idint,cnamevarchar(50),remarktext);
insertintotab_todropselectgenerate_series(1,10000000),'wxgoodboy',md5('wxgoodboy');
insertintotab_todropselectgenerate_series(1,10000000),'wxgoodboy',md5('wxgoodboy');
--查看表大小
qmstst=#selectpg_size_pretty(pg_relation_size('tab_todrop'));
pg_size_pretty
----------------
2410MB
(1row)
qmstst=#
每张数据表放在datap*下。postgresql集群是通过hash分布到dataap*这种文件下。
[root@P1QMSTST01~]#df-h FilesystemSizeUsedAvailUse%Mountedon /dev/mapper/rootvg-rootlv 35G4.8G28G15%/ tmpfs63G063G0%/dev/shm /dev/sda2477M33M419M8%/boot /dev/sda1500M272K500M1%/boot/efi /dev/mapper/rootvg-homelv 4.8G1.6G3.1G34%/home /dev/mapper/rootvg-optlv 20G8.4G11G46%/opt /dev/mapper/rootvg-tmplv 4.8G402M4.2G9%/tmp /dev/mapper/rootvg-usrlv 9.8G3.6G5.8G39%/usr /dev/mapper/rootvg-locallv 52G25G25G51%/usr/local /dev/mapper/rootvg-varlv 15G5.2G8.8G37%/var /dev/mapper/datavg-gpmasterlv 100G50G51G50%/gpmaster /dev/mapper/datavg-datap1lv 150G43G108G29%/datap1 /dev/mapper/datavg-datap2lv 150G42G109G28%/datap2 /dev/mapper/datavg-datap3lv 150G42G109G28%/datap3 /dev/mapper/datavg-datap4lv 150G42G109G28%/datap4 /dev/mapper/datavg-datap5lv 150G43G108G29%/datap5 /dev/mapper/datavg-datap6lv 150G42G108G28%/datap6 /dev/mapper/rootvg-redislv
在dataap*下的base目录下存储的是数据表
select relname,--表/视图/索引等的名字 relowner,--关系所有者 relfilenode--这个关系在磁盘上的文件的名称，如果没有则为0 frompg_class whererelname='tab_todrop'; qmstst=#selectrelname,relowner,relfilenodefrompg_classwhererelname='tab_todrop'; relname|relowner|relfilenode ------------+----------+------------- tab_todrop|17088|15997062 (1row) ls-lh17089/15997006* -rw-------1gpadmingpadmin268MJan1113:5617089/15997006 [root@P1QMSTST01base]#pwd /datap2/gpseg1/base [root@P1QMSTST01base]#
droptable后，base目录下的该文件就被删除了，因此可以断定“在postgresql中droptable会释放空间”
补充：postgresql的table、index物理存储
postgresql是使用文件系统存储数据的，有时需要找表及索引对应的磁盘文件，就必须了解以下知识点。
非toast情况
zabbix=#createtabletmp_t0(c0varchar(100),c1varchar(100),c2varchar(100)); CREATETABLE zabbix=# zabbix=#createindexidx_tmp_t0ontmp_t0(c0); CREATEINDEX zabbix=# zabbix=#insertintotmp_t0selectid::varchar,md5(id::varchar),md5(md5(id::varchar))fromgenerate_series(1,100000)asid; INSERT0100000 zabbix=# zabbix=#deletefromtmp_t0wherec0>'1'; DELETE99999
查看表对应的操作系统文件.
zabbix=#selectpg_relation_filenode('tmp_t0'),pg_relation_filepath('tmp_t0');
pg_relation_filenode|pg_relation_filepath
----------------------+----------------------
24583|base/24579/24583
(1row)
查看索引对应的操作系统文件.
zabbix=#selectpg_relation_filenode('idx_tmp_t0'),pg_relation_filepath('idx_tmp_t0');
pg_relation_filenode|pg_relation_filepath
----------------------+----------------------
24588|base/24579/24588
(1row)
使用pg_class查看
zabbix=#selectpc.oid,pc.relname,pc.relfilenodefrompg_classpcwhere1=1andpc.relnamein('tmp_t0','idx_tmp_t0');
oid|relname|relfilenode
-------+------------+-------------
24583|tmp_t0|24583
24588|idx_tmp_t0|24588
(2rows)
操作系统查看
$ls-l|grep-i24583;ls-l|grep-i24588; -rw-------1postgrespostgres10117120Sep1911:1824583 -rw-------1postgrespostgres24576Sep1911:1824583_fsm -rw-------1postgrespostgres8192Sep1911:2024583_vm -rw-------1postgrespostgres2260992Sep1911:2524588
这个时候做个truncate操作
zabbix=#truncatetabletmp_t0; TRUNCATEtable zabbix=# zabbix=#insertintotmp_t0selectid::varchar,md5(id::varchar),md5(md5(id::varchar))fromgenerate_series(1,100000)asid; INSERT0100000
依次查看
zabbix=#selectpg_relation_filenode('tmp_t0'),pg_relation_filepath('tmp_t0');
pg_relation_filenode|pg_relation_filepath
----------------------+----------------------
24589|base/24579/24589
(1row)
zabbix=#selectpg_relation_filenode('idx_tmp_t0'),pg_relation_filepath('idx_tmp_t0');
pg_relation_filenode|pg_relation_filepath
----------------------+----------------------
24590|base/24579/24590
(1row)
zabbix=#selectpc.oid,pc.relname,pc.relfilenodefrompg_classpcwhere1=1andpc.relnamein('tmp_t0','idx_tmp_t0');
oid|relname|relfilenode
-------+------------+-------------
24583|tmp_t0|24589
24588|idx_tmp_t0|24590
(2rows)
$ls-l|grep-i24583;ls-l|grep-i24588;
-rw-------1postgrespostgres0Sep1911:3324583
-rw-------1postgrespostgres0Sep1911:3324588
$ls-l|grep-i24589;ls-l|grep-i24590;
-rw-------1postgrespostgres10117120Sep1911:3524589
-rw-------1postgrespostgres24576Sep1911:3524589_fsm
-rw-------1postgrespostgres3932160Sep1911:3524590
之后再查看oldrelfilenode时已经消失不见了
$ls-l|grep-i24583;ls-l|grep-i24588; $
总结如下：
1、createtable、createindex时，pg_class的oid与relfilenode相同。
1、truncatetable后，table与index的oid均没有发生变化，但是relfilenode发生了变化。
toast情况
插入4千万行数据，让tmp_t0在磁盘的大小大于1G
zabbix=#insertintotmp_t0selectid::varchar,md5(id::varchar),md5(md5(id::varchar))fromgenerate_series(1,40000000)asid;
INSERT040000000
zabbix=#
zabbix=#createindexidx_tmp_t0_1ontmp_t0(c1);
CREATEindex
zabbix=#selectpc.oid,pc.relname,pc.relfilenodefrompg_classpcwhere1=1andpc.relnamein('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
(3rows)
$ls-l|grep-i24589;ls-l|grep-i24590;ls-l|grep-i24599;
-rw-------1postgrespostgres1073741824Sep1912:1524589
-rw-------1postgrespostgres1073741824Sep1912:1724589.1
-rw-------1postgrespostgres1073741824Sep1912:1924589.2
-rw-------1postgrespostgres1073741824Sep1912:2324589.3
-rw-------1postgrespostgres81788928Sep1912:2524589.4
-rw-------1postgrespostgres1097728Sep1912:1424589_fsm
-rw-------1postgrespostgres1073741824Sep1912:1424590
-rw-------1postgrespostgres332496896Sep1912:1424590.1
-rw-------1postgrespostgres1073741824Sep1912:2424599
-rw-------1postgrespostgres1073741824Sep1912:2424599.1
-rw-------1postgrespostgres220487680Sep1912:2424599.2
下面是查看表及索引对应的存储文件
selectpt.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),--指定的单个索引 pg_indexes_size(pi.schemaname||'.'||pi.tablename)--表上的所有索引 frompg_tablespt leftouterjoinpg_indexespi onpt.schemaname||'.'||pt.tablename=pi.schemaname||'.'||pi.tablename where1=1 andpt.schemaname='public' andpt.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 (2rows)
参考文档：
表9-83.数据库对象尺寸函数
名称返回类型描述 pg_column_size(any)int存储一个特定值（可能压缩过）所需的字节数 pg_database_size(oid)bigint指定OID的数据库使用的磁盘空间 pg_database_size(name)bigint指定名称的数据库使用的磁盘空间 pg_indexes_size(regclass)bigint附加到指定表的索引所占的总磁盘空间 pg_relation_size(relationregclass,forktext)bigint指定表或索引的指定分叉（'main'、'fsm'、'vm'或'init'）使用的磁盘空间 pg_relation_size(relationregclass)bigintpg_relation_size(...,'main')的简写 pg_size_bytes(text)bigint把人类可读格式的带有单位的尺寸转换成字节数 pg_size_pretty(bigint)text将表示成一个64位整数的字节尺寸转换为带尺寸单位的人类可读格式 pg_size_pretty(numeric)text将表示成一个数字值的字节尺寸转换为带尺寸单位的人类可读格式 pg_table_size(regclass)bigint被指定表使用的磁盘空间，排除索引（但包括TOAST、空闲空间映射和可见性映射） pg_tablespace_size(oid)bigint指定OID的表空间使用的磁盘空间 pg_tablespace_size(name)bigint指定名称的表空间使用的磁盘空间 pg_total_relation_size(regclass)bigint指定表所用的总磁盘空间，包括所有的索引和TOAST数据
pg_column_size显示用于存储任意独立数据值的空间。
pg_total_relation_size接受一个表或TOAST表的OID或名称，并返回该表所使用的总磁盘空间，包括所有相关的索引。这个函数等价于pg_table_size+pg_indexes_size。
pg_table_size接受一个表的OID或名称，并返回该表所需的磁盘空间，但是排除索引（TOAST空间、空闲空间映射和可见性映射包含在内）
pg_indexes_size接受一个表的OID或名称，并返回附加到该表的所有索引所使用的全部磁盘空间。
pg_database_size和pg_tablespace_size接受一个数据库或表空间的OID或名称，并且返回它们所使用的全部磁盘空间。要使用pg_database_size，你必须具有在指定数据库上的CONNECT权限（默认会被授予）。要使用pg_tablespace_size，你必须具有指定表空间上的CREATE权限，除非它是当前数据库的默认表空间。
pg_relation_size接受一个表、索引或TOAST表的OID或者名称，并且返回那个关系的一个分叉所占的磁盘空间的字节尺寸（注意对于大部分目的，使用更高层的函数pg_total_relation_size或者pg_table_size会更方便，它们会合计所有分叉的尺寸）。如果只得到一个参数，它会返回该关系的主数据分叉的尺寸。提供第二个参数可以指定要检查哪个分叉：
'main'返回该关系主数据分叉的尺寸。
'fsm'返回与该关系相关的空闲空间映射（见第65.3节）的尺寸。
'vm'返回与该关系相关的可见性映射（见第65.4节）的尺寸。
'init'返回与该关系相关的初始化分叉（如果有）的尺寸。
pg_size_pretty可以用于把其它函数之一的结果格式化成一种人类易读的格式，可以根据情况使用KB、MB、GB或者TB。
pg_size_bytes可以被用来从人类可读格式的字符串得到其中所表示的字节数。其输入可能带有的单位包括字节、kB、MB、GB或者TB，并且对输入进行解析时是区分大小写的。如果没有指定单位，会假定单位为字节。
注意：
函数pg_size_pretty和pg_size_bytes所使用的单位kB、MB、GB和TB是用2的幂而不是10的幂来定义，因此1kB是1024字节，1MB是10242=1048576字节，以此类推
上述操作表和索引的函数接受一个regclass参数，它是该表或索引在pg_class系统目录中的OID。你不必手工去查找该OID，因为regclass数据类型的输入转换器会为你代劳。只写包围在单引号内的表名，这样它看起来像一个文字常量。为了与普通SQL名称的处理相兼容，该字符串将被转换为小写形式，除非其中在表名周围包含双引号。
如果一个OID不表示一个已有的对象并且被作为参数传递给了上述函数，将会返回NULL。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。