PostgreSQL Partitioning – How to Automatically Partition Table List

partitioningpostgresql

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?

Best Answer

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.

Related Question