Re: Foreign keys in pgbench

On Sun, May 13, 2012 at 3:03 PM, Peter Geoghegan <peter@2ndquadrant.com> wrote:
> On 13 May 2012 18:07, Jeff Janes <jeff.janes@gmail.com> wrote:
>> I think that pgbench should it make it easy to assess the impact of
>> foreign key constraints.
>
> I agree in principle.  I favour being more inclusive about pgbench
> options, even if the need for such options is only marginal, which
> this isn't - I personally would have found it very useful recently.
> pgbench is an expert-level tool, and I find arguments against adding
> more options along the lines of "that will distract beginner users"
> completely unconvincing.

If it is a common position that people should probably be making
better (to say, more) use of foreign key constraints -- something I
agree with, although my colleagues have identified non-performance
usability gaps that have to do with unit testing, resetting tables,
deferred constraints, and cascading deletes -- it's probably a good
idea to do our best to ensure that using them does not regress
performance badly, at least.

I might give a different answer if FK constraints had better
penetration in applications and they were viewed as "just the cost of
doing business", but that is not the case.

All in all, though, I think the usability problems trump performance,
and what's interesting is those usability problems are only seen in
development, and not production.  I mention this information because
it may help you qualify my level of support for this idea.

The goal would be for foreign keys to become usable enough that a
framework like ActiveRecord might just use them by default.  The
recent inclusion of much more powerful query compilers, default(!) use
of named prepared statements (perhaps even prematurely, given the
problem with generic selectivity estimates), and hstore suggests that
this time might yet come. Caveat being that I haven't researched any
specific objections from ActiveRecord people yet.

--
fdr