Re: Concurrent HOT Update interference

On 10 May 2013 15:47, Greg Stark <stark@mit.edu> wrote:
> On Fri, May 10, 2013 at 3:04 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> a) Updating two separate rows that happen to be on the same block will
>> eventually cause one or both of the rows to migrate to separate blocks
>> because of 1) the inability to clean the existing block and 2) the way
>> our fsm algorithm gives you a clean new block away from other people.
>> That leads to a one-block-per-row situation, or in other words quite
>> bad bloating, which seems to be avoidable, hence this thread.
>
> This seems like a good behaviour to me. If you have N busy rows then
> having each row in its own block minimizes contention and minimizes
> the frequency of cleanups. You can't be both worried about bloating
> *and* contention -- either you have relatively few busy rows per
> processor in which case the bloat is minor and the contention is an
> issue or you have many rows in which case the contention can't be an
> issue and the bloat becomes important.

For very small tables, yes, for anything else, no.

Bloat and contention both slow you down. Difference is that bloat
slows other people down as well by filling up RAM and causing extra
I/O.

If you have roving contention your table quickly spreads out to one
table per block, which ain't great.

--Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services