Re: foreign key locks, 2nd attempt

On Mon, Mar 12, 2012 at 6:14 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Considering that nobody's done any work to resolve the uncertainty
> about whether the worst-case performance characteristics of this patch
> are acceptable, and considering further that it was undergoing massive
> code churn for more than a month after the final CommitFest, I think
> it's not that unreasonable to think it might not be ready for prime
> time at this point.

Thank you for cutting to the chase.

The "uncertainty" of which you speak is a theoretical point you
raised. It has been explained, but nobody has yet shown the
performance numbers to illustrate the point but only because they
seemed so clear. I would point out that you haven't demonstrated the
existence of a problem either, so redesigning something without any
proof of a problem seems a strange.

Let me explain again what this patch does and why it has such major
performance benefit.

This feature give us a step change in lock reductions from FKs. A real
world "best case" might be to examine the benefit this patch has on a
large batch load that inserts many new orders for existing customers.
In my example case the orders table has a FK to the customer table. At
the same time as the data load, we attempt to update a customer's
additional details, address or current balance etc. The large load
takes locks on the customer table and keeps them for the whole
transaction. So the customer updates are locked out for multiple
seconds, minutes or maybe hours, depending upon how far you want to
stretch the example. With this patch the customer updates don't cause
lock conflicts but they require mxact lookups in *some* cases, so they
might take 1-10ms extra, rather than 1-10 minutes more. >1000x faster.
The only case that causes the additional lookups is the case that
otherwise would have been locked. So producing "best case" results is
trivial and can be as enormous as you like.

I agree with you that some worst case performance tests should be
done. Could you please say what you think the worst cases would be, so
those can be tested? That would avoid wasting time or getting anything
backwards.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services