> 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?