Re: Toasted table not deleted when no out of line columns left

On Sun, 2008-09-21 at 12:05 -0400, Tom Lane wrote:
> Zoltan Boszormenyi <zb(at)cybertec(dot)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. Subsequent insert and
> update operations in the table will store a null value for the
> column. Thus, dropping a column is quick but it will not immediately
> reduce the on-disk size of your table, as the space occupied by the
> dropped column is not reclaimed. The space will be reclaimed over
> time as existing rows are updated.

And it seems that it is never reclaimed (instead of "reclaimed over
time" as claimed in docs) if the column happens to have been toasted.

> ... 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.

Are you sure ?

how do you explain the above "VACUUM [FULL] [ANALYZE] didn't cleaned up
the space" claim ?

Is it just not true ?

Or an overlooked corner case / implementation detail ?

----------------
Hannu