Re: Improving the Performance of Full Table Updates

The obvious advantages are
a) Avoidance of one read lock per page
b) One Big write lock instead of multiple write locks.

But as you said, i will do some initial profiling and get back.

Thanks,
Gokul.

On 9/20/07, Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
>
> Gokulakannan Somsundaram wrote:
> > 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.
>
> I doubt the locking overhead is that significant. Have you done any
> profiling to show that it's worth it?
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>