2017-11-07 20:45 GMT+01:00 Andres Freund <and...@anarazel.de>: > On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote: > > Hi, > > > > 2017-11-07 16:11 GMT+01:00 Andres Freund <and...@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. Cheers, Ulf
