From: Mark Mielke <mark@mark.mielke.cc>
To: Holger Hoffstaette <holger@wizards.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: ??: Postgresql update op is very very slow
Date: Thu, 26 Jun 2008 10:53:21 -0400
Message-ID: <4863AD61.3070909@mark.mielke.cc> (raw)
In-Reply-To: <pan.2008.06.26.12.40.59.151500@wizards.de>
References: <473587.97248.qm@web32701.mail.mud.yahoo.com>
<op.uc83c7m2cigqcu@apollo13.peufeu.com>
<004b01c8d671$3dec1630$0544000a@hz.ali.com>
<486219B6.7050003@enterprisedb.com>
<005101c8d773$ff81b2c0$0644000a@hz.ali.com>
<48638939.5080500@enterprisedb.com>
<pan.2008.06.26.12.40.59.151500@wizards.de>
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?
>
My opinion:
Any system that provides cheap UPDATE operations is either not ACID
compliant, or is not designed for highly concurrent access, possibly
both. By ACID compliant I mean that there both the OLD and NEW need to
take space on the hard disk in order to guarantee that if a failure
occurs in the middle of the transaction, one can select only the OLD
versions for future transactions, or if it fails after the end fo the
transaction, one can select only the NEW versions for future
transactions. If both must be on disk, it follows that updates are
expensive. Even with Oracle rollback segments - the rollback segments
need to be written. Perhaps they will be more sequential, and able to be
written more efficiently, but the data still needs to be written. The
other option is to make sure that only one person is doing updates at a
time, and in this case it becomes possible (although not necessarily
safe unless one implements the ACID compliant behaviour described in the
previous point) for one operation to complete before the next begins.
The HOT changes introduced recently into PostgreSQL should reduce the
cost of updates in many cases (but not all - I imagine that updating ALL
rows is still expensive).
There is a third system I can think of, but I think it's more
theoretical than practical. That is, remember the list of changes to
each row/column and "replay" them on query. The database isn't ever
stored in a built state, but is only kept as pointers that allow any
part of the table to be re-built on access. The UPDATE statement could
be recorded cheaply, but queries against the UPDATE statement might be
very expensive. :-)
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@mark.mielke.cc, holger@wizards.de
Subject: Re: ??: Postgresql update op is very very slow
In-Reply-To: <4863AD61.3070909@mark.mielke.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox