Re: Slow GRANT ROLE on PostgreSQL 16 with thousands of ROLEs

First of all thank you for looking into this.

At the moment we workaround the problem by altering `acc` ROLE into a SUPERUSER
in PostgreSQL 16 instances. It sidestep the problem and having the lowest cost
to implement for us. While at first we think this feels like opening a security
hole, it does not introduce side effects for **our use case** by the way our
application make use of this `acc` ROLE.

Of course we cannot recommend the workaround we took to others having similar
situation.

On Fri, Mar 22, 2024 at 7:59 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Nathan Bossart <nathandbossart@gmail.com> writes:
> > On Thu, Mar 21, 2024 at 03:40:12PM -0500, Nathan Bossart wrote:
> >> On Thu, Mar 21, 2024 at 04:31:45PM -0400, Tom Lane wrote:
> >>> I don't think we have any really cheap way to de-duplicate the role
> >>> OIDs, especially seeing that it has to be done on-the-fly within the
> >>> collection loop, and the order of roles_list is at least potentially
> >>> interesting.  Not sure how to make further progress without a lot of
> >>> work.
>
> >> Assuming these are larger lists, this might benefit from optimizations
> >> involving SIMD intrinsics.
>
> > Never mind.  With the reproduction script, I'm only seeing a ~2%
> > improvement with my patches.
>
> Yeah, you cannot beat an O(N^2) problem by throwing SIMD at it.
>
> However ... I just remembered that we have a Bloom filter implementation
> in core now (src/backend/lib/bloomfilter.c).  How about using that
> to quickly reject (hopefully) most role OIDs, and only do the
> list_member_oid check if the filter passes?
>
>                         regards, tom lane