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