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

Improving the Performance of Full Table Updates

From: "Gokulakannan Somsundaram" <gokul007(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Improving the Performance of Full Table Updates
Date: 2007-09-20 07:11:20
Message-ID: 9362e74e0709200011j7b155bb9p74912a450c5b2f84@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,
   The Current architecture of Updates in PostgreSQL is
1) Make a select query out of update. It involves a READ lock/BUFFER_SHARE
2) Get the tupleid
3) Goto the buffer containing the tupleid, make a BUFFER_EXCLUSIVE lock on
it
4) update it
5) Repeat the above process for subsequent rows

I propose to change this row-by-row approach, when it is a full table
update. I plan to send a extra flag(which will be set for Full table
Deletes/Updates). this would make the access method directly acquire the
exclusive lock and update the existing record.

For Deletes this is simple. But for updates, the projection tuple has to be
made before re-inserting it. So there will be a list of Heap tuples stored
in memory for each page getting updated. these tuples will be inserted after
the deletion part of update is done. This is just a rough design. I may get
involved in a detail design once i get a nod from the mailing list
community.

Thanks,
Gokul.

Responses

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2007-09-20 07:20:06
Subject: Re: Improving the Performance of Full Table Updates
Previous:From: Andrew DunstanDate: 2007-09-20 00:14:56
Subject: Re: like/ilike improvements

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