Re: Creating foreign key on partitioned table is too slow

On Thu, Oct 24, 2019 at 04:28:38PM -0700, Andres Freund wrote:
>Hi,
>
>On 2019-10-23 05:59:01 +0000, kato-sho@fujitsu.com wrote:
>> To benchmark with tpcb model, I tried to create a foreign key in the partitioned history table, but backend process
killedby OOM.
 
>> the number of partitions is 8192. I tried in master(commit: ad4b7aeb84).
>
>Obviously this should be improved. But I think it's also worthwhile to
>note that using 8k partitions is very unlikely to be a good choice for
>anything. The metadata, partition pruning, etc overhead is just going to
>be very substantial.
>

True. Especially with two partitioned tables, each with 8k partitions.

I do think it makes sense to reduce the memory usage, because just
eating all available memory (in the extreme case) is not very nice. I've
added that patch to the CF, although the patch I shared is very crude
and I'm by no means suggesting it's how it should be done ultimately.

The other bit (speed of planning with 8k partitions) is probably a more
general issue, and I suppose we'll improve that over time. I don't think
there's a simple change magically improving that.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services