Re: Giant TOAST tables due to many almost empty pages

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

You might want to think about collapsing all those standalone bigint
columns into an array.

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