Faster Updates - Mailing list pgsql-hackers

From PFC
Subject Faster Updates
Date
Msg-id op.taksiop4cigqcu@apollo13
Whole thread Raw
Responses Re: Faster Updates
List 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 ?




pgsql-hackers by date:

Previous
From: PFC
Date:
Subject: Re: COPY (query) TO file
Next
From: Tino Wildenhain
Date:
Subject: Re: COPY (query) TO file