From the documentation, I see that we can define a custom space partitioning function using partitioning_func
. However, I don't find any hands-on example on how I can define the partitioning function and what should it return.
What I read is, by default it uses an INT32_MAX (key range in the hash table), for example, if we give number_partitions=2
then numeric values < INT32_MAX/2 will fall into the first partition and remaining values will fall into the second partition.
In my case, let's say I have very few different values in the space partitioning column, then based on the default hashing function it is evident that all the values fall into the first partition. However, I want each different value to be in a different partition.
I have come up with one solution, if I have 2 different values in the space partitioning column, I want to put each value in a separate partition (that means I have 2 partitions).
As I mentioned in my questions timescale seems to use INT32_MAX
in c language. Where INT32_MAX
value is 2147483647
so I am dividing this by two to direct timescale to use 2 different partitions based on my space partition value (1 or 2).
The below code worked for me. I am using Postgres 12 with TimeScale 2.3.1
CREATE OR REPLACE FUNCTION two_partition_fun(i anyelement) RETURNS integer AS \$\$
BEGIN
RETURN 1073741821 + i;
END;
\$\$ LANGUAGE plpgsql IMMUTABLE;
I am not confident if this is a good approach, let me know if you got better approaches.