4

How to handle new partitions in a smart automatic manner in PostgreSQL ?

I want to create a PostgreSQL table List partition on a certain column. For example:

CREATE TABLE orders
(
  id            int ,
  country_code  VARCHAR,
  order_total   int,
  CONSTRAINT orders_pk PRIMARY KEY (id)
) PARTITION BY LIST (country_code)

however I dont know all the possible counties in advance, on an oracle table I will enable auto partitioning:

ALTER TABLE orders SET PARTITIONING AUTOMATIC;

It seems like PostgreSQL lacks this feature, I have read that I can add a default partition to the table and then later on add another partition and the values from the default partition would be transferred automatically to the new partition. (EDIT: default partition block you from creating new partitions if you previously inserted values to it)

what would be the best strategy to handle this in an automatic manner ? Are there any plans to add this feature to PostgreSQL?

1
  • I have created an external script as adding a trigger before/update on the default partition would slow down the insertion. So the script runs every month as I have partitioned for each month and then it checks the records which are there in default partition.I admit there are many steps to ensure. I wish it would have been easier but it works – Freedom911 May 10 at 13:24
0

I haven't heard of any plans to add such a feature.

You can easily create a BEFORE INSERT trigger that creates partitions on demand, but of course that trigger has to run for each row inserted, which means a certain performance impact.

Using a default partition like you suggest is a solution with better performance, but it requires later explicitly moving the data to a different partition, which can be painful.

If you expect new countries to pop up all the time, maybe the trigger is the way to go. If it happens once in a blue moon, I might prefer the second way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.