RE: How to make partitioning scale better for larger numbers of partitions

From: Amit Langote [mailto:Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp]
> The immediate one I think is to refactor the planner such that the new
> pruning code, that we were able to utilize for SELECT in PG 11, can also
> be used for UPDATE/DELETE. Refactoring needed to replace the pruning
> algorithm was minimal for SELECT, but not so much for UPDATE/DELETE.
>
> Once we're able to utilize the new pruning algorithm for all the cases,
> we
> can move on to refactoring to avoid locking and opening of all partitions.
> As long as we're relying on constraint exclusion for partition pruning,
> which we still do for UPDATE/DELETE, we cannot do that because constraint
> exclusion has to look at each partition individually.
>
> The UPDATE/DELETE planning for partitioning using huge memory and CPU is
> a
> pretty big issue and refactoring planner to avoid that may be what's
> hardest of all the problems to be solved here.

Thank you. There seem to be many challenges to address... As a user and PG developer, I'd be happy to see some wiki page that describes the current performance characteristics in terms of # of partitions, the ideal and reachable performance, and the challenges to overcome to reach that ideal goal.

> If the benchmark contains queries that will need to access just one
> partition, then yes the planning part has is the biggest overhead.
>
> Execution-time overhead is limited to having an extra, possibly needless,
> Append node, but I know David has patch for that too.

That's good news, thanks. Our user will perform around a hundred single-row INSERT/SELECT/UPDATE/DELETE statements in each transaction, and those are PREPAREd. I hope PG 11 (with David's patches) will work well for that workload. I'll wait for Kato-san's pgbench -M prepared result.

Regards
Takayuki Tsunakawa