Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
