Michal Charemza <michal@charemza.name> writes:
> The issue is that we're hitting a strange performance problem on
> connection. Sometimes it can take ~25 to 40 seconds just to connect,
> although it's often way quicker. There seems to be no middle ground - never
> have I seen a connection take between 0.5 and 25 seconds for example. We
> suspect it's related to the number of roles the connecting user has
> (including via other roles), because if we remove all roles but one from
> the connecting user (the one that grants connection permissions),
> connecting is always virtually instantaneous.
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?
As Tomas said, a self-contained reproduction script would be very
helpful for looking into this.
> The closest issue that I can find that's similar is
> https://www.postgresql.org/message-id/flat/CAGvXd3OSMbJQwOSc-Tq-Ro1CAz%3DvggErdSG7pv2s6vmmTOLJSg%40mail.gmail.com,
> which reports that GRANT role is slow with a high number of roles - but in
> our case, it's connecting that's the problem, before (as far as we can
> tell) even one query is run.
That specific problem is (we think) new in v16, but the root cause
is an inefficient lookup mechanism that has been there a long time.
Maybe you have found a usage pattern that exposes its weakness in
older branches. If so, we could consider back-patching 14e991db8
further than v16 ... but I don't plan to take any risk there without
concrete evidence that it'd improve things.
regards, tom lane