Re: Creating 1 million users in PostgreSQL

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.

  1. When testing such tricks always create temp database. Because I accidentally used… no, no, not the production one.. but anyway. 🙂

    Since when temp database matters when creating cluster-wide role?
    © Ervin Weber

    So, yes. There is no need in test database. My bad! 🙂

  2. 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;
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; $$;
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.


PostgresDAC 3.4.0 is out! #BlackFriday

Microolap Technologies is pleased to announce the availability of PostgresDAC 3.4.0.

Take a 30% discount on any of our products and subscriptions!
Just use #BlackFriday code on any order page.

This new minor version introduces support for the latest PostgreSQL and OpenSSL libraries. Support for NEXTGEN and Lazarus (FreePascal) compilers improved.

Full change log:
[!] v10.1 client libraries added
[!] v10.1 dump & restore libraries (pg_dump.dll, pg_restore.dll) added
[!] Lazarus 1.6.4 support introduced
[!] OpenSSL 1.1.0g support added
[*] Improved ARC support for NEXTGEN compilers
[+] dsoNumericAsFloat option added to TPSQLDataset options
[-] “TPSQLDatabase.KeepConnection set to False may leave active connections on Android” bug fixed

You’re welcome to download the PostgresDAC v3.4.0 right now at:
or login to your private area on our site at

Code Quality Comparison of Firebird, MySQL, and PostgreSQL

I have read very interesting post “Code Quality Comparison of Firebird, MySQL, and PostgreSQL” today about static analysis of three open-source RDBMS. And I wonder, should we use static code analyzers on an ongoing basis, e.g. PVS Studio?


So, the code-quality rankings are as follows:

    • 1 place – Firebird and PostgreSQL.
    • 2 place – MySQL.



Please remember that any review or comparison, including this one, is subjective. Different approaches may produce different results (though it is mostly true for Firebird and PostgreSQL, but not for MySQL).


PostgreSQL on Android?

Yes, it’s possible! Recently I found great Android application Termux.

Termux is an Android terminal emulator and Linux environment app that works directly with no rooting or setup required. A minimal base system is installed automatically – additional packages are available using the APT package manager.

I installed it and my first command was
pkg search postgres

$ pkg search postgres
$ pkg search postgres

Wow! Not only we can have PostgreSQL on Android, but it’s up to date! Cool!