postgresql multicolumn partitioning

Question

Postgres 11

I want to make a table calendars, that is at the same time partitioned by range (one month of data) and a list of keys.

The reason is that:

1. I want to prune months that are not longer updated.
2. I need all the data to be partitioned by list of ids for further joins with and other partitioned table listings, that is partitined by the same list of keys.

The documentation says:

Declarative partitioning only supports range, list and hash partitioning, whereas table inheritance allows data to be divided in a manner of the user's choosing.

Thus, if I get it right, this means that my taks can not be done with Declarative partitioning, but probably can be done using Inheritance.

So, I try to reproduce the given example with my modification

CREATE TABLE measurement (
 city_id int not null,
 logdate date not null,
 peaktemp int,
 unitsales int
 ) PARTITION BY RANGE (logdate); # this already does not accept mixed types of partitioning

than I try to create a partition with my mixed rules of partitining:

CREATE TABLE measurement_y2006m02 (
 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' 
 AND paektamp >0 AND peaktemp <=10)
 ) INHERITS (measurement);

this gives:

cannot inherit from partitioned table "measurement"

Thank you!

• user1822: You shouldn't be using inheritance based partitioning anymore. Use declarative partitioning
• user1822: I think you are looking for sub-partitioning. First use range partitioning on date, then create sub-partitions by list of IDs
• Dmitriy Grankin: @a_horse_with_no_name, you mean it's possible to partition by range, followed by sub-partition by list?
• user1822: Yes, but if you want this to be efficient, you really should be using declarative partitioning (I am not sure if that would even be possible with the deprecated inheritance based partitioning)
• user1822: "To implement sub-partitioning, specify the PARTITION BY clause in the commands used to create individual partitions, for example:"

Answer 1

A partition can again be a partitioned table, so using subpartitions you can partition a table in two different ways:

CREATE TABLE calendar (
 id bigint NOT NULL,
 day date NOT NULL
) PARTITION BY RANGE (day);
CREATE TABLE calendar_2020 PARTITION OF calendar
 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01') PARTITION BY LIST (id);
CREATE TABLE calendar_2020_a
 PARTITION OF calendar_2020 FOR VALUES IN (1, 2, 42);

Make sure to test carefully if you get the desired performance benefits; you probably have to turn on enable_partitionwise_join.

Also make sure that you use a recent PostgreSQL version (≥ x12) and that you don't end up with thousands of partitions.

• Dmitriy Grankin: 1. I will be having thousands of partitions in my case, what is possibly bad about it? 2. I am using postgres 11, do I really have to move to 12? If so, how to do it in an easy way?
• Laurenz Albe: Thousands of partitions is pushing the limits. Query planning time will increase, for one. You will need to raise a number of parameters, like autovacuum_max_workers, max_files_per_process and max_locks_per_transaction. You will have to test if the performance is still acceptable. v12 has improvements in that area. Upgrade can be as simple as running pg_upgrade.
• Yannick Schuchmann: Does the order make any difference? e.g. having it first partitioned by LIST (id) and the by RANGE (day) ? @LaurenzAlbe
• Laurenz Albe: @YannickSchuchmann That depends on your query. Partition pruning will be different.