Hans-Jürgen Schönig wrote a great post “Creating 1 million users in PostgreSQL”. I just love such things! Well done! Thanks.
But since there is no possibility to leave comments under original post (BTW why?), I’ll create my own post.
- When testing such tricks always create temp database. Because I accidentally used… no, no, not the production one.. but anyway. 🙂
UPD:Since when temp database matters when creating cluster-wide role?
© Ervin WeberSo, yes. There is no need in test database. My bad! 🙂
- Hans-Jürgen used shell, psql and \gexec combination. However, we may do all these steps using psql only
To make a long story short, this is my variant of creating 1 million users in PostgreSQL:
Test=# CREATE DATABASE test_role_db; CREATE DATABASE Time: 7947,657 ms Test=# \c test_role_db You are now connected to database "test_role_db". test_role_db=# \timing Timing is on. test_role_db=# DO $$ test_role_db$# BEGIN test_role_db$# FOR i IN 1..1000000 LOOP test_role_db$# EXECUTE 'CREATE ROLE xy' || i; test_role_db$# END LOOP; test_role_db$# END; $$; DO Time: 81267,482 ms
On my test environment generating 1 million users in a single transaction takes around 1 minute 21 seconds. This is a little bit longer than Hans-Jürgen’s timing. Suppose this is due to the hardware difference.
Pasha Golub's Blog 