Re: Update table performance

Erik Jones wrote:
> Decibel! wrote:
>> I should mention that if you can handle splitting the
>> update into multiple transactions, that will help a
>> lot since it means you won't be doubling the size of
>> the table.
>
> As I mentioned above, when you do an update you're actually inserting a
> new row and deleting the old one. That deleted row is still considered
> part of the table (for reasons of concurrency, read up on the
> concurrency chapter in the manual for the details) and once it is no
> longer visible by any live transactions can be re-used by future
> inserts. So, if you update one column on every row of a one million row
> table all at once, you have to allocate and write out one million new
> rows. But, if you do the update a quarter million at a time, the last
> three updates would be able to re-use many of the rows deleted in
> earlier updates.

Only if you vacuum between the updates.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com