16

From the PostgreSQL 10.4 manual regarding a full vacuum:

Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are complete

I've read in this in many different places and phrased in a variety of ways. Some indicating that the space required is at most equal to the size of the vacuumed table. Hinting that it may only require enough space to store the resulting vacuumed table, i.e. of size in the range [0-size_of_original_table], depending on how many dead rows are in the table.

My question is: Will doing a full vacuum of a table always require a space equal to the original table size or is it dependent on the number of live rows in the table?

Thrasi
  • 418
  • 1
  • 5
  • 15

2 Answers2

22

The additional space required by VACUUM (FULL) depends on the number of live rows in the table.

What happens during VACUUM (FULL) is that a new copy of the table is written. All live tuples (= row versions) and the dead tuples that cannot be removed yet will be written to this new copy.

When the transaction finishes, the old copy will be removed.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 3
    thanks for the correction. So given a 1GB table with 500MB of live rows and 500MB of dead rows, VACCUM FULL on the table will incrementally allocate space while copying the live rows to a new table until finished when it reaches 500MB? As opposed to allocating 1GB, copying the live rows and then returning any excess allocated space, 500MB in this case. – Thrasi Jul 16 '18 at 14:00
  • failed to tag you above – Thrasi Jul 16 '18 at 14:19
  • Very helpful answer, you can check how many tuples are dead by querying pg_stat_user_tables (or pg_stat_sys_tables in the case of TOAST's https://dba.stackexchange.com/a/223225/94649 ). – odinho - Velmont Jul 23 '20 at 16:07
  • 1
    @Thrasi You understood correctly. The disk space used is not pre-allocated, but increases as the new file is written. – Laurenz Albe Aug 10 '20 at 06:11
10

It is recommended to have the free space at least equal to size of largest table in the database.

i.e, if your database size is 10GB and the largest table size on your database is 2GB. Then you must have at least 2GB of extra space on your disk, in order to complete the vacuum successfully.

Because VACUUM FULL will create a new copy of the table, excluding the dead rows and then remove the existing tables.

Vipul Shukla
  • 165
  • 4
  • 3
    Recommended because if the largest table contains only live rows you need that much space. But if you know your largest contains mostly dead rows you may get away with less, is what I've gathered from this. – Thrasi Aug 01 '18 at 07:11
  • Yes, possibly. If you know how much active data is present in that table, then the free space should be more than it. But, it the size of active data in this table is less than the data on the other tables, then you will also have to think about the space required by it. – Vipul Shukla Aug 14 '18 at 22:12