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

Hi,

We're running PostgreSQL as essentially a data warehouse, and we have a few
thousand roles, which are used to grant permissions on a table-by-table
basis to a few thousand users, so a user would typically have say between 1
and 2 thousand roles. There is also quite a lot of "churn" in terms of
tables being created/removed, and permissions changed.

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.

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. The database is busy, say up to 60-80% on a 16
VCPU machine - even if it's a "good amount" below 100%, the issue occurs.

Is there anything we can do to investigate (or hopefully fix!) the issue?

Thanks,

Michal

------

A description of what you are trying to achieve and what results you
expect.:
We would like to connect to the database - expect it to connect in less
than 1 second, but sometimes 25 - 40s.

PostgreSQL version number you are running:
PostgreSQL 14.10 on aarch64-unknown-linux-gnu, compiled by
aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit

How you installed PostgreSQL:
Via AWS/Amazon Aurora

Changes made to the settings in the postgresql.conf file
In attached CSV file

Operating system and version:
Unknown

What program you're using to connect to PostgreSQL:
Python + SQLAlchemy, psql, or also via Amazon Quicksight (Unsure which
client they use under the hood, but it surfaces connection timeout errors,
which we suspect is due to the issue described above)

Is there anything relevant or unusual in the PostgreSQL server logs?:
No

For questions about any kind of error:
N/A