How to perform VACUUM FULL without disk space?
This question is common for all PostgreSQL DBA. This is really very important topic that I am going to discuss in this post.
We require VACUUM and VACUUM FULL in PostgreSQL because of MVCC Architecture.
You can visit this article for MVCC:
Postgres VACUUM FULL reclaims all free space released by VACUUM to removing dead rows.
This method also requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete.
In one of the our Postgres Database Server we require VACUUM FULL on one table and this table size is 13GB and we don’t have more space on hard-disk.
I have worked around this problem and created one of the best solution to resolve this problem.
As per the nature of VACUUM FULL, we require additional storage like any other network drive or portable hard disk.
Please do not forget that VACUUM FULL requires an exclusive lock on the table so during this operation your table can not be accessible.
Now, add new hard disk and make it as table-space.
Create new tablespace:
CREATE TABLESPACE temptablespace LOCATION '/path/../';
Check your table current tablespace:
SELECT tablespace FROM pg_tables WHERE tablename = 'mybigtable';
If it is NULL, it has a default tablespace.
Move table to new tablespace:
ALTER TABLE mybigtable SET TABLESPACE temptablespace;
Perform VACUUM FULL:
VACUUM FULL mybigtable;
Move table to old tablespace:(moving to pg_default)
ALTER TABLE mybigtable SET TABLESPACE pg_default;
Drop that temp table space:
DROP TABLESPACE temptablespace;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.