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

Tom Lane:
> Actually, roles_is_member_of sucks before v16 too; the new thing
> is only that it's being invoked during GRANT ROLE. Using the
> roles created by the given test case, I see in v15:
>
> [...]
> So it takes ~3.5s to populate the roles_is_member_of cache for "acc"
> given this membership set. This is actually considerably worse than
> in v16 or HEAD, where the same test takes about 1.6s for me.

Ah, this reminds me that I hit the same problem about a year ago, but
haven't had the time to put together a test-case, yet. In my case, it's
like this:
- I have one role "authenticator" with which the application (PostgREST)
connects to the database.
- This role has been granted all of the actual user roles and will then
do a SET ROLE for each authenticated request it handles.
- In my case that's currently about 120k roles granted to
"authenticator", back then it was probably around 60k.
- The first request (SET ROLE) for each session took between 5 and 10
*minutes* to succeed - subsequent requests were instant.
- When the "authenticator" role is made SUPERUSER, the first request is
instant, too.

I guess this matches exactly what you are observing.

There is one more thing that is actually even worse, though: When you
try to cancel the query or terminate the backend while the SET ROLE is
still running, this will not work. It will not only not cancel the
query, but somehow leave the process for that backend in some kind of
zombie state that is impossible to recover from.

All of this was v15.

Best,

Wolfgang