Thread: BUG #4919: CREATE USER command slows down system performance

From:
"Lauris Ulmanis"
Date:

The following bug has been logged online:

Bug reference:      4919
Logged by:          Lauris Ulmanis
Email address:      
PostgreSQL version: 8.3.7, 8.4.0
Operating system:   Any
Description:        CREATE USER command slows down system performance
Details:

When user count in Postgres database reaches up to 500 000 - database
command of creating users 'CREATE USER' slows down to 5-10 seconds per user.


What could be a reason of this problem and is there any solution how to
avoid it?

For each of user can be associated up to 10 roles with grants to system
objects.
From:
Heikki Linnakangas
Date:

Lauris Ulmanis wrote:
> The following bug has been logged online:
>
> Bug reference:      4919
> Logged by:          Lauris Ulmanis
> Email address:      
> PostgreSQL version: 8.3.7, 8.4.0
> Operating system:   Any
> Description:        CREATE USER command slows down system performance
> Details:
>
> When user count in Postgres database reaches up to 500 000 - database
> command of creating users 'CREATE USER' slows down to 5-10 seconds per user.

I don't see such slowdown here, by just issuing 500000 CREATE USER
commands in a loop.

> For each of user can be associated up to 10 roles with grants to system
> objects.

That may be related..

Can you produce a repeatable test case?

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
From:
"Lauris Ulmanis"
Date:

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?


-----Original Message-----
From: Heikki Linnakangas [mailto:]
Sent: Tuesday, July 14, 2009 6:09 PM
To: Lauris Ulmanis
Cc: 
Subject: Re: [BUGS] BUG #4919: CREATE USER command slows down system
performance

Lauris Ulmanis wrote:
> The following bug has been logged online:
>
> Bug reference:      4919
> Logged by:          Lauris Ulmanis
> Email address:      
> PostgreSQL version: 8.3.7, 8.4.0
> Operating system:   Any
> Description:        CREATE USER command slows down system performance
> Details:
>
> When user count in Postgres database reaches up to 500 000 - database
> command of creating users 'CREATE USER' slows down to 5-10 seconds per
user.

I don't see such slowdown here, by just issuing 500000 CREATE USER
commands in a loop.

> For each of user can be associated up to 10 roles with grants to system
> objects.

That may be related..

Can you produce a repeatable test case?

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com


From:
Alvaro Herrera
Date:

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.

From:
toruvinn
Date:

On Wed, 15 Jul 2009 16:02:09 +0200, Alvaro Herrera
<> wrote:
> My bet is on the pg_auth flat file.  I doubt we have ever tested the
> behavior of that code with 1 billion users ...
I've noticed this behaviour some time ago, on a cluster with 50k+ roles
(not sure about the number now). Restoring the backup took a lot of time,
especially when the users file grew significantly and each additional user
caused PG to rewrite the whole file.
I never bothered to report this, as it's not like the users are
(re)created every day, it was just a one-time run (== some extra time for
another coffee during restore ;-)).
I was always wondering, though, why PostgreSQL uses this approach and not
its catalogs.

Regards,
--
ru

From:
Alvaro Herrera
Date:

toruvinn wrote:
> On Wed, 15 Jul 2009 16:02:09 +0200, Alvaro Herrera
> <> wrote:
>> My bet is on the pg_auth flat file.  I doubt we have ever tested the
>> behavior of that code with 1 billion users ...

> I was always wondering, though, why PostgreSQL uses this approach and not
> its catalogs.

It does use the catalog for most things.  THe flatfile is used for the
situations where the catalogs are not yet ready to be read.

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

From:
Tom Lane
Date:

Alvaro Herrera <> writes:
> toruvinn wrote:
>> I was always wondering, though, why PostgreSQL uses this approach and not
>> its catalogs.

> It does use the catalog for most things.  THe flatfile is used for the
> situations where the catalogs are not yet ready to be read.

Now that we have SQL-level CONNECT privilege, I wonder just how much
functionality would be lost if we got rid of the flat files and told
people they had to use CONNECT to do any per-user or per-database
access control.

