Re: UPDATE

Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
> AFAIK if you run the UPDATE while there are no other concurrent
> transactions, Pg will write the new values in-place. It still updates
> the WAL first, but it won't create whole new copies of each record as
> well. That's the case at least if the field you're updating isn't
> involved in any indexes; I think doing this relies on HOT, and won't
> work if the column being altered is involved in one or more indexes.

This is not correct; PG *never* overwrites an existing record (at least
not in any user-accessible code paths). The deal with HOT is that we
avoid making a fresh set of index entries if none of the indexed columns
changed; but that only happens if there is enough room on the same
database page for the new copy of the record. In that case the old copy
is chained to the new one and index searches have to follow the chain
link. (The same-page restriction is made primarily to ensure that
index lookups don't get too expensive by having to read in extra pages.)

Yes, this is a lot slower than what MyISAM does. As noted, there are
countervailing advantages.

regards, tom lane