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 ?
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
