Re: Updating large tables without dead tuples

Greetings,

* ldh@laurent-hasson.com (ldh@laurent-hasson.com) wrote:
> This was done during a maintenance window, and that table is read-only except when we ETL data to it on a weekly
basis,and so I was just wondering why I should pay the "bloat" penalty for this type of transaction. Is there a trick
thatcould be use here?
 

Yes, create a new table and INSERT the data into that table, then swap
the new table into place as the old table.  Another option, if you don't
mind the exclusive lock taken on the table, is to dump the data to
another table, then TRUNCATE the current one and then INSERT into it.

There's other options too, involving triggers and such to allow updates
and other changes to be captured during this process, avoiding the need
to lock the table, but that gets a bit complicated.

> More generally, I suspect that the MVCC architecture is so deep that something like LOCK TABLE, which would guarantee
thatthere won't be contentions, couldn't be used as a heuristic to not create dead tuples? That would make quite a
performanceimprovement for this type of work though.
 

I'm afraid it wouldn't be quite that simple, particularly you have to
think about what happens when you issue a rollback...

Thanks!

Stephen