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.


psql on bash on Windows

1. Windows 10 supports Subsystem for Linux.
2. psql has some issues with non-English locales under Windows, see Notes for Windows Users.

So I installed WSL, then choose Ubuntu, started Bash and executed:

pasha_golub@PASHA:~$ sudo apt install postgresql-client

Cool. Now I have native Linux psql and, of course, I want to try Pavel Stehule’s Postgres pager!

Windows, Linux, psql…

As for me I’m pretty sure that it’s not cool to use non-Latin letters for database objects names. In other words this is true Mauvais Ton.

However the reality dictates its own rules. I saw a lot of databases with names in Hebrew, Russian etc. What is the disadvantage of such names using?

Linux console

If we’re talking about Linux console then the answer most likely will be: “There is no disadvantage at all”, though I haven’t every existing distributive tested, but only a few. In this article we will use pg_live – a Xubuntu-based live CD designed and optimized expressly for the PostgreSQL Database administrator and enthusiast.

Let’s create database with Cyrillic letters under gorgeous name of “Колбаса”. First of all start psql. You may use such command:

$psql -h /tmp -U ubuntu ubuntu

After psql started we’ll create and connect to “Колбаса” database:

Creating database with Cyrillic letters in Ubuntu
Creating database with Cyrillic letters in Ubuntu

As you can see no magic is needed. In Windows it’s quite the contrary.

Windows console

Just for information: I’m using WinXP with SP3 installed.

Let’s the wizardry begin.  First of all we must set code page used in the Windows console :

C:\Program Files\PostgreSQL\8.3\bin\>chcp 1251
Active code page: 1251

Could not find this for three days, was cursing up hill and down dale. But it turnes out the only thing needed is the Notes for Windows users chapter in the psql manual.

Do not forget to set Lucida Console font either:

Setting Lucida Console font
Setting Lucida Console font

Starting psql:

C:\Program Files\PostgreSQL\8.3\bin\>psql -U pasha "Test"
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.
Type:  copyright for distribution terms
       h for help with SQL commands
       ? for help with psql commands
       g or terminate with semicolon to execute query
       q to quit


Creating database:

Test=# CREATE DATABASE "Колбаса";
Test=# l
          List of databases
       Name       | Owner | Encoding
 Comments         | pasha | UTF8
 DBDEMOS          | pasha | UTF8
 Test             | pasha | UTF8
 UTF8             | pasha | UTF8
 huy              | pasha | UTF8
 postgres         | pasha | UTF8
 template0        | pasha | UTF8
 template1        | pasha | UTF8
 template_postgis | pasha | UTF8
 Колбаса          | pasha | UTF8
(10 rows)

Connecting to just created database:

Test=# c "Колбаса"
FATAL:  database "Колбаса" does not exist
Previous connection kept

Oops! Houston, we have a problem.

Checking encoding parameters:

Test=# SHOW client_encoding;
(1 row)

Test=# SHOW server_encoding;
(1 row)

Can we at least drop it?

Test=# DROP DATABASE "Колбаса";

May be I’m stupid, however I didn’t find a way to connect to “Колбаса” database using psql.