This article is half-done without your Comment! *** Please share your thoughts via Comment ***
How to perform VACUUM FULL without disk space?
The above question is common for all PostgreSQL DBA. This is really a 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 by removing dead rows physically.
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 our Postgres Database Server, we require VACUUM FULL on one table, and this table size is 13GB, and we don’t have enough free space on hard-disk.
I worked around this problem and created one of the best solutions to resolve this problem.
To solve this problem, we need 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 cannot be accessible.
Now, add new hard disk and make it as table-space.
Create a new tablespace:
|
1 |
CREATE TABLESPACE temptablespace LOCATION '/path/../'; |
Check your table current tablespace:
|
1 |
SELECT tablespace FROM pg_tables WHERE tablename = 'mybigtable'; |
If it is NULL, it has a default tablespace.
Move table to new tablespace:
|
1 |
ALTER TABLE mybigtable SET TABLESPACE temptablespace; |
Perform VACUUM FULL:
|
1 |
VACUUM FULL mybigtable; |
Move table to old tablespace:(moving to pg_default)
|
1 |
ALTER TABLE mybigtable SET TABLESPACE pg_default; |
Drop that temp table space:
|
1 |
DROP TABLESPACE temptablespace; |
