Login v2 performance issue with a lot of users in database
š¬Self-hostedšŖµLoginšPerformanceāQuestionā
Solved
Hi all,
Currently we're testing zitadel as a solution for our company's needs. We were doing some stress testing and encountered performance issue on login. Currently we have around 5 million users created and login v2 takes a long time. After some investigation I've found this change which looks like breaks performance https://github.com/zitadel/zitadel/pull/10475
If I understand correctly basically what happens is that when query is build it ends up scanning whole user table ignoring indexes, because it generates query as such:
WHERE
projections.users14.instance_id = '12345678'
AND projections.users14.id IN (
SELECT projections.login_names3.user_id
FROM projections.login_names3
WHERE
projections.login_names3.instance_id = projections.users14.instance_id
AND projections.login_names3.user_id = projections.users14.id
AND lower(projections.login_names3.login_name) = 'username@example.com'
)
Is there a way to bypass this?
Currently we're testing zitadel as a solution for our company's needs. We were doing some stress testing and encountered performance issue on login. Currently we have around 5 million users created and login v2 takes a long time. After some investigation I've found this change which looks like breaks performance https://github.com/zitadel/zitadel/pull/10475
If I understand correctly basically what happens is that when query is build it ends up scanning whole user table ignoring indexes, because it generates query as such:
WHERE
projections.users14.instance_id = '12345678'
AND projections.users14.id IN (
SELECT projections.login_names3.user_id
FROM projections.login_names3
WHERE
projections.login_names3.instance_id = projections.users14.instance_id
AND projections.login_names3.user_id = projections.users14.id
AND lower(projections.login_names3.login_name) = 'username@example.com'
)
Is there a way to bypass this?
