Postgres table partition by list limit

Question

I wonder if there is a limit for partition table by list where each subpartition table contains only one element.

For example, I have this partition table:

CREATE TABLE whatever (
    city_id         int not null,
    country_id      int not null,
) PARTITION BY LIST (country_id);

And I create millions of subpartition tables:

CREATE TABLE whatever_1 PARTITION OF whatever
    FOR VALUES IN (1);

CREATE TABLE whatever_2 PARTITION OF whatever
    FOR VALUES IN (2);

# until millions...

CREATE TABLE whatever_10000000 PARTITION OF whatever
    FOR VALUES IN (10000000);

Assuming an index on country_id, would that still work? Or Will I hit the 65000 limit as described here?

Answer 1

Even with PostgreSQL v13, anything that goes beyond at most a few thousand partitions won't work well, and it's better to stay lower.

The reason is that when you use a partitioned table in an SQL statement, the optimizer has to consider all partitions separately. It has to figure out which of the partitions it has to use and which not, and for all partitions that it uses it has to come up with an execution plan. Consequently, planning time will go up as the number of partitions increases. This may not matter for large analytical queries, where execution time dominates, but it will considerably slow down the execution of small statements.

Use longer lists or use range partitioning.