Re: Transactions vs speed.

* mlw <markw(at)mohawksoft(dot)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(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."