Re: Extremely slow to establish connection when user has a high number of roles

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> It's not very clear what you mean by "sometimes". Is the slowness
reproducible for a particular user and role configuration, or does
it seem to come and go by itself?

Ah it's more come and go by itself - as in one connection takes 30 seconds,
then the next say 0.06s. It's happened for every user we've tried. Even
more anecdotally, I would say it happens more when the database is busy in
terms of tables being dropped/created and permissions changing.

Also: realise we did have one user that had directly was a member of
several thousand roles, but indirectly several million. It would sometimes
take 10 minutes for that user to connect. We've since changed that to one
role, and that user connects fine now.

> As Tomas said, a self-contained reproduction script would be very
helpful for looking into this.

Have tried... but alas it seems fine in anything but the production
environment. My closest attempt is attached to at least it show in more
detail how our system is setup, but it always works fine for me locally.

I am wondering - what happens on connection? What catalogue tables does
PostgreSQL check and how? What's allowed to happen concurrently and what
isn't? If I knew, maybe I could come up with a reproduction script that
does reproduce the issue?