Re: no default hash partition - Mailing list pgsql-hackers

Hi,

On Wed, Aug 7, 2019 at 8:02 AM Stephen Frost <sfrost@snowman.net> wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > > On 2019-Aug-06, Tom Lane wrote:
> > >> Seems like "it's likely to cause trouble for users" is just going to
> > >> beg the question "why?".  Can we explain the hazard succinctly?
> > >> Or point to a comment somewhere else that explains it?
> >
> > > Right ... the "trouble" is just that if the user later wants to add the
> > > missing partitions, they'll need to acquire some strong lock (IIRC it's AEL)
> > > in the partitioned table, so it effectively means an outage.  With
> > > list/range partitioning, there's the slight advantage that you don't
> > > have to guess all your partitions in advance, or cover data values that
> > > are required for a very small number of rows.  In hash partitioning you
> > > can't really predict which values are those going to be, and the set of
> > > missing partitions is perfectly known.
> >
> > Hmm.  So given the point about it being hard to predict which hash
> > partitions would receive what values ... under what circumstances
> > would it be sensible to not create a full set of partitions?  Should
> > we just enforce that there is a full set, somehow?
>
> I imagine there's good reasons this wasn't just done (for this or
> various other things), but couldn't we enforce it by just creating them
> all..?  Sure would simplify a lot of things for users.  Similairly for
> list partitions, I would think.  Again, I feel like there's probably a
> reason why it doesn't just work(tm) like that, but it sure would be
> nice.

Maybe the reason that we don't create all partitions automatically is
that hash-partitioning developers thought that such a feature could be
built later [1].  Maybe you know, but I think it's just that we
implemented the syntax needed to get things like pg_dump/upgrade
working sanely, that is, a command to define each partition
separately, and... stopped there.  There're no other intrinsic reasons
that I know of for this implementation order.  pg_partman helps with
the automation, with features that users want in most or all cases --
define all needed partitions for a given modulus, define time series
partitions for a given window, etc.  Maybe not everyone likes to rely
on an external tool, so the core at some point will have features to
perform some if not all of the tasks that pg_partman does, with the
added benefit that the new feature might allow the core to optimize
partitioning better.

Btw, there was even a discussion started recently to discuss the
user-level feature:

Subject: Creating partitions automatically at least on HASH?
https://www.postgresql.org/message-id/alpine.DEB.2.21.1907150711080.22273%40lancre

Thanks,
Amit

[1] https://www.postgresql.org/message-id/CA%2BTgmobGH4zK27y42gGbtvfWFPnATHcocMZ%3DHkJF51KLkKY_xw%40mail.gmail.com