vacuum的使用

1.查看当前表的大小(表+索引)
db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
1487 MB
(1 row)

单独表的索引
db_test=# SELECT pg_size_pretty(pg_relation_size('tb_test'));
pg_size_pretty
----------------
1273 MB
(1 row)

表记录数:
db_test=# select count(1) from tb_test;
count
----------
10000000
(1 row)

 

2.删除部分数据
db_test=# delete from tb_test where id<=5000000;
DELETE 4999901

这个时候再次查看表的大小

db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
1487 MB
(1 row)

说明delete数据是不会是否空间的

 

3.vacuum(期间可以对表进行dml)
db_test=# vacuum tb_test;
VACUUM

瞬间完成,再次查看表占用空间情况

db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
1487 MB
(1 row)

发现没有变化,下面使用vacuum full
db_test=# vacuum full tb_test;
VACUUM
这里一直等待,执行很久,同时对该表无法进行dml操作,包括select也无法使用,这个时候空间释放出来了

db_test=# SELECT pg_size_pretty(pg_total_relation_size('tb_test'));
pg_size_pretty
----------------
748 MB
(1 row)

-- The End --