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.


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!

Hackers and Bugs lists migration and GMail’s filters

Hackers and Bugs are heavy loaded lists. So it’s wise to turn notification off for them. This may be done using Gmail’s filters. “Mark as Read” is the key to success.

Filters looking for terms in Subject
Filters looking for terms in Subject

Yesterday I began to receive strange notifications on my phone from GMail. First was from Stephen Frost, then from Tatsuo Ishii and so on.

I was sure that unlikely all these good people were mailing to me directly, so I opened my mail client to throw some light on the situation.

The letter from Stephen explained what is happening – pgsql-hackers and pgsql-bugs mailing lists migrated to a new software known as ‘PGLister’. You may find all details following this link

The “Subject:” header will no longer be changed to include the name of the list (for example, “[HACKERS]”).

Aha, I see now. So my filters are not working anymore. Time for changes.

If you filter your email based on those “Subject:” header insertions, you will need to adjust your filtering rules. We recommend looking at the industry standard “List-ID:” header instead.

You should use “list:” in “Has the words” field now.

Use "list: " operator instead
Use “list: ” operator instead

Then apply some labels, mark as read and, of course, never send it to Spam!

2017-11-14 09_43_41-Search results - - Microolap Mail
Apply some actions