Postgres - how much space is required to perform a VACUUM

Question

I know that doing a VACUUM FULL on a table will free up disk space and return it to the OS.

There's been a couple of occasions where my OS (windows) has warned me that it's running low on disc space. My response has been to quickly shut down everything I can and run a VACUUM FULL on one of the more active tables.

I've noticed that VACUUM FULL appears to require a least a few hundred Mbytes free to actually work. If I attempt a VACCUM FULL on a 1Gbyte table when I've only got say 50 Mbytes of disk space then this quickly disappears and my computer grounds to a halt pretty quickly and the VACUUM FULL won't work.

How do I free OS space in a POSTGRES database when I know there is unused space available, but there's not enough space to run a VACUUM?

(I know the correct answer is - 'don't be stupid enough to let it happen in the first place', but sh1t happens occasionally!)

Answer 1

how much space is required to perform a VACUUM

At worst pg_total_relation_size(table) * 2. Usually lots less because the new table and indexes are significantly more compact.

How do I free OS space in a POSTGRES database when I know there is unused space available, but there's not enough space to run a VACUUM?

Clean up unrelated files on the volume. Drop some tables or indexes you don't need. Move tables or indexes to other tablespaces on other storage. Expand the underlying volume.

There isn't really a good way to compact in-place and free space to the OS without first needing to allocate more space. I think most people agree this is a flaw, but nobody seems to care enough to develop any improvement for it.

Answer 2

I keep a couple gig (or more on larger systems) of random data in a "ballast" file. That way if sh1t does happen when I wasn't paying close enough attention, I can just delete that file and get some instant free space to use to work my way out of the problem. And then recreate it once done.

But having got yourself into this situation without that backstop, the best option might be to drop some indexes. It will free up space to the OS immediately, as well as make a VACUUM FULL consume less temp space while it operates, and you can create them again once the emergency has passed without needing to backup and restore any data. It is a very safe operations, as long as you have a record of the create statements so you can recreate them easily.

Another low-hanging fruit is the server log files, often stored in "pg_log" (warning: not "pg_xlog", which is indispensable to the system integrity). These human readable logs can be deleted, compressed, or moved offline.

If your "wal_level" is not already at "minimal", changing it to that could help, but make sure you understand the implications before doing that.

Note that a plain VACUUM, without FULL, can return free space to the OS if the table has a bunch of free space right at the end of the file. This is not the most common situation, but can happen if for example you were running a large bulk INSERT or COPY which aborted without committing. (which actually might not be all that rare--the bulk COPY is what ran you out of disk space, and then failed due to the out-of-disk-space). It won't shrink the indexes though, just the table.

Answer 3

If you're getting a lot of space back when you do this, it suggests that a lot of your data is changing (or, rather, being removed).

Are you running autovacuum, which would do a lot of this reorganising for you?

OK, it won't release space back to the operating system but, then, if you're filling that space back up again (to the point you need to vacuum again), then perhaps you really do need to get some more.