[HACKERS] Is this a better MVCC.
I just had an interesting idea. It sounds too easy to beleve, but hear me out and correct me if I'm wrong. Currently, during update, PostgreSQL takes the existing record, modifyies it, and adds it as a new row. The previous record has a pointer to the new version. If the row is updated twice, the original row is hit first, followed by the next version, then the last version. Do I understand this correctly? Now, what if we did it another way, copy the old version of the row into the new row and update the tuple in place? (Space permitting, of course.) That way, performance does not degrade over time, also Vacuum should be easier and less combersome because it simply lops off the end of the list, and mark tuples which are not in any transaction path. Is this a lot of work, is it inherently wrong? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is this a better MVCC.
mlw [EMAIL PROTECTED] writes: Now, what if we did it another way, copy the old version of the row into the new row and update the tuple in place? I don't think we can get away with moving the extant tuple. If we did, a concurrent scan that should have found the old tuple might miss it. (This is why VACUUM FULL needs exclusive lock to move tuples.) It's fairly unclear whether this would actually buy any performance gain, anyway. In the case of a seqscan I don't see that it makes any difference on average, and in the case of an indexscan what matters is the index ordering not the physical location. (In this connection, btree indexes already do the right thing, cf comments for _bt_insertonpg.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Is this a better MVCC.
On 7.1.x it definitely gets slower even for indexscans. e.g. 60 updates/sec dropping to 30 then to 20 over time. Is this fixed for 7.2? If not, is it possible to make the pointer point to the latest row instead of the most obsolete one, and having the newer rows point to the older ones, instead of the other way round (which seems to be happening with 7.1)? I suppose this could make updates slower - have to update indexes? But selects would be faster (other than cases where there are a lot of uncommitted updates outstanding). If that is not possible (or updating the index too painful), how about having the first pointer point to first row which then points to latest row, which then points to subsequent older rows. That way the miss penalty is reduced. It seems reasonable to me that the newer rows should be more visible- unless more people update rows and then rollback rather than update and then commit. I'm missing something out right? :) Regards, Link. At 09:15 AM 4/16/02 -0400, Tom Lane wrote: mlw [EMAIL PROTECTED] writes: Now, what if we did it another way, copy the old version of the row into the new row and update the tuple in place? I don't think we can get away with moving the extant tuple. If we did, a concurrent scan that should have found the old tuple might miss it. (This is why VACUUM FULL needs exclusive lock to move tuples.) It's fairly unclear whether this would actually buy any performance gain, anyway. In the case of a seqscan I don't see that it makes any difference on average, and in the case of an indexscan what matters is the index ordering not the physical location. (In this connection, btree indexes already do the right thing, cf comments for _bt_insertonpg.) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
