I've did loads of testing for create/load/delete big number of users
into database and I've send PR for add some additional indexes into
database to improve performance of operations with big number of users
in DB - https://github.com/keycloak/keycloak/pull/2456

Note that adding DB indexes is tricky as the performance gain (or loss)
might be highly dependent on underlying database. Some example:

1) PostgreSQL is adding the indexes by default just for primary keys and
unique constraints. But it doesn't add indexes for foreign keys. So when
you have 50K users in DB and 150K role mappings (each user member of 3
roles), then this query take more than 20 ms:

select * from USER_ROLE_MAPPING where USER_ID='123';


2) MySQL is adding the btree indexes by default even for foreign keys.
So the same query like above took less than 1 ms by default.


So to improve the time on postgres, the index for USER_ID column in
USER_ROLE_MAPPING is needed, however for MySQL adding the index doesn't
have any effect as it's already there. In the end, I've explicitly added
the indexes into changelog file by default and I can see very good
performance for both mysql and postgresql and operations are not
dependent on number of users in DB.

However in theory, adding the indexes may have bad effect for some
databases and slow down the time for create new user etc. I personally
don't want to test performance with all supported DBs :) So another
approach might be to not explicitly add the indexes and let DBA to do
that. I've added the possibility to enable logging some overall
statistics from Hibernate, so people can check it by themselves. Let me
know if you rather want to go this way and not add indexes by default.

Marek
_______________________________________________
keycloak-dev mailing list
keycloak-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/keycloak-dev