Re: Slow execution of SET ROLE, SET search_path and RESET ROLE

2017-11-07 20:45 GMT+01:00 Andres Freund <andres(at)anarazel(dot)de>:

> On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote:
> > Hi,
> >
> > 2017-11-07 16:11 GMT+01:00 Andres Freund <andres(at)anarazel(dot)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