pg_partman

pg_partman copied to clipboard

Hello @keithf4 !

We've been using pg_partman for several years now, using trigger-based partitions and we are starting a migration to native partitioning. It's pretty awesome how this project evolved since 1.X and all the new features, great work :)

We have one table, that we are going to partition by range (timestamp) and then we subpartition by the HASH(id) and a modulus. The DDL looks something like this:

CREATE TABLE range_with_hash_id_table (
  id bigint NOT NULL,
  timestamp timestamptz NOT NULL DEFAULT now(),
  constraint range_with_hash_id_table_p_pkey PRIMARY KEY (id, timestamp)
)
PARTITION BY RANGE (timestamp);

CREATE TABLE range_with_hash_id_table2021_02 PARTITION OF range_with_hash_id_table FOR VALUES FROM ('2021-02-01') TO ('2021-03-01') PARTITION BY HASH (id);
CREATE TABLE range_with_hash_id_table2021_02_0 PARTITION OF range_with_hash_id_table2021_02 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE range_with_hash_id_table2021_02_1 PARTITION OF range_with_hash_id_table2021_02 FOR VALUES WITH (MODULUS 2, REMAINDER 1);

CREATE TABLE range_with_hash_id_table2021_03 PARTITION OF range_with_hash_id_table FOR VALUES FROM ('2021-03-01') TO ('2021-04-01') PARTITION BY HASH (id);
CREATE TABLE range_with_hash_id_table2021_03_0 PARTITION OF range_with_hash_id_table2021_03 FOR VALUES WITH (MODULUS 2, REMAINDER 0);
CREATE TABLE range_with_hash_id_table2021_03_1 PARTITION OF range_with_hash_id_table2021_03 FOR VALUES WITH (MODULUS 2, REMAINDER 1);

I was wondering if this is something that pg_partman would be able to support. Looking for your feedback!

Thanks again!

Thanks for the long time support and usage!

I had not planned on adding hash partitioning support to pg_partman. Mostly because the decision on the hash size has to be made up front and can't be changed after that, so there was nothing special needed outside of native support.

I can see how support for it would be needed in a sub-partitioning case like you gave though. However, I don't know when I'd be able to work on something like this. It would basically mean building in hash partitioning as a whole, which could then be applied to sub-partition parents. If you or anyone else would like to submit a patch for review to add hash partitioning, I'd be glad to review it.

Thanks for the quick response!

If you can point me in the right direction I can try giving it a shot :)

Thanks again for this awesome project and your support!

Would need a new create_partition_hash() function and incorporate that into the create_parent() function. Hash partitioning would not need any ongoing maintenance, so likely wouldn't need much additional code in run_maintenance() but it would need to be accounted for in subpartitioning as you requested here. Would also have to require that the automatic_maintenance column in part_config be set to false.