I've just been asked to clarify what actually happens when a DELETE takes
place, and what happens to TOASTed data. The MVCC never-update-in-place
phrase caused some debate over what happens. I couldn't find a specific and
conclusive comment on this that I trust to be completely up to date. One may
exist however....?
My answer was this, though this was not thought accurate (on the DELETE
aspect):
For DELETEs, the xmax field on the tuple/row header is updated-in-place to
show the xid that deleted the row. Thus, DELETEs of long data rows are just
as efficient as DELETEs of shorter data rows, since both effect only a
single data page. This still allows concurrent access because only one
backend may hold the page lock at any time, so nobody is actively reading
the row at the time of the setting of xmax for the DELETE.
MVCC uses a never-update-in-place algorithm to allow concurrency during
UPDATEs. In general, the whole row is re-written, just as if the data had
been DELETEd and then re-INSERTed. If there are TOASTed fields, then the
TOASTed data is only re-written if it is has changed as part of the UPDATE.
Thus an UPDATE of a row with TOASTed data, yet that doesn't alter the
TOASTed data itself, is no more expensive than an UPDATE of a shorter row,
since it will change only one page (with an equal risk of requiring a new
block write as a result of the insertion of the new main row version).
DELETE does NOT take a full copy of the row and THEN mark the xmax field as
the xid of the deleting transaction, so is NOT similar to an UPDATE action
on the same row.
I've checked heapam.c and tuptoaster.c, and think this is correct.
Any differing views? If possible, please refer me to the code - I'm still
working my way around the heap access code, so feel free to show me the
light...
Best regards, Simon Riggs
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
