I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is that I'm not sure what will happen if I run VACUUM FULL and there is not enough free space to make a copy of the table.

I understand that VACUUM FULL should not be used but I figured it was the best option in this scenario.

Any ideas would be appreciated.

I'm using PostgreSQL 9.0.6


6 Answers 6


Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis.

1 Backup all of the databases on your postgresql server

You will want to backup all of your databases to a partition that has enough space. If you were on Linux, you can use gzip to further compress the backup to save space

su - postgres
pg_dumpall | gzip -9 > /some/partition/all.dbs.out.gz

2 Backup your configuration files

cp /path/to/postgresql/data_directory/*.conf /some/partition/

3 Stop Postgresql

pg_ctl -D /path/to/postgresql/data_directory stop

4 erase the contents of the data directory

rm -Rf /path/to/postgresql/data_directory/*

5 Run initdb to reinitalize your data directory

initdb -D /path/to/postgresql/data_directory

6 Restore configuration files

cp /some/partition/*.conf /path/to/postgresql/data_directory/*.conf 

7 Start Postgresql

pg_ctl -D /path/to/postgresql/data_directory start

8 Restore the dump of all the databases you made

gunzip -c /some/partition/all.dbs.out.gz | psql
  • 1
    Thanks, this is what I ended up doing, with a couple of differences. I just dropped the database after backing it up. Then created a new one and restored it. Commented Apr 30, 2012 at 15:51
  • You're welcome. I figured that removing the contents of the data directory and doing initdb would have been sufficient though. Commented Apr 30, 2012 at 15:56
  • 2
    Worked great, I just recommend skipping the gzip part to save time. Commented Nov 4, 2016 at 13:50
  • 1
    @CraigEfrein it would be suffucient in that a sledgehammer can open a peanut, drop database basically tells postgres to do the same thing, but only to the files of one database.
    – Jasen
    Commented Apr 14, 2022 at 1:34
  • @RafaelBarbosa, gzip can be useful if there's not much disk space available, less of an issue if external storage can easily be added.
    – Jasen
    Commented Apr 14, 2022 at 1:36

Method with little to no downtime (minimal user-impact)

NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeettty sure though it will work on 9.0 though.

CAUTION (As noted in the comments by @erny):

Note that high CPU load due to I/O operations may be expected.

You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of exclusive locks. But only on the table you are vacuuming. So all that will happen is that client queries will simply wait for the lock to be acquired if they access the table in question. You don't need to close existing connections.

One thing to be aware of though, is that moving the table and the vacuum full will themselves need to wait for an exclusive lock first!

First, you obviously need some additional storage. As Stéphane mentions in the comments, this needs to be at least twice as big as the table in question as VACUUM FULL does a full copy. If you are lucky and can dynamically add a disk to the machine, do that. In the worst case you can just attach an USB disk (risky and slow though)!

Next, mount the new device and make it available as tablespace:

CREATE TABLESPACE tempspace LOCATION '/path/to/new/folder';

You can list the tablespaces easily using:


Double-check the current tablespace of your table (you need to know where to move it back to):

SELECT tablespace FROM pg_tables WHERE tablename = 'mytable';

If it's NULL, it will be in the default tablespace:

SHOW default_tablespace;

If that is NULL as well, it will likely be pg_default (check the official docs in case it's changed).

Now move the table over:

COMMIT;  -- if autocommit is off

Vacuum it:

VACUUM FULL mytable;

Move it back:

-- assuming you are using the defaults, the tablespace will be "pg_default".
-- Otherwise use the value from the SELECT we did earlier.
ALTER TABLE mytable SET TABLESPACE pg_default;
COMMIT;  -- if autocommit is off

Remove the temporary space:

  • NB: the move appears to use more disk space in the original data directory... Commented Nov 24, 2015 at 11:13
  • Just tested it on 9.3 and it works like charm. Commented Jul 27, 2016 at 6:10
  • Successfully used in production on 9.1. After changing tablespace the original used space is released. Note that high CPU load due to I/O operations may be expected.
    – erny
    Commented Jul 29, 2016 at 19:06
  • 2
    Amazing tips, thanks for this detailed explaination. Note that on the temporary tablespace you'll need at least size of table x 2, since VACUUM FULL is making a full copy of the table.
    – Stéphane
    Commented Mar 8, 2018 at 14:27
  • 1
    Thank you @exhuma for really nice tips. I'd like to mention that created folder should have proper rights set, so before running create tablespace (...) command there should be check if directory owner is set to postgres (chown postgres:postgres /path/to/new/folder) Commented Jun 28, 2022 at 19:12

Quick and dirty:

  • Stop Postgres
  • Move the main database directory to another disk where there's enough room for vacuuming
  • In the original location of main, add a symlink to the new location
  • Vacuum
  • Delete the symlink and move the main directory back to its original location
  • Start Postgres


$ service postgresql stop $ mv /var/lib/postgresql/9.5/main /mnt/bigdisk $ ln -sr /mnt/bigdisk/main /var/lib/postgresql/9.5 $ vacuumdb --all --full $ rm /var/lib/postgresql/9.5/main $ mv /mnt/bigdisk/main /var/lib/postgresql/9.5 $ service postgresql start


If you have the disk space to do a dump and restore, you should have the disk space to do a vacuumdb --full. The problem is that vacuumdb --full will make a copy of the entire data file. So, what you could do is:

  1. copy the files that hold the huge table to a different drive, for example, a slower, bigger drive.
  2. make symbolic links from the original location to the new place on the other drive.
  3. run vacuumdb --full, now it should read the data from the other disk, and write the final table to your original data disk.

After using exhuma's answer above to move the tablespace of many tables to a different disk, the following can be helpful to:

  • Vacuum all tables, one at a time.
  • For all moved tables, move their tablespace back to the default pg_default.

Run vacuumdb on all tables, one at a time

psql -U postgres -d $db -tA -c "SELECT table_name
  FROM information_schema.tables WHERE table_schema='public'" \
| while read -r tbl; do
    echo "==Table $tbl"
    vacuumdb -U postgres -d $db -t "\"$tbl\"" --full --verbose 2>&1 
  done \
| tee -a vacuum-$db.log

Save commands to move back all moved tables

This finds all tables which use a tablespace named "tempspace", and writes to a file the SQL commands to move them back to pg_default

psql -U postgres -d $db -tA -c "SELECT tablename from pg_tables WHERE tablespace='tempspace';" \
| while read -r tbl; do
    echo "ALTER TABLE \"$tbl\" SET tablespace pg_default;"
  done \

Finally, run the SQL commands in the file

psql -U postgres -d "$db" -a -f my_pg_restore_tblspc.sql

I can recommend https://github.com/reorg/pg_repack. It can be used to restore the physical order of a single table using -t CLI option.

  • pg_repack needs free space too...
    – jimis
    Commented Dec 22, 2023 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.