PostgreSQL: Execute VACUUM FULL without Disk Space

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:

Check your table current tablespace:

If it is NULL, it has a default tablespace.

Move table to new tablespace:

Move table to old tablespace:(moving to pg_default)
Drop that temp table space:
Anvesh Patel

Leave a Reply

3 Comments on "PostgreSQL: Execute VACUUM FULL without Disk Space"

Notify of
Sort by:   newest | oldest | most voted

I am regular visitor, how are you everybody? Thiis post posted at this
web page is truly pleasant.


I had passed throught a situation exactly like that, a table became to big, back then I didn´t what to do, well, I end up increasing the disk where the database is allocated and THEN I executed the vaccum. Your way is better and technical.