Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> The old row is ALWAYS visible to somebody, until you commit (if you ever
>> do). You can't simply overwrite existing data.
> Huh, I am not suggesting overwriting tuples you created, but tuples
> created by earlier transactions and now invisible to everyone.
Hm? If they're invisible to everyone, they're invisible to you too,
so you'd never select such a row as the basis for an update.
> I should be clearer. Suppose you have a table with a single index on
> the primary key. You are updating the row over and over again (a
> typical case). You create the first row, commit, then it is updated
> (two copies), commit, then you update it again. That first created row
> might not be visible to anyone, but has the same index value as the new
> row you are about to add. Why not reused that heap tuple?
You appear to be talking about searching the heap to see if there's a
row that is vacuumable but coincidentally has the same physical length
and all the same index values as the row you'd like to insert. I cannot
believe that the cost of doing that on every insert is a win compared to
vacuum. Point 1: the cost is being paid up front (in the foreground
inserting transaction) not in a background vacuum. Point 2: you cannot
just assume that such a row actually has the index entries you need ---
it might predate the creation of some indexes. You'd have to actually
probe each of the indexes to see if there's an entry pointing at the
row. Point 3: you can't do this if vacuum is currently running on the
table, as it might be in the midst of removing that same entry. Hence
such an insert requires locking out vacuum, which eliminates one of the
main reasons for having lazy vacuum in the first place. Point 4: you
also have conflicts against other inserts that might be trying to seize
on that same dead row. The locking needed to fix that is considerably
worse than the short-term lock needed to soak up some free space on a
page, because you'd have to hold it across the time needed to check all
the indexes per point 2.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
