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?