Re: Update table performance

On Aug 8, 2007, at 3:00 AM, Heikki Linnakangas wrote:

> 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.

This is true.  In fact, the chapter on Routine Database Maintenance
tasks that discusses vacuuming explains all of this.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com