Re: BUG #4919: CREATE USER command slows down systemperformance

Yes, it seems problem in pg_auth flat file.

We are using db users to manage access rights to db tables and data, that
way we have two layer security - application and DB. Each system user has
it's own group role and groups have different access levels.

So we cannot use one login role for all users.

Because of using group roles we need to use pgbouncer connection pooling for
seperate users sessions to pool server and use this connection till user
closes session. We cannot user pgbouncer pooling and role managment if in
postgres is one login role for all users.

I hope there is some solution how to use login roles for each users and role
groups with grants to system objects up to ~500 000 users.

For example, Oracle database allows to create users more then 500 000
without performance problems in creation process. I suppose it is because
oracle don't use flat file to store all users.

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre(at)commandprompt(dot)com]
Sent: Wednesday, July 15, 2009 5:02 PM
To: Lauris Ulmanis
Cc: 'Heikki Linnakangas'; pgsql-bugs(at)postgresql(dot)org;
pgsql-performance(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #4919: CREATE USER command slows down
systemperformance

Lauris Ulmanis wrote:
> Hello again!
>
> I did test on my local test server
>
> I created up 500 000 users in function loop very quickly - within 48
> seconds. I did again this script reaching up to 1 billion users - results
> was the same - 48 seconds. It is very quickly.
>
> But problem seems is with transaction preparation because if in database
is
> 1 billion users and I want to create 1 new - it will take 4 seconds!
>
> After that I generated up to 2 billion users in this server (generation
> process took just 1.44 minutes of times - again quickly).
>
> And did 1 user creation again - now it took 9 seconds of time!
>
> What is a reason of this slowness? Is there a workaround or solution how
to
> avoid it?

My bet is on the pg_auth flat file. I doubt we have ever tested the
behavior of that code with 1 billion users ...

Do you really need 1 billion users? Are you planning on giving accounts
to every human being in the planet or what? I mean, what's the point of
this test?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.