When doing database training the other week to migrate some poor fellows from Oracle to PostgreSQL I was confronted with a 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 simply 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 thing has to happen within 5 minutes at a training course.
In short: It had to be fast, so I decided to come up with the following script…
BEGIN; SELECT 'CREATE USER xy' || id FROM generate_series(1, 1000000) AS id; \gexec COMMIT;
What it does is simply to generate a couple of SQL commands, which do exactly what I want. Here is the output:
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.
Before the script is executed, the table is empty:
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:
[[email protected] 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 so many 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:
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.