Creating many (really many) users in PostgreSQLIn his post Hans-Jürgen Schönig showed how to easily and quickly create a million users in PostgreSQL; taking inspiration from such post, I decided to stress one of my virtual machines with a little more complex user creation use-case.
Roles in RolesOne feature of PostgreSQL roles is that they can contain other roles, creating a hierarchy of roles. Therefore, I decided to write a simple
plpgsqlfunction to loop creating a chain of roles at each iteration. The function
f_usersaccepts two integers:
deepis the number of roles within the single role inheritance chain;
how_manyis the number of iterations.
( 1 + deep ) x how_manyroles. Each role name is made by a random string and the iteration number, therefore preventing as much as possible collisions. The function code is as follows:
Please note the above code can be optimized reducing the number of
CREATE OR REPLACE FUNCTION f_users( deep int, how_many int ) RETURNS VOID AS $BODY$ DECLARE main_role_name text; current_role_name text; current_level int; iteration int; query text; BEGIN <<LP_MAIN>> FOR iteration IN 1..how_many LOOP -- main role main_role_name := 'role_test_' || md5( random()::text ) || '_' || iteration; RAISE DEBUG 'Main role is %', main_role_name; query := 'CREATE ROLE ' || main_role_name || ' WITH NOLOGIN CONNECTION LIMIT 0;'; RAISE DEBUG '%', query; EXECUTE query; <<LP_DEEP>> FOR current_level IN 1..deep BY 1 LOOP current_role_name := main_role_name || '_' || current_level; RAISE DEBUG 'Level % -> role %', current_level, current_role_name; query := 'CREATE ROLE ' || current_role_name || ' WITH IN ROLE ' || main_role_name || ' NOLOGIN CONNECTION LIMIT 0;'; RAISE DEBUG '%', query; EXECUTE query; END LOOP LP_DEEP; END LOOP LP_MAIN; END; $BODY$ LANGUAGE plpgsql;
RAISE(that implies string concatenation). The
connection limit 0is for safety reasons: it is not desiderable to have such automatically created roles to be of any practical use.
ResultsThe first attempt was short and sweet: 5000 roles within 1000 groups.
As readers can see, this took less than a second to perform. What about a 10x factor?
# SELECT f_users( 5, 1000 ); f_users --------- (1 row) Time: 965.479 ms
It seems time is growing linearly. Increase by a 5x factor:
# SELECT f_users( 5, 10000 ); f_users --------- (1 row) Time: 9118.100 ms
To recap, the following is the timing of role creations:
# SELECT f_users( 5, 50000 ); f_users --------- (1 row) Time: 104680.382 ms
FreeBSD 11.1-RELEASErunning PostgreSQL 9.6 with 512 MB of RAM without WAL archiving or any other replication active. I cannot hit one million roles in a single shoot in such machine because it starts swapping until the swap daemon freezes. In order to confirm such, let’s consider how many roles there are in my system:
the final result is greater than what is expected because I had already a discrete amount of roles. Not so bad for a database!
# SELECT count(*) FROM pg_roles; count -------- 552018