Vacuum full space usage when removing indicies and toast

Question

I think my question is if I drop constraints and or indices can that space be used by a vacuum full?

However I don't really know enough to be sure that's the right question.

I have a Heroku managed DB, it has allocated storage of 768GB

I am using ~500GB(there's a big full vacuum running it'll be clearer after that)

Leaving ~268GB available (edit: vacuum is done I actually have 289GB free)

There is this other table that is getting a diet, it has:

• pg_relation_size = 274GB
• pg_table_size = 283GB
• pg_indexes_size = 74GB
• pg_total_relation_size = 357GB

I'm going to drop the column that is producing most of the toast (moving the data to S3) and two of the FK's which show in \d as having indicies and constraints.

I'm looking at how can I full vacuum this thing afterward. (I will do regular vacuum first)

My understanding of full vacuum is basically it makes a whole other copy.

If my changes get the pg_total_relation_size under the ~268GB free space then presumably I'm good. But that seems unlikely.

So I'm wondering if the full vacuum can use any of the space that gets marked as reusable by the regular vacuum, if so there might be enough room anyway.

So will the vacuum be able to make use of the space freed up by any of the above?

Is there anywhere else I should look for freeing up some space?

Also is there anyway to know in advance if I'm going to have enough space for the full vacuum?

Of course my fall back plan is to eat the time and cost for moving the whole thing to a bigger database plan, vacuuming it and then moving it back.

edit: what if I drop all the indicies and constraints, full vacuum and then add them back?

then the "copy" doesn't need space for those

btw I'm able to shut down all access to this table for a day or two if need be, hence my ability to full vacuum it

Answer 1

Dropping an index will free up the roughly twice the space for VACUUM FULL to use. The space used by the index is immediately freed and available for reuse, and the new copy of the table will not have that index automatically built for it, reducing that space too. But both components of this space are going to be visible to you. Once you drop the index, you should immediately see more space available in the filesystem, and less space taken by pg_total_relation_size.

Dropping constraints will only free up space if that automatically drops a backing index. Dropping FK constraints doesn't do that, so it frees no space.

The space freed up internally by VACUUM will not be available for VACUUM FULL to use. If the VACUUM returns any space back to the filesystem, that space will be available, but will also be apparent beforehand that space has been freed, just like it is for dropped indexes.

Space that is freed internally by VACUUM will not be copied to the new table, so the space needed for that is lower. But that data would not have been copied anyway. If it was "freeable" by VACUUM, it will not be copied, regardless of whether regular VACUUM was actually run. This internally available space should show up in pgstattuple. (It will also show up in pg_freespacemap, but only after a regular vacuum has been run to update the freespace map).

If you drop a column, that space is not released from the old table but it will not be needed in the new table. But the amount of this freed-up space is not visible by any mechanism that I know of, other than actually copying the table.

Answer 2

Plain VACUUM generally does not shrink the table size, it only makes more room within the table.

VACUUM (FULL) writes a new copy of the files, so it cannot use the space that is occupied by other files (tables). The 268 GB free space are all that VACUUM (FULL) can use.

Exporting, dropping and restoring the table might be the way to go.