Re: Giant TOAST tables due to many almost empty pages

On Thursday 13. of May 2010 21:43:37 Tom Lane wrote:
> Rumko <rumcic(at)gmail(dot)com> writes:
> > As far as I'm concerned, the TOAST table itself does not bother me even
> > if I have a few bytes per row there, only the part where VACUUM claims no
> > free space even though pages are more empty than not.
>
> Yeah, that's the still-unexplained part. It is certainly acting like
> there's a very small fillfactor setting for that toast table :-(.
> Don't understand where that's coming from. Is this happening for
> more than one table?

Yes, 2 for each day of data (both with a lot higher column count than other
tables and both have an extremely high ratio of bloated vs. non-bloated
sizes).

>
> > From what I can tell, the problem seems to be in the fsm?
>
> No. What VACUUM is printing is from direct inspection of the table,
> it's not gone through the fsm. There is certainly free space on each
> toast table page given the reported tuple sizes, but seemingly the
> free space is less than what it thinks it should reserve for fillfactor;
> that would cause VACUUM to report the free space as zero.
>
> Do *any* of the rows in pg_class have non-null reloptions?

First of all, really sorry.
"select reloptions from pg_class where relname = 'pg_toast_1066371';"
Returns "{autovacuum_enabled=false}" (a remnant of some testing/playing) and
not NULL (was looking at the wrong server).

As for the others in pg_class, there is an index for a totally different table
which has "{fillfactor=90}". There are a few more tables (main and toast)
with "{autovacuum_enabled=false}" and that's it (others have NULL).

>
> regards, tom lane
--
Regards,
Rumko