Re: concurrency performance degradation

On Fri, 26 Oct 2001, Sheer El-Showk wrote:

> Hi,
>
> I have an application that uses foreign keys rather heavily in postgres.
> It supports multiple concurrent sessions with high transaction volumes.
> Ideally the server should be able to have 20-30 connections open to the
> database each partaking in similar operations. Each transaction may
> involve 20+ writes and maybe 100 reads.
>
> When run single threaded, I can perform on transaction per second (30 or
> so writes, 100 or so reads, plus time spent int he application), but if I
> increase concurrency to about 10 active threads all doing similar
> operations (on the same set of tables) I gain very little from it. I
> notice in the process listing that the postges processes are almost all in
> "INSERT WAITING..." or "UPDATE WAITING..." except for one which is in a
> "SELECT" of an "INSERT". My confusion here is that I thought postgres
> used record-level locking so I should be able run multipel concurrent
> inserts on a table without locking.
>
> The reason I mention foreign keys above is that I imagine that foreign
> keys might complicate locking issues and I use them a _lot_. I also

If you're doing alot of inserts or updates to rows that match the same row
in the primary key table this may be explained by the foreign key locking
as I believe a row lock is grabbed on the primary key row to prevent
it from being removed by another transaction.