Re: Should we warn against using too many partitions?

On Fri, 24 May 2019 at 14:04, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> The latest patch on the thread linked from this CF entry (a modified
> version of your patch sent by Justin Pryzby) looks good to me.  Why not
> post it on this thread and link this one to the CF entry?

I'm not much of a fan of that patch:

+     <para>
+      When using table inheritance, partition hierarchies with more than a few
+      hundred partitions are not recommended.  Larger partition hierarchies may
+      incur long planning time, and, in the case of <command>UPDATE</command>
+      and <command>DELETE</command>, excessive memory usage.  When inheritance
+      is used, see also the limitations described in
+      <xref linkend="ddl-partitioning-constraint-exclusion"/>.
+     </para>

I'm a bit confused about this paragraph.  It introduces itself as
talking about table inheritance, then uses the word "partition" in
various places. I think that can be dropped.  The final sentence
throws me off as it tries to reduce the scope to only inheritance, but
as far as I understand that was already the scope of the paragraph,
unless of course "table inheritance" is not the same as "inheritance".
Without any insider knowledge on it, I've no idea if this
UPDATE/DELETE issue affects native partitioning too.

+     <para>
+      When using declarative partitioning, the overhead of query planning
+      is directly related to the number of unpruned partitions.  Planning is
+      generally fast with small numbers of unpruned partitions, even in
+      partition hierarchies containing many thousands of partitions.  However,
+      long planning time will be incurred by large partition hierarchies if
+      partition pruning is not possible during the planning phase.
+     </para>

This should really mention the excessive memory usage when many
partitions survive pruning.

I've attached 3 patches of what I think should go into master, pg11, and pg10.

> Or maybe, make
> this an open item, because we should update documentation back to v11?

I'll add this to the open items list since it includes master, and
shift the CF entry to point to this thread.

Authors are Robert Haas and Justin Pryzby, who I've included in the email.

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