The main point I can see offhand is that password checking would have
to be done a lot later in the startup sequence, with correspondingly
more cycles wasted to reject bad passwords.

            regards, tom lane

From:
Marko Kreen
Date:

On 7/15/09, Tom Lane <> wrote:
> Alvaro Herrera <> writes:
>
> > toruvinn wrote:
>  >> I was always wondering, though, why PostgreSQL uses this approach and not
>  >> its catalogs.
>
>  > It does use the catalog for most things.  THe flatfile is used for the
>  > situations where the catalogs are not yet ready to be read.
>
>
> Now that we have SQL-level CONNECT privilege, I wonder just how much
>  functionality would be lost if we got rid of the flat files and told
>  people they had to use CONNECT to do any per-user or per-database
>  access control.
>
>  The main point I can see offhand is that password checking would have
>  to be done a lot later in the startup sequence, with correspondingly
>  more cycles wasted to reject bad passwords.

From security standpoint, wasting more cycles on bad passwords is good,
as it decreases the rate bruteforce password scanning can happen.

And I cannot imagine a scenario where performance on invalid logins
can be relevant..

--
marko

From:
David Wilson
Date:

On Wed, Jul 15, 2009 at 11:10 AM, Marko Kreen<> wrote:

> From security standpoint, wasting more cycles on bad passwords is good,
> as it decreases the rate bruteforce password scanning can happen.
>
> And I cannot imagine a scenario where performance on invalid logins
> can be relevant..

DoS attacks. The longer it takes to reject an invalid login, the fewer
invalid login attempts it takes to DoS the server.

--
- David T. Wilson


From:
Tom Lane
Date:

David Wilson <> writes:
> On Wed, Jul 15, 2009 at 11:10 AM, Marko Kreen<> wrote:
>> From security standpoint, wasting more cycles on bad passwords is good,
>> as it decreases the rate bruteforce password scanning can happen.
>>
>> And I cannot imagine a scenario where performance on invalid logins
>> can be relevant..

> DoS attacks. The longer it takes to reject an invalid login, the fewer
> invalid login attempts it takes to DoS the server.

Yeah, but even with the current setup, an attacker who can fire
connection request packets at your postmaster port is not going to have
any trouble DoS'ing the service.  We expend quite a lot of cycles before
getting to the password challenge already.

            regards, tom lane

From:
Marko Kreen
Date:

On 7/15/09, David Wilson <> wrote:
> On Wed, Jul 15, 2009 at 11:10 AM, Marko Kreen<> wrote:
>  > From security standpoint, wasting more cycles on bad passwords is good,
>  > as it decreases the rate bruteforce password scanning can happen.
>  >
>  > And I cannot imagine a scenario where performance on invalid logins
>  > can be relevant..
>
>
> DoS attacks. The longer it takes to reject an invalid login, the fewer
>  invalid login attempts it takes to DoS the server.

No, this is not a good argument against it.  Especially if you consider
that DoS via hanging-connect or SSL is still there.

Compared to minor DoS, the password-leakage is much worse danger.

--
marko

From:
"Lauris Ulmanis"
Date:

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:]
Sent: Wednesday, July 15, 2009 5:02 PM
To: Lauris Ulmanis
Cc: 'Heikki Linnakangas'; ;

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.


From:
Bruce Momjian
Date:

Tom Lane wrote:
> Alvaro Herrera <> writes:
> > toruvinn wrote:
> >> I was always wondering, though, why PostgreSQL uses this approach and not
> >> its catalogs.
>
> > It does use the catalog for most things.  THe flatfile is used for the
> > situations where the catalogs are not yet ready to be read.
>
> Now that we have SQL-level CONNECT privilege, I wonder just how much
> functionality would be lost if we got rid of the flat files and told
> people they had to use CONNECT to do any per-user or per-database
> access control.
>
> The main point I can see offhand is that password checking would have
> to be done a lot later in the startup sequence, with correspondingly
> more cycles wasted to reject bad passwords.

Is this a TODO?

--
  Bruce Momjian  <>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

From:
Tom Lane
Date:

