AW: Re: scaling multiple connections

> If you are familiar with cddb (actually freedb.org) I am taking that data in
> putting it into postgres. The steps are: (pseudo code)
>
> select nextval('cdid_seq');
>
> begin;
>
> insert into titles (...) values (...);
>
> for(i=0; i < tracks; i++)
> insert into tracks (...) values (...);
>
> commit;
>
>
> When running stand alone on my machine, it will hovers around 130 full CDs per
> second. When I start two processes it drops to fewer than 100 inserts per
> second. When I add another, it drops even more. The results I posted with
> pgbench pretty much showed what I was seeing in my program.

The above is a typical example of an application that will lose performance
when perfomed in parallel as long as the bottleneck is the db. The only way to make
above behave better when done in parallel is a "fragmented" tracks table.
The chance that two concurrent clients insert into the same table file needs to be
lowered, since above suffers from lock contention. Remember that for the non blocking
lock PostgreSQL currently uses the fastest possible approach optimized in assembler.

A valid design in PostgreSQL would involve n tracks tables tracks_1 .. tracks_n
a union all view "tracks" and some on insert and on update rules. Unfortunalely there
is currently no way to optimize the select with a select rule, that is based on the given where
clause. Nor would the optimizer regard any applicable check constraints for the union all
query. Thus if you don't have separate disks for the tracks_n's you will loose performance
on select.

When not doing the above, your best chance is to tweak the single inserter case,
since that will be fastest.

Andreas