Re: Free space mapping (was Re: Multi-Versions and Vacuum)

Mark Kirkwood <markir@slingshot.co.nz> writes:
> There is also a complicating effect when rows have toasted storage, which can prevent the space being freed (I
think),
> see hackers thread entitled something like "unbounded database growth".

> I have set myself the task of examining this -

It doesn't need a lot of examination in my mind: the cause is surely
growth of the index on the toast table.  Since that index's first column
is the OID assigned to the toast item, the range of indexed values tends
to shift over time, causing the leftmost parts of the index btree to
become sparsely populated and eventually empty.  Since we don't
currently have code to collapse out empty pages in a btree, the index
grows during continued update activity, even if the total amount of data
you're storing isn't growing.  This is quite unrelated to the free space
map --- indexes don't use the FSM.

REINDEXing the toast index is the only available workaround at the
moment.

Eventually we need code to collapse out free space in indexes.

            regards, tom lane