Re: no default hash partition - Mailing list pgsql-hackers
| From | Magnus Hagander |
|---|---|
| Subject | Re: no default hash partition |
| Date | |
| Msg-id | CABUevExS0G-_JLqH+dcGXE3T_dKGE72k5-zT41yizsmH_jX=Hw@mail.gmail.com Whole thread Raw |
| In response to | Re: no default hash partition (Robert Haas <robertmhaas@gmail.com>) |
| List | pgsql-hackers |
On Wed, Aug 7, 2019 at 5:26 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Tue, Aug 6, 2019 at 6:58 PM Tom Lane <tgl@sss.pgh.pa.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.
pgsql-hackers by date: