DELETE /realms/:realm/orgs/:orgId takes ages (seconds)

[mkyc]

When calling delete organization with this method it takes about 2.5 seconds to delete single organization on my installation while any other operation takes milliseconds. Running it in parallel doesn't change that value very much, it just takes longer for each request to finish (10 threads mean that requests will take ~8 times longer).

Connecting it with behavior that you cannot delete realm that contains organizations it makes any realm creation / deletion automation pretty hard if you have enough organizations (couple thousands in tests in my case).

PH2 version: quay.io/phasetwo/phasetwo-keycloak:24.0.5

[xgp]

@mkyc How many organizations do you have? How many members per organization? How many identity providers?

I don't see the same behavior, but it may be due to different data.

[mkyc]

in example test realm I had:

• over 6k organizations
• lets say about 200 members per organization (but it's hard to count for me right now)
• 3 identity providers

[xgp]

Thanks for the detail. Is that 3 identity providers per organization?

I ask because this line in the delete event handler:
https://github.com/p2-inc/keycloak-orgs/blob/main/src/main/java/io/phasetwo/service/resource/OrganizationResourceProviderFactory.java#L218

Has a lazy approach that iterates through all of the identity providers in the system:
https://github.com/p2-inc/keycloak-orgs/blob/main/src/main/java/io/phasetwo/service/model/jpa/OrganizationAdapter.java#L322-L323

in order to find the ones for that organization.

[mkyc]

Nope. Inside organization there is no identity provider. Those are on realm level.

One more thing I can think of which might influence that behavior is that I have Infinispan cache defined between two nodes for HA.

[xgp]

Nothing happens related to orgs in infinispan. It is a strange behavior, as deleting one row, and a few hundred foreign key records shouldn't be a big deal.

However, how often do you plan to actually be deleting orgs? I know it's not an ideal performance characteristic, but is it going to happen often?

[mkyc]

In general that is tests related issue.

We have number of test and dev environments. Each environment gets its own realm. Development and qa operations introduce some weird stuff into that environments (which is expected and understandable). Every some time someone decides, that this environment is "too broken" and we basically delete it and create new one.

Due to CI processes we have sometimes thousands of organizations in realm, so that removal time issue is noticeable.

[xgp]

@mkyc Thanks for the description. We're not really targeting such test use cases, so I doubt we will spend the time to debug this one. I'll leave it open if you have interest in trying to optimize the delete time. My guess is Hibernate is doing something naive like iterating through all the members and running an individual SQL DELETE. I'd suggest starting with running your tests with Hibernate set to DEBUG logging so you can see what's happening on a delete. Might be some JPA magic you could do to improve the performance.

[xkamil]

Is it possible to add bulk delete endpoint for organizations?
Now we have only DELETE /auth/realms/{realm}/orgs/{id} and it would be nice to also have DELETE /auth/realms/{realm}/orgs.
I can see that most of the code is already there as all organizations are removed from realm when realm is removed.

[xgp]

It's possible, but my concerns are:

1. There is no precedent for such bulk endpoints in the Keycloak API, and we try to model our API based on theirs
2. It doesn't solve the problem of how long it would take, unless you optimize the query AND all of the listeners.

In any case, we'd look at a PR and a case for why it should be included.

[xkamil]

what about this code link ?
I see that all organizations are removed when realm is removed. I added endpoint to call this via api locally + test and it works. I don't know how it behave with numbers like @mkyc mentioned above because in test it's hard to recreate this scenario.
If it's already there and it looks like it's used mayby it's worth exposing it via api so @mkyc can test it and provide us with more feedback on performance?

I forked this repo and created code with this endpoint that you may consider using:
PR