Re: Should I partition this table?

On Thu, 10 Jul 2014 13:16:05 -0700 Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> >
> > In general, yes, given the information you provided. A parition on
> > ParentID % $something should improve performance.
>
> PostgresSQL's constraint exclusion logic is not smart enough to turn a
> simple equality into a mod equality. Which means every select query would
> have to include "AND ParentID % something = (:1 % somthing)" in addition to
> the primary clause "ParentID=:1", in order to benefit from constraint
> exclusion. That would be very unnatural, annoying, and error prone. Range
> partitioning would be better, if any partitioning is needed at all.

I was remiss in pointing out the additional WHERE requirement -- I tend to
assume that people are already aware of that, but it's likely that not
everyone is. Thank you for pointing it out.

As far as your comments against adding them: I'm not going to speculate as
to what kind of queries people do or do not find annoying. The term
"unnatural" is an odd choice of words, and the only thing I can think to
respond with is "platypus." As far as error-prone is concerned, it's going
to have to be the OPs decision on whether the additional work is worth the
improvement. In the end, software isn't error-prone, programmers are error-
prone. If you have a good QA process in place, then you don't worry about
error-prone programmers, as the QA process catches their mistakes. If you
don't have such a process in place, or you don't trust it; then things get
harder and you make tradeoff decisions like, "I don't want to write complex
code, even if it's better, because we don't have the ability to ensure it's
error free." And I can't make those kinds of judgments because I don't know
what your environment is like.

There's no silver bullet. The OP doesn't seem to have any information about
what he's planning for: How big is the data predicted to get? What is
an acceptable level of performance? Has he even tested to see if the existing
layout will scale acceptably to the expected data volume? (perhaps nothing
needs to be changed at all) We don't know, so we can only speculate.

--
Bill Moran <wmoran(at)potentialtech(dot)com>