Re: no default hash partition

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.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company