Search code examples
databasepostgresqltime-seriestimescaledb

How to use 'partitioning_func' in TimeScale DB


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.


Solution

  • 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.