[Postgres] Reclaiming space from bloated table without enough space left

postgres_emergency_vacuum_full_if_not_enough_space_is_left.md

Reclaiming space from bloated table without enough space left

The vacuum full needs two-times the table size to perform on it because it first makes full physical copy of the table. If you are running out of space and in need of reclaiming the space occupated by dead tuples from a bloated database table but you can not because of lacking free space in the table's namespace here is how you can work around that (assuming you some additional disk space resources can be added to the server). It is also the least disruptive method of doing it (in comparison to the obvious dump and restore et cetera).

---

1. Add some (temporary) disk or disk resources.

2. Create and format new partition on the server and make filesystem of choice on it. Mount it on a <temporary tablespace path>.

3. Create new <temporary tablespace> withing the database:

   create tablespace
     <temporary tablespace>
   location
     '<temporary tablespace path>';

   a. List tablespaces to verify (in psql(1)):

   \db

   Check carefuly!

4. Check where the original <table> resides (the <original tablespace>):

   select
     t.tablespace as tablespace
   from
     pg_tables as t
   where
     t.tablename = '<table>';

   Value of null it means the default tablespace which is:

   show default_tablespace;

5. Move the <table> to the <temporary tablespace>:

   alter table
     <table>
   set
     tablespace `<temporary tablespace>`;

6. Do the vacuum full:

   vacuum full `<table>`;

   Note that this operation is CPU and I/O intensive and will bring your server to high system load.

---

Now you can either keep the <table> in it's new tablespace (and path) or you can move if back to the <original tablespace> (and path).

---

6. Once vacuum full succeeds move the <table> back to it's original place:

   alter table
     <table>
   set
     tablespace `<original tablespace>`;

7. Check where the <table> resides now (should be in the default tablespace):

   select
     t.tablespace as tablespace
   from
     pg_tables as t
   where
     t.tablename = '<table>';

8. Drop the (temporary) tablespace:

   drop tablespace <temporary tablespace>;

   a. And verify it's really gone:

   \db

9. Finaly un-mount the <temporary tablespace path> and remove the device resource from the server.