Bruce Momjian <> writes:
> Tom Lane wrote:
>> Now that we have SQL-level CONNECT privilege, I wonder just how much
>> functionality would be lost if we got rid of the flat files and told
>> people they had to use CONNECT to do any per-user or per-database
>> access control.
>>
>> The main point I can see offhand is that password checking would have
>> to be done a lot later in the startup sequence, with correspondingly
>> more cycles wasted to reject bad passwords.

> Is this a TODO?

Well, it's a TO-THINK-ABOUT anyway.  I think the appropriate next step
would not be to write code, but to do a detailed investigation of what
would be gained or lost.  I don't remember exactly what we do with the
flat-file contents.

            regards, tom lane

From:
Alvaro Herrera
Date:

Tom Lane wrote:

> Well, it's a TO-THINK-ABOUT anyway.  I think the appropriate next step
> would not be to write code, but to do a detailed investigation of what
> would be gained or lost.  I don't remember exactly what we do with the
> flat-file contents.

Maybe what we need is not to get rid of the flat files, but to speed
them up.  If we're worried about speed in the pg_authid flatfile, and
come up with a solution to that problem, what will we do with the
pg_database flatfile when it grows too large?  We can't just get rid of
it, because autovacuum needs to access it.

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

From:
Tom Lane
Date:

Alvaro Herrera <> writes:
> Tom Lane wrote:
>> ... I don't remember exactly what we do with the
>> flat-file contents.

> Maybe what we need is not to get rid of the flat files, but to speed
> them up.  If we're worried about speed in the pg_authid flatfile, and
> come up with a solution to that problem, what will we do with the
> pg_database flatfile when it grows too large?  We can't just get rid of
> it, because autovacuum needs to access it.

Well, one of the components of the TODO would have to be to figure out a
way to fix autovacuum to avoid that.

Or we could consider getting rid of the pg_auth flatfile while keeping
the pg_database one, which would fix the issue for role names anyway...
but it's certainly an ugly compromise.

            regards, tom lane

From:
Tom Lane
Date:

I wrote:
> Alvaro Herrera <> writes:
>> Tom Lane wrote:
>>> ... I don't remember exactly what we do with the
>>> flat-file contents.

>> Maybe what we need is not to get rid of the flat files, but to speed
>> them up.  If we're worried about speed in the pg_authid flatfile, and
>> come up with a solution to that problem, what will we do with the
>> pg_database flatfile when it grows too large?  We can't just get rid of
>> it, because autovacuum needs to access it.

> Well, one of the components of the TODO would have to be to figure out a
> way to fix autovacuum to avoid that.

Actually, I had forgotten that we were using the pg_database flatfile
for purposes other than authentication checks.  In particular, we need
it during backend startup to map from database name to database OID,
without which it's impossible to locate the system catalogs for the
target database.  It's pretty hard to see a way around that one.
We could grovel through pg_database itself, as indeed is done to rebuild
the flatfile during system start.  But that's certainly not going to be
fast in cases where there are enough DBs to make the flatfile slow.

So on third thought, Alvaro's right: the only real solution here is to
adopt a more efficient representation of the flat files.  Maybe some
sort of simple hashtable arrangement would work.  (Rendering them not so
flat anymore...)

            regards, tom lane

From:
Alvaro Herrera
Date:

Tom Lane wrote:

> Actually, I had forgotten that we were using the pg_database flatfile
> for purposes other than authentication checks.  In particular, we need
> it during backend startup to map from database name to database OID,
> without which it's impossible to locate the system catalogs for the
> target database.  It's pretty hard to see a way around that one.
> We could grovel through pg_database itself, as indeed is done to rebuild
> the flatfile during system start.  But that's certainly not going to be
> fast in cases where there are enough DBs to make the flatfile slow.

Also, IIRC flatfiles were introduced precisely to avoid having to read
the catalogs manually.

> So on third thought, Alvaro's right: the only real solution here is to
> adopt a more efficient representation of the flat files.  Maybe some
> sort of simple hashtable arrangement would work.  (Rendering them not so
> flat anymore...)

As long as there's a simple API, there should be no problem.

(Except that it would be nice to be able to build the file incrementally
...  If we have to write out a million lines each time a millionth user
is created, there will still be a bottleneck at CREATE USER time.)

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

