1

If I do a VACUUM on a Postgres table (without the FULL option) I know that it should make any free-space available to the database, but not the operating system.

Should it make the freespace available to any database object? or just the table that's been vacuumed?

0

After a VACUUM the table will be as big as before, just more of the space will be free and available for future INSERTs and UPDATEs. In other words, the file won't shrink (1). So the space will not be available for other tables, only for the table that has been vacuumed.

Note (1): Sometimes the file will shrink. If the last block is completely empty after the VACUUM, PostgreSQL will try to truncate all empty blocks off the end of the table.

  • So, If I want to do a space-consuming operation on TABLE_A, there's no point in doing a simple VACUUM on TABLE_B, TABLE_C, TABLE_D or TABLE_E because it won't free up any space for TABLE_A ? – ConanTheGerbil Sep 17 at 13:09
  • Yes, exactly. You can use the pg_total_relation_size() function to measure the size of a table and all that goes with it. – Laurenz Albe Sep 17 at 13:19
  • Thanks, wish I'd known that years ago! That's a big revelation to me, for to long I've assumed that if I needed DB space for a big operation then a simple VACUUM on all the other tables would do the trick. Obviously not! – ConanTheGerbil Sep 17 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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