Zoltan Boszormenyi <zb@cybertec.at> writes:
> we came across a database where a table had a toasted table,
> keeping huge amounts of disk space allocated. However,
> the table's current definition didn't explain why there was
> a toasted table. Then upon some experiments, it struck me.
> There _was_ a toasted field but as the schema was modified,
> the fields was dropped, leaving only inline stored fields.
> VACUUM [FULL] [ANALYZE] didn't cleaned up the space
> that was used by the toasted table. My tests were done on 8.3.3.
This is not a bug; it is operating as designed. Observe the statement
in the NOTES section of the ALTER TABLE page:
The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations.
Subsequentinsert and update operations in the table will store a null value for the column. Thus, dropping a column
isquick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped
columnis not reclaimed. The space will be reclaimed over time as existing rows are updated.
... and it goes on to point out how to force immediate space reclamation
if you need that. These statements apply independently of whether any
particular value is toasted or not.
The reason for this choice is that reclaiming the space immediately
would turn DROP COLUMN from a quick operation into a slow one, as it
would have to grovel over every row of the table looking for TOAST
pointers.
> Judging from that, the toasted table
> cleanup may be part of ALTER TABLE DROP COLUMN.
That would only help if you were dropping the last potentially-toastable
column of a table. And implementing it would require introducing weird
corner cases into the tuple toaster, because it might now come across
TOAST pointers that point to a no-longer-existent table, and have to
consider that to be a no-op instead of an error condition.
regards, tom lane