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.
Table of Contents
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...
|
1 2 3 4 5 |
BEGIN; SELECT 'CREATE USER xy' || id FROM generate_series(1, 1000000) AS id; gexec COMMIT; |
It simply generates a couple of SQL commands, which do exactly what I want. Here is the output:
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# SELECT 'CREATE USER xy' || id FROM generate_series(1, 1000000) AS id; ?column? ----------------------- CREATE USER xy1 CREATE USER xy2 CREATE USER xy3 CREATE USER xy4 CREATE USER xy5 CREATE USER xy6 CREATE USER xy7 |
The idea is to get 1 million statements.
|
1 2 3 4 5 |
test=# SELECT count(*) FROM pg_user; count ------- 1 (1 row) |
Here comes the trick: gexec executes the SQL we have just generated directly. Finally the transaction is committed:
|
1 2 3 4 5 |
[hs@zenbook master]$ time psql test < /tmp/a.sql > /dev/null real 1m0.009s user 0m4.212s sys 0m8.600s |
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 🙂
|
1 2 3 4 5 |
test=# SELECT count(*) FROM pg_user; count --------- 1000001 (1 row) |
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.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
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.
BEGIN;
SELECT 'CREATE USER xy' || id ||';'
FROM generate_series(4, 6) AS id;
gexec
COMMIT;
And the error i initially received was
ERROR: current transaction is aborted, commands ignored until end of transaction block
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.
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?
Very nice post. Specially very helpful when application demanding bulk PostgreSQL create users for testing purpose.