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

От: Mark Mielke
Тема: Re: ??: Postgresql update op is very very slow
Дата: ,
Msg-id: 4863AD61.3070909@mark.mielke.cc
(см: обсуждение, исходный текст)
Ответ на: Re: ??: Postgresql update op is very very slow  ("Holger Hoffstaette")
Список: pgsql-performance

Скрыть дерево обсуждения

Postgresql is very slow  (bijayant kumar, )
 Re: Postgresql is very slow  (Jan de Visser, )
 Re: Postgresql is very slow  (Jeremy Harris, )
 Re: Postgresql is very slow  (, )
  Re: Postgresql is very slow  (bijayant kumar, )
   Re: Postgresql is very slow  ("Scott Marlowe", )
    Re: Postgresql is very slow  ("Scott Marlowe", )
    Re: Postgresql is very slow  (, )
     Re: Postgresql is very slow  (bijayant kumar, )
      Re: Postgresql is very slow  (, )
       Re: Postgresql is very slow  (bijayant kumar, )
       Re: [SOLVED] Postgresql is very slow  (bijayant kumar, )
      Re: Postgresql is very slow  (PFC, )
       Postgresql update op is very very slow  ("jay", )
        Re: Postgresql update op is very very slow  (Rusty Conover, )
         答复: [PERFORM] Postgresql update op is very very slow  ("jay", )
          PostgreSQL and Ruby on Rails - better accessibility  ("Amol Pujari", )
           Re: PostgreSQL and Ruby on Rails - better accessibility  (Nikhils, )
        Re: Postgresql update op is very very slow  ("Heikki Linnakangas", )
         答复: [PERFORM] Postgresql update op is very very slow  ("jay", )
          Re: [PERFORM] 答复: [PERFORM] Postgresql update op is very very slow  ("Pavan Deolasee", )
           Re: [PERFORM] Re: [PERFORM] 答复: [PERFORM] Postgresql update op is very very slow  ("Scott Marlowe", )
          Re: ??: Postgresql update op is very very slow  ("Heikki Linnakangas", )
          Re: 答复: [PERFORM] Postgresql update op is very very slow  (Tom Lane, )
           Re: [PERFORM] 答复: [PERFORM] Postgresql update op is very very slow  ("Scott Marlowe", )
        Re: Postgresql update op is very very slow  (Greg Smith, )
      Re: ??: Postgresql update op is very very slow  ("Holger Hoffstaette", )
       Re: ??: Postgresql update op is very very slow  (Craig Ringer, )
        Re: ??: Postgresql update op is very very slow  (Andrew Sullivan, )
        Re: ??: Postgresql update op is very very slow  (Greg Smith, )
       Re: ??: Postgresql update op is very very slow  (Andrew Sullivan, )
       Re: ??: Postgresql update op is very very slow  (Mark Mielke, )
       Re: ??: Postgresql update op is very very slow  (Greg Smith, )
    Re: Postgresql is very slow  ("Ian Barwick", )
 Re: Postgresql is very slow  ("Gregory S. Youngblood", )

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 <>



В списке pgsql-performance по дате сообщения:

От: kevin kempter
Дата:
Сообщение: Federated Postgresql architecture ?
От: "Jonah H. Harris"
Дата:
Сообщение: Re: Federated Postgresql architecture ?