Re: Giant TOAST tables due to many almost empty pages - Mailing list pgsql-bugs

On Thursday 13. of May 2010 17:24:47 Tom Lane wrote:
> Rumko <rumcic@gmail.com> writes:
> > Tom Lane wrote:
> >> There's something extremely wacko about that vacuum output.
> >
> > Regarding storage paramaters, you mean ALTER TABLE x SET STORAGE...? Th=
en
> > no.
>
> No, I was wondering about ALTER TABLE ... SET (fillfactor =3D n).
> It would be worth checking to see if you get a nonnull result from
>     select reloptions from pg_class where relname =3D '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 co=
mes=20
up to ~275MB at the end and as far as performance goes, there were no=20
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=20
exception of the 2 tables that have giant toast tables) to be the most usef=
ul=20
(administration vs. speed vs. ease of use). There will be more=20
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=20
have a few bytes per row there, only the part where VACUUM claims no free=
=20
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=
=20
from the pg_freespacemap module and it claims that there are no pages in th=
e=20
toast table that have any free space left (on the other hand vacuum shows=
=20
that each page has a max of 122 bytes of data ... so there should still be=
=20
~8000 bytes of free space left, right?). I tested this on a table that I=20
already ran VACUUM FULL and CLUSTER on it and on a table that I didn't, but=
=20
for both, pg_freespace claimed that all pages were full for the toast table.
--=20
Regards,
Rumko