本文共 2410 字,大约阅读时间需要 8 分钟。
在postgresql中 Drop table会不会释放磁盘空间,今日以实操来见证
--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*这种文件下。[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目录下存储的是数据表
select
relname, --表/视图/索引等的名字 relowner, --关系所有者 relfilenode --这个关系在磁盘上的文件的名称,如果没有则为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会释放空间”
转载地址:http://rzeqj.baihongyu.com/