Re: Giant TOAST tables due to many almost empty pages

Rumko <rumcic(at)gmail(dot)com> writes:
> On Thursday 13. of May 2010 17:24:47 Tom Lane wrote:
>> You might want to think about collapsing all those standalone bigint
>> columns into an array.

> The current design is not final yet, but for now it has proven (with the
> exception of the 2 tables that have giant toast tables) to be the most useful
> (administration vs. speed vs. ease of use). There will be more
> experimentation.

Well, as long as you aren't accessing any of the variable-width columns
often, the performance hit of having them out-of-line isn't going to be
a problem for you.

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

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

regards, tom lane