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. 🙂
    UPD:

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

Advertisements

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:
http://microolap.com/products/connectivity/postgresdac/download/
or login to your private area on our site at
http://microolap.com/my/downloads/

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?

tl;dr:

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).

image7

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 https://wiki.postgresql.org/wiki/PGLister_Announce.

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: pgsql-hackers.lists.postgresql.org” 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 - pavel.golub@gf.microolap.com - Microolap Mail
Apply some actions