Re: making update/delete of inheritance trees scale better

On Thu, 1 Apr 2021 at 15:09, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> Note that the patch over there doesn't do anything about
> AcquireExecutorLocks() bottleneck, as there are some yet-unsolved race
> conditions that were previously discussed here:
>
> https://www.postgresql.org/message-id/flat/CAKJS1f_kfRQ3ZpjQyHC7=PK9vrhxiHBQFZ+hc0JCwwnRKkF3hg(at)mail(dot)gmail(dot)com

The only way I can think of so far to get around having to lock all
child partitions is pretty drastic and likely it's too late to change
anyway. The idea is that when you attach an existing table as a
partition that you can no longer access it directly. We'd likely have
to invent a new relkind for partitions for that to work. This would
mean that we shouldn't ever need to lock individual partitions as all
things which access them must do so via the parent. I imagined that we
might still be able to truncate partitions with an ALTER TABLE ...
TRUNCATE PARTITION ...; or something. It feels a bit late for all
that now though, especially so with all the CONCURRENTLY work Alvaro
has done to make ATTACH/DETACH not take an AEL.

Additionally, I imagine doing this would upset a lot of people who do
direct accesses to partitions.

Robert also mentioned some ideas in [1]. However, it seems that might
have a performance impact on locking in general.

I think some other DBMSes might not allow direct access to partitions.
Perhaps the locking issue is the reason why.

David

[1] https://www.postgresql.org/message-id/CA%2BTgmoYbtm1uuDne3rRp_uNA2RFiBwXX1ngj3RSLxOfc3oS7cQ%40mail.gmail.com