Very high response time for setup licence key api with existing data

[edubacco]

🐛 Bug Report

Hi folks,
I'm encountering response times of around 1 minute when calling the API to set up the license key in my self-hosted SuperTokens instance. This delay coincides with a noticeable increase in CPU usage by the database, the query consuming many resources seems to be

SELECT count(?) as c FROM ( SELECT count(user_id) as num_login_methods, app_id, primary_or_recipe_user_id FROM app_id_to_user_id WHERE primary_or_recipe_user_id IN ( SELECT user_id FROM user_last_active WHERE app_id = $1 AND last_active_time >= $2 ) GROUP BY app_id, primary_or_recipe_user_id) uc WHERE num_login_methods > ?

Useful informations

I'm using this docker image: registry.supertokens.io/supertokens/supertokens-postgresql:9.0

I run the apps inside kubernetes, and AWS RDS aurora postgres database.
I have a paid licence, I use two different tenants connected to two different database instance. I activated roles and account linking. Every tenant has some thousand users.

My setup consists of three scaled instances of SuperTokens core, and three backend instances communicating with the core.
I use different databases per tenant, with one main tenant in one database instance and another tenant in a separate database instance.
Following guide, I configure SuperTokens core via API calls made by the backend application: at every backend startup, I do api calls to the core to inject configs (licence key, tenant configs, and so on).
The set licence call lasts minutes, while other setup api calls answer fast.

It seems a ST issue, can you have a look?

Thanks 🙏

link to discord chat we had: https://discord.com/channels/603466164219281420/1239606145195184178
cc @rishabhpoddar, @sattvikc

[sattvikc]

@edubacco Thank you for pointing out this issue. The fix is a bit complex and it might take a while to implement. Meanwhile, would request you to not have add license key call in the infra code for now. Once the fix is in, you would be notified from this issue and then you can re-add.

[edubacco]

Thanks for the update @sattvikc, I've already deployed that workaround, I wait for the fix.