1

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!

Improve this question
8
  • 1
    You shouldn't be using inheritance based partitioning anymore. Use declarative partitioning Commented Oct 22, 2020 at 8:58
  • 1
    I think you are looking for sub-partitioning. First use range partitioning on date, then create sub-partitions by list of IDs Commented Oct 22, 2020 at 8:59
  • @a_horse_with_no_name, you mean it's possible to partition by range, followed by sub-partition by list? Commented Oct 22, 2020 at 9:07
  • 1
    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) Commented Oct 22, 2020 at 9:12
  • 1
    "To implement sub-partitioning, specify the PARTITION BY clause in the commands used to create individual partitions, for example:" Commented Oct 22, 2020 at 9:20

1 Answer 1

Reset to default
4

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.

Improve this answer
4
  • 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? Commented Oct 22, 2020 at 10:46
  • 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. Commented Oct 22, 2020 at 10:49
  • Does the order make any difference? e.g. having it first partitioned by LIST (id) and the by RANGE (day) ? @LaurenzAlbe Commented Mar 30, 2022 at 12:34
  • 1
    @YannickSchuchmann That depends on your query. Partition pruning will be different. Commented Mar 30, 2022 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.