Skip site navigation (1) Skip section navigation (2)

Re: pessimal trivial-update performance

From: Rainer Pruy <Rainer(dot)Pruy(at)Acrys(dot)COM>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: pessimal trivial-update performance
Date: 2010-07-04 07:40:06
Message-ID: 4C303AD6.5090405@acrys.com (view raw or flat)
Thread:
Lists: pgsql-hackers

Am 04.07.2010 06:11, wrote Tom Lane:
> ... but is it representative of real-world cases?
>
> 			regards, tom lane
>

Hi Tom,
we do run an application in productive use that suffered from a similar effect.
We did not have 1000000 updates per row, but 10-100 updates per row on about 1-10 million rows of a table.
In the end we managed to increase performance by factor of more than two
by adding support to the application to track updates internally and only "flush" changes to the database
at the (final) application commit.
This did cost a lot as now we needed to adjust queries on the table with data stored internally
(as not yet reflected in the database). This still is more efficient as updating and performing operation an the database
directly. (e.g. an update using the primary key of the table (about 50 million rows total) would have lasted over 3 seconds(!)
while initially the very same update  was done within far below 1ms).

So I think this could qualify as a real world example of that case.

Regards,
Rainer

-- 
Rainer Pruy
Managing Director

Acrys Consult GmbH & Co. KG
Theodor-Heuss-Str. 53-63, 61118 Bad Vilbel, Germany
Phone: +49-6101-98760-0  Fax: +49-6101-98760-50
Web: http://www.acrys.com -  Email: office(at)acrys(dot)com
Registered: Frankfurt am Main, HRA 31151
General partner: Acrys Verwaltungs GmbH
Theodor-Heuss-Str. 53-63, D-61118 Bad Vilbel
Registered: Frankfurt am Main, HRB 57625

In response to

pgsql-hackers by date

Next:From: Florian PflugDate: 2010-07-04 07:59:40
Subject: Re: proof concept: do statement parametrization
Previous:From: Pavel StehuleDate: 2010-07-04 06:41:35
Subject: proof concept: do statement parametrization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group