On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote: > Hi, > > 2017-11-07 16:11 GMT+01:00 Andres Freund <andres@anarazel.de>: > > > Hi, > > > > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote: > > > I'm using PostgreSQL 9.5.9 on Debian and experience slow execution of > > some > > > basic SET statements. > > > > > > I created about 1600 roles and use that setup for a multi tenancy > > > application: > > > > Hm. How often do you drop/create these roles? How many other > > roles/groups is one role a member of? > > > > I create between 10-40 roles per day.
Could you VACUUM (VERBOSE, FREEZE) that table and report the output? Do you ever delete roles?
Which table do you mean exactly? pg_catalog.pg_authid?
Sorry, forgot to write that: I delete about 2-3 roles per day.
> > Can you manually reproduce the problem? What times do you get if you > > manually run the statement? > > > > Unfortunately not. Every time I manually execute "SET ROLE ..." the > statement is pretty fast. I created a simple SQL file that contains the > following statements: > > --snip-- > SET ROLE tenant382; > SET ROLE tenant1337; > SET ROLE tenant2; > -- repeat the lines above 100k times > --snap-- > > When I execute those statements via 'time psql < set-roles.sql', the call > lasts 138,7 seconds. So 300k "SET ROLE" statements result in 0,46ms per > call on average.
And most of that is going to be roundtrip time. Hm. Could it be that you're just seeing the delays when pgbouncer establishes new pooling connections and you're attributing that to SET ROLE in your app?
I stopped using pgbouncer when I solely started using role 'admin' with "SET ROLE" statements. I use a connection pool (HikariCP) that renews connections after 30 minutes. I couldn't find a pattern yet when those slow statements occur.
Does using a few thousands roles and schemata in postgres scale well? I only found some theoretical descriptions of multi tenancy setups with postgres while googling.
Using tabulator in psql cli is pretty slow, mainly because pg_table_is_visible() is being called for many entries in pg_class.
