High level Postgres run down of INSERT/UPDATE speed?

Question

I know that when I UPDATE a row in Pg, that row gets rewritten and the old row gets deactivated when the new row gets activated. I know this is due to how the MVCC layer is implemented.

What the advantages then of UPDATE over DELETE ... INSERT? Is there anything else to be said about the relationship of UPDATE and DELETE in Postgresql? I can't find any docs that speak of this or mailing list postings.. Obviously, they run different user triggers if any but what happens under the hood to give them different performance profiles?

Answer 1

A DELETE will trigger foreign key constraints, you could violate them. Other triggers might be a problem as well.

If you change the FILLFACTOR, you also give the database the option to do a HOT update. That's an update inside the same memory block as where the original record is located. HOT updates can be much faster and generate a lot less overhead (vacuum) as a normal update.