Creating 1 million users in PostgreSQL

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…

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:

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:

[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 🙂

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.