On Thu, Jun 17, 2010 at 2:14 PM, Marvin S. Addison <serac@vt.edu> wrote:
> My impression at present is that the fundamental locking behavior of
> PostgreSQL is poorly suited to applications that have both great deals of
> concurrency and referential integrity. This is of concern to us as we are
> considering migrating to PostgreSQL from Oracle for other applications. I
> realize that application design changes could possibly mitigate this
> problem, but I would argue that this case is so common that it ought to be
> handled better.
>
I see a performance problems from the FK locks but rarely do we get
deadlocks. Are you running in SERIALIZABLE mode?
Our issues come mostly from updating status rows related to the parent
record. I ended up having to send all of those requests through a
work queue and having a single process apply them, but now I have the
bottleneck of that single thread applying a *lot* of updates.
I'm wating on Greg Smith to finish up his book so he can dive into
solving this FK locking problem for me once and for all.