首页
登录 | 注册

delete后进行VACUUM操作可能会释放空间

从中间删掉200W条数据:

mydb=# select count(*) from tbl_kenyon ;
  count
----------
 20000000
(1 row)

mydb=# select pg_size_pretty(pg_relation_size('tbl_kenyon'));
 pg_size_pretty
----------------
 1611 MB
(1 row)
mydb=# delete from tbl_kenyon where id < 10000000 and id > 9000000;
DELETE 1999998
mydb=# select pg_size_pretty(pg_relation_size('tbl_kenyon'));
 pg_size_pretty
----------------
 1611 MB
(1 row)

mydb=# select count(*) from tbl_kenyon ;
  count
----------
 18000002
(1 row)
mydb=# vacuum tbl_kenyon ;
VACUUM
mydb=# select count(*) from tbl_kenyon ;
  count
----------
 18000002
(1 row)
mydb=# select pg_size_pretty(pg_relation_size('tbl_kenyon'));
 pg_size_pretty
----------------
 1611 MB
(1 row)

删除部分数据后,对表进行vacuum操作,大小未发生变化,空间并未释放。

这次从最后删掉200W条数据:

mydb=# delete from tbl_kenyon where id > 8000000;
DELETE 2000002
mydb=# select count(*) from tbl_kenyon ;
  count
----------
 16000000
(1 row)
mydb=# select pg_size_pretty(pg_relation_size('tbl_kenyon'));
 pg_size_pretty
----------------
 1611 MB
(1 row)
mydb=# vacuum tbl_kenyon ;
VACUUM
mydb=# select pg_size_pretty(pg_relation_size('tbl_kenyon'));
 pg_size_pretty
----------------
 1450 MB
(1 row)

当删除一张表中最后的部分数据时,对该表做VACUUM操作,会释放部分空间。



2020 jeepxie.net webmaster#jeepxie.net
10 q. 0.010 s.
京ICP备10005923号