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?
Laurenz Albe
62k4 gold badges57 silver badges93 bronze badges
asked Sep 17, 2019 at 11:48
1 Answer 1
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.
answered Sep 17, 2019 at 12:49
-
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– ConanTheGerbil2019年09月17日 13:09:38 +00:00Commented Sep 17, 2019 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– Laurenz Albe2019年09月17日 13:19:05 +00:00Commented Sep 17, 2019 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– ConanTheGerbil2019年09月17日 13:25:26 +00:00Commented Sep 17, 2019 at 13:25
lang-sql