On Fri, Mar 3, 2017 at 5:00 PM, Greg Stark <stark@mit.edu> wrote:
On 2 March 2017 at 13:03, amul sul <sulamul@gmail.com> wrote: > create table foo (a integer, b text) partition by hash (a); > create table foo1 partition of foo with (modulus 4, remainder 0); > create table foo2 partition of foo with (modulus 8, remainder 1); -- legal, > modulus doesn't need to match > create table foo3 partition of foo with (modulus 8, remainder 4); -- > illegal, overlaps foo1
Instead of using modulus, why not just divide up the range of hash keys using ranges?
That should be just as good for a good hash
function (effectively using the high bits instead of the low bits of the hash value). And it would mean you could reuse the machinery for list partitioning for partition exclusion.
It also has the advantage that it's easier to see how to add more partitions. You just split all the ranges and (and migrate the data...). There's even the possibility of having uneven partitions if you have a data distribution skew -- which can happen even if you have a good hash function. In a degenerate case you could have a partition for a single hash of a particularly common value then a reasonable number of partitions for the remaining hash ranges.
Initially
we
had
to have
somewhat similar thought to make a range of hash
values for
each partition, using the same half-open interval syntax we use in general:
create table foo (a integer, b text) partition by hash (a);
create table foo1 partition of foo for values from (0) to (1073741824);
create table foo2 partition of foo for values from (1073741824) to (-2147483648);
create table foo3 partition of foo for values from (-2147483648) to (-1073741824);
create table foo4 partition of foo for values from (-1073741824) to (0);
That's really nice for the system, but not so much for the users. The system can
now generate each partition constraint correctly immediately upon seeing the SQL
statement for the corresponding table, which is very desirable. However, users are
not likely to know that the magic numbers to distribute keys equally across four
partitions are 1073741824, -2147483648, and -1073741824.