Re: no default hash partition

On Wed, Aug 7, 2019 at 5:26 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Aug 6, 2019 at 6:58 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > 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 think it would only be sensible as a temporary state. The system
> allows more than one modulus so that you can do partition split
> incrementally. For example if you have 8 partitions all with modulus
> 8 and with remainders 0..7, you could:
>
> - detach the partition with (modulus 8, remainder 0)
> - attach two new partitions with (modulus 16, remainder 0) and
> (modulus 16, remainder 8)
> - move the data from the old partition to the new ones
>
> Then you'd have 9 partitions, and you'd only have taken the amount of
> downtime needed to repartition 1/8th of your data. You could then
> repeat this process one partition at a time during additional
> maintenance windows, and end up with 16 partitions in the end.
> Without the ability to have more than one modulus, or if you had
> chosen not to double the modulus but to change it to some other value
> like 13, you would've needed to repartition all the data at once,
> which would have required one much longer outage. You can argue about
> whether the ability to do this kind of thing is useful, but it seemed
> to me that it was.
>
> I think, as Amit says, that having an automatic partition creation
> feature for hash partitions (and maybe other kinds, but certainly for
> hash) would be a useful thing to add to the system. I also think that
> it might be useful to add some commands to automate partition
> splitting (and maybe combining) although I think there's some design
> work to be done there to figure out exactly what we should build. I
> don't think it's ever useful to have a hash-partitioned table with an
> incomplete set of partitions long term, but it makes things simpler to
> allow that temporarily, for example during dump restoration.
> Therefore, I see no reason why we would want to go to the trouble of
> allowing hash-partitioned tables to have default partitions; it would
> just encourage people to do things that don't really make any sense.
>

Another usecase for not having all partitions temporarily is if some of
them should be different enough that you don't want them auto-created. A
common one would be that they should be on different tablespaces, but that
can of course be solved by moving the partition after it had been
auto-created (and should be fast since at this point it would be empty).
But imagine you wanted one partition to be a FOREIGN one for example, you
can't ALTER a partition to become foreign, you'd have to drop it and
recreate it, in which case not having created it in the first place
would've been better. That's pretty weird for hash partitioning, but one
could certainly imagine having *all* partitions of a hash partitioned table
be FOREIGN...

None of that is solved by having a default partition for it though, since
it would only be a temporary state. It only goes to that if we do want to
auto-create the hash partitions (which I think would be really useful for
the most common usecase), we should have a way not to do it. Either by only
autocreating them if a specific keyword is given, or by having a keyword
that would prevent it.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>