Re: Avoid MVCC using exclusive lock possible?

On Tuesday 24 February 2004 22:13, Stephen wrote:
> Hi,
>
> Recently, I ran a huge update on an Integer column affecting 100 million
> rows in my database. What happened was my disk space increased in size and
> my IO load was very high. It appears that MVCC wants to rewrite each row
> (each row was about 5kB due to a bytea column). In addition, VACUUM needs
> to run to recover space eating up even more IO bandwidth.

I am sure people have answered the approach you have suggested so let me
suggest a workaround for your problem.

You could run following in a transaction.

- begin
- Create another table with exact same structure
- write a procedure that reads from input table and updates the value in
between
- drop the original table
- rename new table to old one
- commit
- analyze new table

Except for increased disk space, this approach has all the good things
postgresql offers. Especially using transactable DDLs it is huge benefit. You
certainly do save on vacuum.

If the entire table is updated then you can almost certainly get things done
faster this way.

HTH

Shridhar