Re: Seeking advice regarding a design problem

On 2 Aug 2002, Wei Weng wrote:

> I am running postgresql as database backend, and I have some scripts
> dealing with constant incoming data and then insert these data into the
> database, in a quite complex way, involving a couple of procedures.
>
> But the performance of the database is worse than I had thought. After
> about 100 times of the script being run, the speed of the insertion
> slowed down dramatically. But it went back to the regular fast speed
> after I did a vacuum analyze.
>
> how can I redesign the system to avoid the bottleneck? And why is it

Upgrade to 7.2 so that you can vacuum while other things are going on
and vacuum analyze after modifying a large portion of the database (note
that if the database is particularly large you'll probably need to change
the free space map configuration as well).

It's hard to tell what particularly you're running into, is it just a
case that you're accessing the dead tuples and that's slowing it down,
is it that you're changing the data in a fashion that changes how the
optimizer should be choosing to do queries, etc...