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@xxxxxxxxxx
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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
