Re: Giant TOAST tables due to many almost empty pages

On Thursday 13. of May 2010 21:43:37 Tom Lane wrote:
> Rumko <rumcic@gmail.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=
=20
tables and both have an extremely high ratio of bloated vs. non-bloated=20
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 =3D 'pg_toast_1066371';"
Returns "{autovacuum_enabled=3Dfalse}" (a remnant of some testing/playing) =
and=20
not NULL (was looking at the wrong server).

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

>
>             regards, tom lane
--=20
Regards,
Rumko