From:
Tom Lane
Date:

Alvaro Herrera <> writes:
> Tom Lane wrote:
>> So on third thought, Alvaro's right: the only real solution here is to
>> adopt a more efficient representation of the flat files.  Maybe some
>> sort of simple hashtable arrangement would work.  (Rendering them not so
>> flat anymore...)

> As long as there's a simple API, there should be no problem.

> (Except that it would be nice to be able to build the file incrementally
> ...  If we have to write out a million lines each time a millionth user
> is created, there will still be a bottleneck at CREATE USER time.)

If we allow the requirements to creep on this, we'll soon find ourselves
either using or reinventing BDB for the flatfiles.  Ick.

[ thinks for awhile... ]

In some sense this is a bootstrap problem: what does it take to get to
the point of being able to read pg_database and its indexes?  That is
necessarily not dependent on the particular database we want to join.
Maybe we could solve it by having the relcache write a "global" cache
file containing only entries for the global tables, and load that before
we have identified the database we want to join (after which, we'll load
another cache file for the local entries).  It would doubtless take some
rearrangement of the backend startup sequence, but it doesn't seem
obviously impossible.

            regards, tom lane

From:
Alvaro Herrera
Date:

Tom Lane wrote:

> In some sense this is a bootstrap problem: what does it take to get to
> the point of being able to read pg_database and its indexes?  That is
> necessarily not dependent on the particular database we want to join.
> Maybe we could solve it by having the relcache write a "global" cache
> file containing only entries for the global tables, and load that before
> we have identified the database we want to join (after which, we'll load
> another cache file for the local entries).

This sounds good, because autovacuum could probably use this too.

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

From:
Tom Lane
Date:

Alvaro Herrera <> writes:
> Tom Lane wrote:
>> In some sense this is a bootstrap problem: what does it take to get to
>> the point of being able to read pg_database and its indexes?  That is
>> necessarily not dependent on the particular database we want to join.
>> Maybe we could solve it by having the relcache write a "global" cache
>> file containing only entries for the global tables, and load that before
>> we have identified the database we want to join (after which, we'll load
>> another cache file for the local entries).

> This sounds good, because autovacuum could probably use this too.

Maybe I'll look at this after commitfest is over.  I haven't messed
with the bootstrap sequence in awhile, but I used to remember how
it worked ...

As far as AV is concerned, taking this approach would likely mean
turning the launcher into a full-fledged backend just like the workers.
Do you see any problem with that?

            regards, tom lane

From:
Bruce Momjian
Date:

Tom Lane wrote:
> Bruce Momjian <> writes:
> > Tom Lane wrote:
> >> Now that we have SQL-level CONNECT privilege, I wonder just how much
> >> functionality would be lost if we got rid of the flat files and told
> >> people they had to use CONNECT to do any per-user or per-database
> >> access control.
> >>
> >> The main point I can see offhand is that password checking would have
> >> to be done a lot later in the startup sequence, with correspondingly
> >> more cycles wasted to reject bad passwords.
>
> > Is this a TODO?
>
> Well, it's a TO-THINK-ABOUT anyway.  I think the appropriate next step
> would not be to write code, but to do a detailed investigation of what
> would be gained or lost.  I don't remember exactly what we do with the
> flat-file contents.

The flat file is the username/password sorted list.  We load that info
into the postmaster in an array that we can binary sort.

I wonder how big the postmaster process address space was when handling
2 billion users:

    http://archives.postgresql.org/pgsql-bugs/2009-07/msg00176.php

It seems just storing many users in the postmaster could be burdensome,
but fixing that would be creating something like a database, which we
already have.  ;-)

--
  Bruce Momjian  <>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

From:
Alvaro Herrera
Date:

Lauris Ulmanis wrote:

> When user count in Postgres database reaches up to 500 000 - database
> command of creating users 'CREATE USER' slows down to 5-10 seconds per user.

A bunch of commits along the direction of eliminating what we thought
was the main cause of the slowdown have just concluded.  If you could
grab the current CVS version (or alternative a snapshot tarball from the
FTP, but make sure it contains my commit as of 5 minutes ago) and try
your test case with it, it would be great.

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