gleeco <gleeco@gmail.com> writes:
> hello - we're excited to be making a change to drop an insane wide column
> that is pg_toast table > 10T. The question is: what kind of fallout/impact
> to expect for auto-vacuum and replication after `DROP COLUMN xxx`.
I believe that exactly nothing will happen as a result of DROP COLUMN
per se. Rather, each entry in the toast table will be deleted when
the referencing row in the parent table is next updated or deleted.
So reclamation of the toast table space will be spread out over what
could be a really long interval. You could speed it up by issuing
dummy bulk updates against the parent table, or you could force the
issue by doing a VACUUM FULL, but it sounds like maybe you'd rather sit
tight and let it happen slowly.
[ thinks a bit... ] Or maybe not. The attraction of forcing the issue
with an immediate VACUUM FULL is that, for the price of rewriting the
parent table, you'd end up dropping the toast table without, I think,
incurring any update traffic against it first. If the toast table is
way bigger than the parent then this seems mighty tempting. You'd need
to be able to schedule some downtime because of VACUUM FULL's exclusive
lock, but it's likely worth trying to do that.
Unless the very-wide column is the *only* one in this table with any toast
entries, you'd likely eventually have to do a VACUUM FULL anyway to get
back the disk space eaten by the toast table --- Murphy's Law guarantees
that some of the surviving toast entries will be near the end, preventing
significant truncation of the toast table with anything less than VACUUM
FULL.
regards, tom lane