VACUUM FULL - > JUST LIKE MOVE COMMAND IN ORACLE . IT TAKES MORE TIME, BUT IT RETURNS THE SPACE TO OS BECAUSE OF ITS COMPLEX ALGORITHM.
IT also requires additional disk space , which can store the new copy of the table., until the activity is completed. Also it locks the table exclusively, which block all operations on the table .
dbaclass=#
VACUUM FULL
dbatest.emptab;
VACUUM
-- Check existing space and delete some data:
dbaclass=#
select pg_size_pretty(pg_relation_size('dbatest.emptab'));
pg_size_pretty
----------------
114 MB
(1 row)
dbaclass=#
delete from dbatest.emptab where oid=13634;
DELETE 131072
-- We can observe size is still same:
dbaclass=#
select pg_size_pretty(pg_relation_size('dbatest.emptab'));
pg_size_pretty
----------------
114 MB
(1 row)
-- Run vacuum full and observe the space usage:
dbaclass=#
VACUUM FULL dbatest.emptab;
VACUUM
dbaclass=#
select pg_size_pretty(pg_relation_size('dbatest.emptab'));
pg_size_pretty
----------------
39 MB ---- > from 114MB it came down to 39 MB.
(1 row)