在postgresql中 Drop table会不会释放磁盘空间，今日以实操来见证
|
1
2
3
4
5
6
7
8
9
10
11
|
--2019-01-11 09:49:21 drop table 会不会释放空间 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=# |
每张数据表放在datap*下。postgresql集群是通过hash分布到dataap*这种文件下。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
[root@P1QMSTST01 ~]# df -hFilesystem 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目录下存储的是数据表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
selectrelname, --表/视图/索引等的名字relowner, --关系所有者relfilenode --这个关系在磁盘上的文件的名称，如果没有则为0from pg_classwhere 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会释放空间”
补充：postgresql 的table、index物理存储
postgresql 是使用文件系统存储数据的，有时需要找表及索引对应的磁盘文件，就必须了解以下知识点。
非toast情况
|
1
2
3
4
5
6
7
8
9
10
11
|
zabbix=# create table tmp_t0(c0 varchar(100),c1 varchar(100), c2 varchar(100));CREATE TABLEzabbix=# zabbix=# create index idx_tmp_t0 on tmp_t0(c0);CREATE INDEXzabbix=# zabbix=# insert into tmp_t0 select id::varchar,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,100000) as id ;INSERT 0 100000zabbix=# zabbix=# delete from tmp_t0 where c0 > '1';DELETE 99999 |
查看表对应的操作系统文件.
|
1
2
3
4
5
|
zabbix=# select pg_relation_filenode('tmp_t0'),pg_relation_filepath('tmp_t0'); pg_relation_filenode | pg_relation_filepath ----------------------+---------------------- 24583 | base/24579/24583(1 row) |
查看索引对应的操作系统文件.
|
1
2
3
4
5
|
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 查看
|
1
2
3
4
5
6
|
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) |
操作系统查看
|
1
2
3
4
5
|
$ 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 |
这个时候做个truncate操作
|
1
2
3
4
5
|
zabbix=# truncate table tmp_t0;TRUNCATE tablezabbix=#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 |
依次查看
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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 时已经消失不见了
|
1
2
|
$ ls -l |grep -i 24583; ls -l |grep -i 24588;$ |
总结如下：
1、create table、create index 时，pg_class 的 oid 与 relfilenode 相同。
1、truncate table 后，table与index的oid均没有发生变化，但是 relfilenode 发生了变化。
toast 情况
插入4千万行数据，让tmp_t0在磁盘的大小大于1G
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
zabbix=# insert into tmp_t0 select id::varchar,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,40000000) as id ;INSERT 0 40000000zabbix=#zabbix=# create index idx_tmp_t0_1 on tmp_t0(c1);CREATE indexzabbix=# 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 |
下面是查看表及索引对应的存储文件
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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),--指定的单个索引 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.tablenamewhere 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. 数据库对象尺寸函数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
名称 返回类型 描述 pg_column_size(any) int 存储一个特定值（可能压缩过）所需的字节数 pg_database_size(oid) bigint 指定 OID 的数据库使用的磁盘空间 pg_database_size(name) bigint 指定名称的数据库使用的磁盘空间 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')的简写 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。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://blog.csdn.net/MyySophia/article/details/86299772
