Support space partitions per distinct value

[mbande]

I have a table with an integer field that takes limited values 1,2,3. and I want to space partition by this field, one partition for each value, i.e. to create 3 partitions and use value as exact partition id. searching the documentation, I cant figure out an easy solution for this.

[LeeHampton]

You can specify a custom partitioning function (see explanation for the partitioning_func argument here). With that you can control which partition a given element gets sent to. You can't directly control the exact partitioning id, however.

[mbande]

So, to enforce 1 partition per field value, i should initialize hypertable with 3 space partition and use an identity (f(x)->x) partitioning function, right?

[erimatnor]

@mbande the function can't be the identity function. The purpose of the partitioning function is to map a value onto a int32 (positive) key space. That key space is then divided evenly across the number of partitions. So, for three partitions and an input value of 1, the partitioning function needs to output a positive int32 between 0 and INT32_MAX/3. For 2 and 3 it would be analogous but falling instead into the two remaining ranges.

[bartpeeters]

We have the exact same usecase, any plans to add this as a new feature? More generic would be a possibility to dynamically add new partitions, just how a hypertable makes new partitions based on time ranges, but in this case based on unique ids.

[adrianog]

Where can I find more info about partitioning_func? An example that goes through defining the function and passing it to create_hypertable?

I only see this entry in the documentation: SELECT create_hypertable('conditions', 'time', 'location', 4, partitioning_func => 'location_hash');

[erimatnor]

Related to #563

[muntdan]

Any update on this please?

[muntdan]

I belive this might work: If you want let's say 100 Tenant/partition and use smallint to count them from 0 to 99. Because 2147483647/100 = 21474836, the function would look like this:

CREATE OR REPLACE FUNCTION SinglePartition(tenant anyelement) RETURNS 
integer AS 
$$
BEGIN
 RETURN tenant::smallint * 21474836 + 1;
END;
$$ 
LANGUAGE plpgsql IMMUTABLE;