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

Faster Updates

From: PFC <lists(at)peufeu(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Faster Updates
Date: 2006-06-03 15:12:14
Message-ID: op.taksiop4cigqcu@apollo13 (view raw or flat)
Thread:
Lists: pgsql-hackers
	Hello,
	Sometimes people complain that UPDATE is slow in postgres. UPDATE...

	- generates dead tuples which must be vacuumed.
	- needs to hit all indexes even if only one column was modified.

	From what I know UPDATE creates a new copy of the old row with the  
relevant C/TID's, then indexes it. On COMMIT the old version becomes dead  
but stays in the table and indexes until VACUUM.
	I propose a simple idea, which may be idiotic, but who knows.

	When a row is UPDATED, instead of storing a new copy of the entire row,  
only a differential is stored. The old row stays in the page anyway, so we  
might as well only store the binary encoded equivalent of "Use the row  
version number X and change column A to value Y".
	This is possible only if the differential fits in the free space on the  
page.
	In this case, a lot less dead space is generated. VACUUM would  
consolidate the differentials for commited transactions into a new base  
value for this row.
	While reading the page looking for a specific version of a row, all  
differences would need to be consolidated. This adds overhead, but it  
might be a win.
	With this method, it could be possible to avoid updating the indexes for  
unmodified columns. This is a big win.

	What do you think ?


	

Responses

pgsql-hackers by date

Next:From: Tino WildenhainDate: 2006-06-03 15:26:55
Subject: Re: COPY (query) TO file
Previous:From: PFCDate: 2006-06-03 14:59:29
Subject: Re: COPY (query) TO file

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