Predict partition number for Postgres hash partitioning

Question

I'm writting an app which uses partitions in Postgres DB. This is will be send to customers and run on their server. This implies that I have to be prepared for many different scenarios. Lets start with simple table schema:

CREATE TABLE dir (
   id SERIAL,
   volume_id BIGINT,
   path TEXT
);

I want to partition that table by volume_id column.

What I would like to achieve:

• limited number of partitions (right now it's 500 but I'm will be tweaking this parameter later)
• Do not create all partitions at once - add them only when they are needed
• support volume ids up to 100K
• [NICE TO HAVE] - been able for human to calculate partition number from volume_id

Solution that I have right now:

• partition by LIST
• each partition handles volume_id % 500 like this: CREATE TABLE dir_part_1 PARTITION OF dir FOR VALUES IN (1, 501, 1001, 1501, ..., 9501);

This works great because I can create partition when it's needed, and I know exactly to which partition given volume_id belongs. But I have to manually declare numbers and I cannot support high volume_ids because speed of insert statements decrease drastically (more than 2 times).

It looks like I could try HASH partitioning but my biggest concern is that I have to create all partitions at the very beginning and I would like to be able to create them dynamically when they are needed, because planning time increases significantly up to 5 seconds for 500 partitions. For example I know that I will be adding rows with volume_id=5. How can I tell which partition should I create?

Answer 1

I was able to force Postgres to use dummy hash function by adding hash operator for partitioned table.

CREATE OR REPLACE FUNCTION partition_custom_bigint_hash(value BIGINT, seed BIGINT)
RETURNS BIGINT AS $$
    -- this number is UINT64CONST(0x49a0f4dd15e5a8e3) from
    -- https://github.com/postgres/postgres/blob/REL_13_STABLE/src/include/common/hashfn.h#L83
    SELECT value - 5305509591434766563;
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;


CREATE OPERATOR CLASS partition_custom_bigint_hash_op
    FOR TYPE int8
    USING hash AS
    OPERATOR 1 =,
    FUNCTION 2 partition_custom_bigint_hash(BIGINT, BIGINT);

Now you can declare partitioned table like this:

CREATE TABLE some_table (
   id SERIAL,
   partition_id BIGINT,
   value TEXT
) PARTITION BY HASH (partition_id);

CREATE TABLE  some_table_part_2 PARTITION OF some_table FOR VALUES WITH (modulus 3, remainder 2);

Now you can safely assume that allow rows with partition_id % 3 = 2 will land in some_table_part_2 partition. So if you are sure what values you will receive in partition_id column you can create only required partitions.

DISCLAIMER 1: Unfortunately this will not work correctly right now (Postgres 13.1) because of bug #16840

DISCLAIMER 2: There is not point of using this technic unless you are planning to create large number of partitions (I would say 50 or more) and prolonged planning time is an issue.