Re: Transactions vs speed.

* mlw <markw@mohawksoft.com> [010113 17:19] wrote:
> I have a question about Postgres:
> 
> Take this update:
>     update table set field = 'X' ;
> 
> 
> This is a very expensive function when the table has millions of rows,
> it takes over an hour. If I dump the database, and process the data with
> perl, then reload the data, it takes minutes. Most of the time is used
> creating indexes.
> 
> I am not asking for a feature, I am just musing. 

Well you really haven't said if you've tuned your database at all, the
way postgresql ships by default it doesn't use a very large shared memory
segment, also all the writing (at least in 7.0.x) is done syncronously.

There's a boatload of email out there that explains various ways to tune
the system.  Here's some of the flags that I use:

-B 32768   # uses over 300megs of shared memory
-o "-F" # tells database not to call fsync on each update

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."