Re: Same-page UPDATEs in bloated tables

In message <24849(dot)1160940026(at)sss(dot)pgh(dot)pa(dot)us>, Tom Lane writes:
>Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Using the same page for an updated tuple is generally a useful
>> optimization, so I don't think you have much hopes for having it
>> disabled.
>
>Especially not since there's no very reasonable way for anything as
>low-level as heap_update to know that "the table is way beyond its
>nominal size". What's nominal size anyway?

Thanks - yes, I don't know enough about PostgreSQL internals to
suggest a good approach, but thought there might be a small change
possible that would cause the table size to eventually recover
itself without manual intervention. For example even if the same-page
optimisation was only disabled on the very last page and for 1 in
10 updates then the table size would start to shrink. Even better
would be a way for this to happen more aggressively when the table
is very sparsely populated relative to the target fill factor.

Just to explain a bit more about the original access pattern, we
were only performing UPDATEs (no INSERT/DELETEs), so the reason
that the table gets bloated to begin with is that vacuuming is
ineffective during long transactions such as backups. In our
particular case, full vacuuming is not an acceptable option due to
the exclusive locking, so to handle the rare table bloat problems
we currently have to just switch all our UPDATEs to use slower
INSERT/DELETE operations instead.

>Actually, the recent thinking in this area has been to try to *increase*
>the usage of same-page UPDATE, so as to prevent table bloat in the first
>place ...

If you mean some kind of in-place update, then that would solve the
problem we were seeing too if it can prevent table bloat of high
UPDATE churn tables during long trasactions such as backups.

Thanks,

Ian