IntenseFinance/zitadel
3
0
Fork 0
mirror of https://github.com/zitadel/zitadel.git synced 2026-08-17 16:35:14 -05:00
Code Issues Packages Projects Releases Wiki Activity
Files
main
zitadel/benchmark/Makefile
T
SilvanCursor AgentSilvan
 318acd4bc3 perf(query): speed up ListUsers login name equality filters (#12460) 
<!-- 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>&nbsp;<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>&nbsp;</div>

---------

Co-authored-by: Cursor Agent <cursoragent@cursor.com>
Co-authored-by: Silvan <adlerhurst@users.noreply.github.com>
2026-07-28 11:56:07 +00:00

113 lines
5.1 KiB
Makefile
Raw Permalink Blame History

VUS ?= 20
DURATION ?= "200s"
ZITADEL_HOST ?=
ADMIN_LOGIN_NAME ?=
ADMIN_PASSWORD ?=
DATE := $(shell date '+%d-%H:%M:%S')
K6 := ./../../xk6-modules/k6
.PHONY: human_password_login
human_password_login: bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/human_password_login.js --vus ${VUS} --duration ${DURATION} --out csv=output/human_password_login_${DATE}.csv
.PHONY: machine_pat_login
machine_pat_login: bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/machine_pat_login.js --vus ${VUS} --duration ${DURATION} --out csv=output/machine_pat_login_${DATE}.csv
.PHONY: machine_client_credentials_login
machine_client_credentials_login: bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/machine_client_credentials_login.js --vus ${VUS} --duration ${DURATION} --out csv=output/machine_client_credentials_login_${DATE}.csv
.PHONY: user_info
user_info: bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/user_info.js --vus ${VUS} --duration ${DURATION} --out csv=output/user_info_${DATE}.csv
.PHONY: manipulate_user
manipulate_user: bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/manipulate_user.js --vus ${VUS} --duration ${DURATION} --out csv=output/manipulate_user_${DATE}.csv
.PHONY: introspect
introspect: ensure_key_pair ensure_modules bundle
go install go.k6.io/xk6/cmd/xk6@latest
cd ../../xk6-modules && xk6 build --with xk6-zitadel=.
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/introspection.js --vus ${VUS} --duration ${DURATION} --out csv=output/introspect_${DATE}.csv
.PHONY: oidc_session
oidc_session: ensure_key_pair ensure_modules bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/oidc_session.js --vus ${VUS} --duration ${DURATION} --out csv=output/oidc_session_${DATE}.csv
.PHONY: otp_session
otp_session: ensure_key_pair ensure_modules bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/otp_session.js --vus ${VUS} --duration ${DURATION} --out csv=output/otp_session_${DATE}.csv
.PHONY: password_session
password_session: ensure_key_pair ensure_modules bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/password_session.js --vus ${VUS} --duration ${DURATION} --out csv=output/otp_session_${DATE}.csv
.PHONY: add_session
add_session: ensure_key_pair ensure_modules bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/add_session.js --vus ${VUS} --duration ${DURATION} --out csv=output/add_session_${DATE}.csv
.PHONY: machine_jwt_profile_grant
machine_jwt_profile_grant: ensure_modules ensure_key_pair bundle
go install go.k6.io/xk6/cmd/xk6@latest
cd ../../xk6-modules && xk6 build --with xk6-zitadel=.
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/machine_jwt_profile_grant.js --vus ${VUS} --duration ${DURATION} --out csv=output/machine_jwt_profile_grant_${DATE}.csv
.PHONY: machine_jwt_profile_grant_single_user
machine_jwt_profile_grant_single_user: ensure_modules ensure_key_pair bundle
go install go.k6.io/xk6/cmd/xk6@latest
cd ../../xk6-modules && xk6 build --with xk6-zitadel=.
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/machine_jwt_profile_grant_single_user.js --vus ${VUS} --duration ${DURATION} --out csv=output/machine_jwt_profile_grant_single_user_${DATE}.csv
.PHONY: verify_all_user_grants_exist
verify_all_user_grants_exist: ensure_modules bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/verify_all_user_grants_exist.js --vus ${VUS} --duration ${DURATION}
# --out csv=output/verify_all_user_grants_exist_${DATE}.csv
.PHONY: users_by_metadata_key
users_by_metadata_key: ensure_modules bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/users_by_metadata_key.js --vus ${VUS} --duration ${DURATION} --out csv=output/users_by_metadata_${DATE}.csv
.PHONY: users_by_metadata_value
users_by_metadata_value: ensure_modules bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/users_by_metadata_value.js --vus ${VUS} --duration ${DURATION} --out csv=output/users_by_metadata_${DATE}.csv
.PHONY: users_by_login_name
users_by_login_name: ensure_modules bundle
${K6} run --summary-trend-stats "min,avg,max,p(50),p(95),p(99)" dist/users_by_login_name.js --vus ${VUS} --duration ${DURATION} --out csv=output/users_by_login_name_${DATE}.csv
.PHONY: lint
lint:
npm i
npm run lint:fix
.PHONY: ensure_modules
ensure_modules:
ifeq (,$(wildcard $(PWD)/../../xk6-modules))
@echo "cloning xk6-modules"
cd ../.. && git clone https://github.com/zitadel/xk6-modules.git
endif
cd ../../xk6-modules && git pull
.PHONY: bundle
bundle:
mkdir -p output
npm i
npm run bundle
go install go.k6.io/xk6/cmd/xk6@latest
cd ../../xk6-modules && xk6 build --with xk6-zitadel=.
.PHONY: ensure_key_pair
ensure_key_pair:
ifeq (,$(wildcard $(PWD)/.keys))
mkdir .keys
endif
ifeq (,$(wildcard $(PWD)/.keys/key.pem))
openssl genrsa -out .keys/key.pem 2048
endif
ifeq (,$(wildcard $(PWD)/.keys/key.pem.pub))
openssl rsa -in .keys/key.pem -outform PEM -pubout -out .keys/key.pem.pub
endif
Reference in New Issue View Git Blame Copy Permalink