Re: ??: Postgresql update op is very very slow

Holger Hoffstaette wrote:
> Hi -
>
> I have been following this thread and find some of the recommendations
> really surprising. I understand that MVCC necessarily creates overhead,
> in-place updates would not be safe against crashes etc. but have a hard
> time believing that this is such a huge problem for RDBMS in 2008. How do
> large databases treat mass updates? AFAIK both DB2 and Oracle use MVCC
> (maybe a different kind?) as well, but I cannot believe that large updates
> still pose such big problems.
> Are there no options (algorithms) for adaptively choosing different
> update strategies that do not incur the full MVCC overhead?

I think Pg already does in place updates, or close, if the tuples being
replaced aren't referenced by any in-flight transaction. I noticed a
while ago that if I'm doing bulk load/update work, if there aren't any
other transactions no MVCC bloat seems to occur and updates are faster.

I'd be interested to have this confirmed, as I don't think I've seen it
documented anywhere. Is it a side-effect/benefit of HOT somehow?

--
Craig Ringer