Creating 1 million users in PostgreSQL

12.2017 / Category: / Tags: |

While I was at database training to migrate some poor fellows from Oracle to PostgreSQL, I was confronted with this simple question: “How many users can you have in PostgreSQL?”. Obviously somebody has made some bad experiences with other databases, or Oracle in particular, to come up with this kind of question. “Well, as many as you want. How many do you need?”. After some brief discussion it seems that people just did not believe in what I said. So I decided to show them how easily a large number of database users can be created in no time.

Creating a large number of users

The good thing about PostgreSQL is that DDLs can happen inside transactions. As user creation is done by DDLs, it is possible to create all those new accounts in a single transaction. Also 1 million users is a lot, so we clearly don’t want to compile this list by hand. And: The entire process has to be quick and take only 5 minutes at a training course.

In short: I needed something fast, so I decided to come up with the following script...

It simply generates a couple of SQL commands, which do exactly what I want. Here is the output:

The idea is to get 1 million statements.

Before the script is executed, the table is empty:

Here comes the trick: gexec executes the SQL we have just generated directly. Finally the transaction is committed:

On my laptop, generating 1 million users in a single transaction takes around 1 minute. Many people might be surprised that it is actually possible to create this number of users. Others might be delighted to see that it only takes one minute. The first feedback I got from my folks here at Cybertec HQ was: “Why does it take so long 😉 ”. Well, people in the PostgreSQL community see things differently sometimes 🙂

Here is what happens:

The bottom line is really that PostgreSQL can easily handle a million users without any problems. It is good to see, that it is possible to create so many accounts with just 4 lines of code.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Santosh Tiwary
3 years ago

Very nice post. Specially very helpful when application demanding bulk PostgreSQL create users for testing purpose.

Carnot Antonio Romero
Carnot Antonio Romero
4 years ago

Very useful, thanks. Because even when you integrate PG with an external authentication server like LDAP, you still have to create a user account in a PG database for every user you want to have permission to access the data, it's very important to know "can I create 100K or 1M users" and have authenticated access not turn into a serious bottleneck. Do you know whether performance in any way suffers when one of the million users tries to log in, vs. a database with maybe 100 user accounts?

5 years ago

This is a joke, right?

I mean of course I can create 10 million users using a script. The question is can PgSQL actually handle a million users actually doing stuff?

And I think the answer would also be "sure" but it would depend a lot on what it is that those millions of users are doing.

I came here wanting to learn about how well PgSQL does in building responsive million-users applications.

Goldie Fernandes
Goldie Fernandes
6 years ago

Wonderful article! I initially thought that the gexec was a typo, and that you needed a once over for the article! Boy was i mistaken! So glad to have learned this!

btw, i think the select needs a semicolon. When i tried to run it, it complained with the following error. The fixed sql ran beautifully.

SELECT 'CREATE USER xy' || id ||';'
FROM generate_series(4, 6) AS id;

And the error i initially received was
ERROR: current transaction is aborted, commands ignored until end of transaction block

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram