3

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?

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.

| improve this answer | |

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.