Costly service account queries

We are running Keycloak (10.0.2) with a fairly large amount of users (a bit under 100,000). We are trying to optimize the system resource usage, as we’ve noticed that our Keycloak deployment is pretty hungry for resources.

The load on the database in particular seems very high. One of the queries that is being executed often is one like this (formatting is mine - I’ve also redacted some of the parameter values to protect the innocent):

select userentity0_.ID as ID1_73_, userentity0_.CREATED_TIMESTAMP as CREATED_2_73_, userentity0_.EMAIL as EMAIL3_73_,
       userentity0_.EMAIL_CONSTRAINT as EMAIL_CO4_73_, userentity0_.EMAIL_VERIFIED as EMAIL_VE5_73_, userentity0_.ENABLED as ENABLED6_73_,
       userentity0_.FEDERATION_LINK as FEDERATI7_73_, userentity0_.FIRST_NAME as FIRST_NA8_73_, userentity0_.LAST_NAME as LAST_NAM9_73_,
       userentity0_.NOT_BEFORE as NOT_BEF10_73_, userentity0_.REALM_ID as REALM_I11_73_, userentity0_.SERVICE_ACCOUNT_CLIENT_LINK as SERVICE12_73_,
       userentity0_.USERNAME as USERNAM13_73_ from USER_ENTITY userentity0_
where userentity0_.SERVICE_ACCOUNT_CLIENT_LINK='533d7XXX-bbXX-44XX-91XX-c343d3XXXXXX'
  and userentity0_.REALM_ID='our-realm'

This seems to be the getRealmUserByServiceAccount named query as defined in org.keycloak.models.jpa.entities.UserEntity.

Execution of this query seems very inefficient: although there are indices that cover the REALM_ID column, there is none that covers the SERVICE_ACCOUNT_CLIENT_LINK column. As a result, all rows in the table are evaluated.

Although USER_ENTRIES are cached, my understanding is that the cache lookup is based on something different than SERVICE_ACCOUNT_CLIENT_LINK. Therefor, I don’t expect the functionality that executes this query to benefit from existing caches.

As far as I can tell, our application is not making use of any service accounts:

  • None of the USER_ENTITY rows have a non-null value for that column.
  • None of the rows in CLIENT have a value for the SERVICE_ACCOUNTS_ENABLED column that’s different from 0x00.

Can we somehow configure Keycloak to skip this lookup? If not, is there a way to optimize the configuration of Keycloak around this functionality?

The invocation of the getRealmUserByServiceAccount seems to stem from our usage of the Keycloak-admin-client. With that, one typically first obtains a reference to a realm that is to be operated on, which invokes https://github.com/keycloak/keycloak/blob/main/services/src/main/java/org/keycloak/services/resources/admin/RealmsAdminResource.java#L179

When initializing a KeycloakIdentity object, the query is executed: keycloak/KeycloakIdentity.java at main · keycloak/keycloak · GitHub

I wonder if this can be prevented by checking if for the clientModel, the service account feature is enabled.