Re: pessimal trivial-update performance - Mailing list pgsql-hackers

From Andres Freund
Subject Re: pessimal trivial-update performance
Date
Msg-id 201007051228.39200.andres@anarazel.de
Whole thread Raw
In response to Re: pessimal trivial-update performance  ("Pierre C" <lists@peufeu.com>)
List pgsql-hackers
On Monday 05 July 2010 12:11:38 Pierre C wrote:
> > The problem can generally be written as "tuples seeing multiple
> > updates in the same transaction"?
> > 
> > I think that every time PostgreSQL is used with an ORM, there is
> > a certain amount of multiple updates taking place. I have actually
> > been reworking clientside to get around multiple updates, since they
> > popped up in one of my profiling runs. Allthough the time I optimized
> > away ended being both "roundtrip time" + "update time", but having
> > the database do half of it transparently, might have been sufficient
> > to get me to have had a bigger problem elsewhere..
> > 
> > To sum up. Yes I think indeed it is a real-world case.
> > 
> > Jesper
> 
> On the Python side, elixir and sqlalchemy have an excellent way of
> handling this, basically when you start a transaction, all changes are
> accumulated in a "session" object and only flushed to the database on
> session commit (which is also generally the transaction commit). This has
> multiple advantages, for instance it is able to issue multiple-line
> statements, updates are only done once, you save a lot of roundtrips, etc.
> Of course it is most of the time not compatible with database triggers, so
> if there are triggers the ORM needs to be told about them.
Its also not concurrency safe in many cases.

Andres


pgsql-hackers by date:

Previous
From: Jesper Krogh
Date:
Subject: Re: pessimal trivial-update performance
Next
From: Greg Smith
Date:
Subject: Re: Table partitioning - is anything coming?