Re: Giant TOAST tables due to many almost empty pages

On Thursday 13. of May 2010 17:24:47 Tom Lane wrote:
> Rumko <rumcic(at)gmail(dot)com> writes:
> > Tom Lane wrote:
> >> There's something extremely wacko about that vacuum output.
> >
> > Regarding storage paramaters, you mean ALTER TABLE x SET STORAGE...? Then
> > no.
>
> No, I was wondering about ALTER TABLE ... SET (fillfactor = n).
> It would be worth checking to see if you get a nonnull result from
> select reloptions from pg_class where relname = 'pg_toast_1066371';

Returns NULL.

> The funny behavior would be partially explained if the toast table has a
> ridiculously small fillfactor --- in particular that would explain
> VACUUM claiming there's no free space, as well as the bloat caused by
> having only one useful toast row per page.
>
> There's still the question of why it's toasting such short values at
> all, but I think I see that: your table rows contain 500 non-toastable
> columns, either bigints or timestamps, each of which requires 8 bytes.
> So assuming those are all non-null, that's 4000 unremovable bytes right
> there. The toast code then goes nuts trying to push out all the
> toastable columns to bring the tuple down to target size; it's going to
> push columns to toast that ordinarily wouldn't get pushed.

This does not bother me, the amount in the toast tables is miniscule and comes
up to ~275MB at the end and as far as performance goes, there were no
noticable problems (it's quite fast).

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

>
> Maybe the toast heuristics should be modified to cope a bit more
> gracefully with a case like this. Pushing out a relatively small column
> in order to get down from 4200 to 4100 bytes doesn't seem like a win.
> OTOH, this is by no stretch of the imagination a good schema design, so
> I'm not sure how excited people will be about making it perform better.
>
> regards, tom lane

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.

From what I can tell, the problem seems to be in the fsm? Used pg_freespace
from the pg_freespacemap module and it claims that there are no pages in the
toast table that have any free space left (on the other hand vacuum shows
that each page has a max of 122 bytes of data ... so there should still be
~8000 bytes of free space left, right?). I tested this on a table that I
already ran VACUUM FULL and CLUSTER on it and on a table that I didn't, but
for both, pg_freespace claimed that all pages were full for the toast table.
--
Regards,
Rumko