On 4/20/24 13:55, Michal Charemza wrote: > 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. > I tried a couple simple setups with many roles (user with many roles granted directly and with many roles granted through other roles), but I've been unable to reproduce 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. 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? > A reproducer would be great - a script that creates user/roles, and triggers the long login time would allow us to investigate that. Another option would be to get a perf profile from the process busy with logging the user in - assuming it's CPU-intensive, and not (e.g.) some sort of locking issue. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
