Re: Unbounded (Possibly) Database Size Increase - Toasting

<markir(at)slingshot(dot)co(dot)nz> writes:
> The toast table gets about 90 percent of the growth, followed by the toast
> index at about 9 percent. The actual table + primary key stay at about 2M each.

Odd. I wonder whether you are looking at an unintended behavior of the
free space map's thresholding mechanism. The toast table will generally
have large tuples of consistent size (about 2K each). This will cause
the FSM threshold for whether to remember a page to approach 2K, which
probably will mean that we forget about pages that could still hold one
toast tuple. That might be enough to cause the growth. It may be
worth playing around with the details of the threshold-setting policy.

In particular, I'd suggest altering the code in GetPageWithFreeSpace
and RecordAndGetPageWithFreeSpace (in
src/backend/storage/freespace/freespace.c) to make the threshold
converge towards something less than the average request size, perhaps
average/2, which you could do with

- cur_avg += ((int) spaceNeeded - cur_avg) / 32;
+ cur_avg += (((int) spaceNeeded)/2 - cur_avg) / 32;

Possibly the initial threshold set in create_fsm_rel also needs to be
smaller than it is. Not sure about that though.

Let me know how that affects your results ...

regards, tom lane