Re: MVCC performance issue

On Thu, Nov 11, 2010 at 20:25, Kyriacos Kyriacou
<kyriacosk(at)prime-tel(dot)com> wrote:
> By definition of MVCC, when an UPDATE is performed, PostgreSQL creates a
> new copy of the row in a new location.

> result is to have huge fragmentation on table space, unnecessary updates
> in all affected indexes, unnecessary costly I/O operations, poor
> performance on SELECT that retrieves big record sets (i.e. reports etc)
> and slower updates.

Have you tried reducing the table fillfactor and seeing if HOT update
ratio increases?

PostgreSQL 8.3 introduced HOT updates as kind of a middle ground -- if
the update doesn't affect indexed columns and there's enough space in
the same page that is being updated, then the new version will be
written in the same page and indexes don't need to be touched at all.

Regards,
Marti