Re: two memory-consuming postgres processes

On Fri, May 2, 2008 at 4:51 PM, Alexy Khrabrov <deliverable(at)gmail(dot)com> wrote:
>
> On May 2, 2008, at 1:40 PM, Scott Marlowe wrote:
>
> > Again, a database protects your data from getting scrambled should the
> > program updating it quit halfway through etc...
> >
>
> Right -- but this is a data mining work, I add a derived column to a row,
> and it's computed from that very row and a small second table which should
> fit in RAM.

Full table update of a single field is one of the worst possible
operations with PostgreSQL. mysql is better at this because lack of
proper transactions and full table locking allow the rows to be
(mostly) updated in place. Ideally, you should be leveraging the
power of PostgreSQL so that you can avoid the full table update if
possible. Maybe if you step back and think about the problem you may
be able to come up with a solution that is more efficient.

Also, if you must do it this way, (as others suggest), do CREATE TABLE
new_table AS SELECT...., then create keys, and drop the old table when
done. This is much faster than update.

merlin