mirror of
https://github.com/zitadel/zitadel.git
synced 2026-08-17 16:35:14 -05:00
<!-- CURSOR_AGENT_PR_BODY_BEGIN --> ## Summary `ListUsers` with `LoginNameQuery` + equals/equals-ignore-case was very slow on large orgs (e.g. ~330k users) because the generated SQL filtered the `projections.login_names3` **view** on the computed `login_name_lower` expression and correlated that subquery on `users14.id`. Postgres nested-looped every user and never used `login_names3_users_search (instance_id, user_name_lower)`. This change rewrites the **query planner** for that hot path: when an equals/equals-ignore-case login-name filter is present (and not under `OR`/`NOT`), the user list query **INNER JOINs** an indexed matches subquery instead of filtering via the view expression. The matches SQL mirrors `user_by_login_name.sql` (`user_name_lower` / domain paths + `preferred` / `is_primary`). Non-equals methods and OR combinations keep the previous view-based filter so semantics stay unchanged. Also adds a k6 use case that mirrors login v2 discovery (`loginNameQuery` EQUALS_IGNORE_CASE + `organizationIdQuery`, `limit: 2`): ```bash cd benchmark make users_by_login_name USER_AMOUNT=100000 VUS=10 DURATION=60s ``` ## Approach 1. `NewLoginNameSearchQuery` for equals / equals-ignore-case returns a marker `loginNameEqualsFilter` (other methods unchanged). 2. `prepareUsersQuery` extracts that marker when safe, then: - builds the usual `sq.SelectBuilder` **without** the login-name view predicate - adds `JoinClause` to `user_login_name_matches(.sql)` / `_case_sensitive.sql` as `login_name_matches` - keeps metadata JOIN/`DISTINCT` only when metadata filters are present (same as before) 3. Embedded SQL files under `internal/query/` for the matches subquery. Local smoke against ~330k synthetic users: baseline ~1392ms → rewritten path ~0.4ms for a single equals-ignore-case lookup. ## Test plan - [x] `go test ./internal/query/ -run 'TestLoginName|TestUsers|TestUserByLoginName'` (after generate-stubs) - [x] Existing `user_test` expected SQL updated (no always-on metadata join; login-name equals uses JOIN) - [ ] Run k6 before/after on a large `USER_AMOUNT` (e.g. 50k–100k+) and compare `list_users_duration` p50/p95/p99 - [x] Manual login v2 username discovery against a large org - [x] Confirm OR / NOT / CONTAINS login-name queries still return expected results <!-- CURSOR_AGENT_PR_BODY_END --> <div><a href="https://cursor.com/agents/bc-252883bd-48d9-492e-b619-5ccfa93cf9c3"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/open-in-web-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/open-in-web-light.png"><img alt="Open in Web" width="114" height="28" src="https://cursor.com/assets/images/open-in-web-dark.png"></picture></a> <a href="https://cursor.com/background-agent?bcId=bc-252883bd-48d9-492e-b619-5ccfa93cf9c3"><picture><source media="(prefers-color-scheme: dark)" srcset="https://cursor.com/assets/images/open-in-cursor-dark.png"><source media="(prefers-color-scheme: light)" srcset="https://cursor.com/assets/images/open-in-cursor-light.png"><img alt="Open in Cursor" width="131" height="28" src="https://cursor.com/assets/images/open-in-cursor-dark.png"></picture></a> </div> --------- Co-authored-by: Cursor Agent <cursoragent@cursor.com> Co-authored-by: Silvan <adlerhurst@users.noreply.github.com>
Benchmarks
This package contains code for benchmarking specific endpoints of the API using k6.
Prerequisite
Structure
The use cases under tests are defined in src/use_cases. The implementation of ZITADEL resources and calls are located under src.
Execution
Env vars
VUS: Amount of parallel processes execute the test (default is 20)DURATION: Defines how long the tests are executed (default is200s)ZITADEL_HOST: URL of ZITADEL (default ishttp://localhost:8080)ADMIN_LOGIN_NAME: Loginanme of a human user withIAM_OWNER-roleADMIN_PASSWORD: password of the human userUSER_AMOUNT: Number of users created during setup for list-users benchmarks (default is2500)SETUP_CONCURRENCY: Max in-flight user-create requests during list-users setup (default is50). LargeUSER_AMOUNTwith unbounded parallelism can exhaust ephemeral ports (can't assign requested address).
To setup the tests we use the credentials of management console and log in using an admin. The user must be able to create organizations and all resources inside organizations.
ADMIN_LOGIN_NAME:zitadel-admin@zitadel.localhostADMIN_PASSWORD:Password1!
Test
Before you run the tests you need an initialized user. The tests don't implement the change password screen during login.
make human_password_login
setup: creates human users
test: uses the previously created humans to sign in using the login uimake machine_pat_login
setup: creates machines and a pat for each machine
test: calls user info endpoint with the given patsmake machine_client_credentials_login
setup: creates machines and a client credential secret for each machine
test: calls token endpoint with theclient_credentialsgrant type.make user_info
setup: creates human users and signs them in
test: calls user info endpoint using the given humansmake manipulate_user
test: creates a human, updates its profile, locks the user and then deletes itmake introspect
setup: creates projects, one api per project, one key per api and generates the jwt from the given keys
test: calls introspection endpoint using the given JWTsmake add_session
setup: creates human users
test: creates new sessions with user id checkmake oidc_session
setup: creates a service account to create the auth request and session.
test: creates an auth request, a session and links the session to the auth request. Implementation of this flow.make otp_session
setup: creates 1 human user for each VU and adds OTP Email to it
test: creates a session based on the login name of the user, sets the OTP Email challenge to the session and afterwards checks the OTP codemake password_session
setup: creates 1 human user for each VU and adds OTP Email to it
test: creates a session based on the login name of the user and checks for the password on a second stepmake machine_jwt_profile_grant
setup: generates private/public key, creates service accounts, adds a key
test: creates a token and calls user infomake machine_jwt_profile_grant_single_user
setup: generates private/public key, creates service account, adds a key
test: creates a token and calls user info in parallel for the same usermake users_by_metadata_key
setup: creates for half of the VUS a human user and a machine for the other half, adds 3 metadata to each user test: calls the list users endpoint and filters by a metadata keymake users_by_metadata_value
setup: creates for half of the VUS a human user and a machine for the other half, adds 3 metadata to each user test: calls the list users endpoint and filters by a metadata valuemake users_by_login_name
setup: createsUSER_AMOUNThuman users (default2500) in a new org, withSETUP_CONCURRENCYparallel creates (default50)
test: calls ListUsers the same way as login v2 (loginNameQuerywithEQUALS_IGNORE_CASE,organizationIdQuery,limit: 2)
note: to reproduce multi-second latency on the old query plan, use a large dataset, e.g.USER_AMOUNT=100000 VUS=10 DURATION=60smake verify_all_user_grants_exists
setup: creates 50 projects, 1 machine per VU test: creates a machine and grants all projects to the machine teardown: the organization is not removed to verify the data of the projections are correct. You can find additional information at the bottom of this file