Re: Should we warn against using too many partitions?

On Fri, 24 May 2019 at 22:00, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> I've attached the pg10 and pg11 patches with that updated... and also
> the master one (unchanged) with the hopes that the CF bot picks that
> one.

I got talking to Andres about this at PGCon after a use case of 250k
partitions was brought to our attention. I was thinking about the best
way to handle this on the long flight home and after studying the
current docs I really feel that they fairly well describe what we've
done so far implementing table partitioning, but they offer next to
nothing on best practices on how to make the most of the feature.

I've done some work on this today and what I've ended up with is an
entirely new section to the partitioning docs about best practices
which provides a bit of detail on how you might go about choosing the
partition key. It gives an example of why LIST partitioning on a set
of values that may grow significantly over time might be a bad idea.
It talks about memory growth with more partitions and mentions that
rel cache might become a problem even if queries are touching a small
number of partitions per query, but a large number per session.

The attached patch is aimed at master. PG11 will need the planner
memory and performance part tweaked and for PG10 I'll do that plus
remove the mention of PRIMARY KEY and UNIQUE constraints on the
partitioned table.

Does anyone see anything wrong with doing this? I don't think there
should be an issue adding a section to the docs right at the end as
it's not causing any resequencing.

Or does anyone have any better ideas or better examples to give? or
any comments?

If it looks okay I can post version for PG11 and PG10 for review, but
I'd like to get this in fairly soon.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services