Re: Should we warn against using too many partitions?

Hi,

On Thu, Jun 6, 2019 at 1:44 PM David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>
> 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.

Agreed that some "best practices" text is overdue, so thanks for taking that up.

> 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.

Design advice like this is good.

> 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.

I wasn't sure at first if stuff like this should be mentioned in the
user-facing documentation, but your wording seems fine in general.

Thanks,
Amit