mirror of
https://github.com/andwati/zitadel.git
synced 2026-10-18 05:52:51 +00:00
main
307
Commits
| Author | SHA1 | Message | Date | |
|---|---|---|---|---|
   
|
0a08e7269a |
fix(eventstore): omit owners on empty unique constraint inserts (#12785)
<!-- CURSOR_AGENT_PR_BODY_BEGIN --> # Which Problems Are Solved After #12740, setup on existing deployments (v4.17.x → v4.18.0) fails before migration 78 can add `eventstore.unique_constraints.owners`. Starting any setup step pushes `system.migration.started`, which inserts a unique constraint that names the `owners` column. On upgraded databases that column does not exist yet, so setup dies on `76_users14_login_equality_indexes` with `SQLSTATE 42703`. # How the Problems Are Solved Empty-owner unique-constraint inserts no longer name the `owners` column: - `len(Owners) == 0` → `INSERT (instance_id, unique_type, unique_field)` - tagged `WithOwners(...)` → unchanged `INSERT (..., owners)` Setup started/done events never stamp owners, so 76–78 can start. Migration 78 still adds the column; after that, omitted `owners` uses `DEFAULT '{}'`. Both v2 and v3 pushers apply the same rule. Mixed pushes split into two INSERT batches so tagged and empty rows do not share a column list. # Additional Changes - v2 SQL mocks now expect the three-column INSERT for untagged adds, plus tagged and mixed cases - Pusher tests cover mixed tagged+empty adds and adds against a table that does not have `owners` yet - The missing-column pusher test drops `owners` and pushes in one transaction (`PushWithClient`), then rolls back so the shared test schema is not mutated. Other tests may stall on `AccessExclusiveLock` until rollback. # Additional Context - Closes #12780 - Follow-up for PR #12740 - Operators do not need to re-run init or apply DDL outside setup Verified locally: - `go test ./internal/eventstore ./internal/v2/eventstore/postgres ./internal/eventstore/v3` (including `-race` on those packages) - Empty-owner insert succeeds when the `owners` column is missing; tagged insert still requires the column <!-- CURSOR_AGENT_PR_BODY_END --> <div><a href="https://cursor.com/agents/bc-31bfb5c4-5888-43f0-85cf-af1af097ca30?cursor_ref=pr_footer&cursor_cta=open_in_web"><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-31bfb5c4-5888-43f0-85cf-af1af097ca30&cursor_ref=pr_footer&cursor_cta=open_in_cursor"><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 <[email protected]> Co-authored-by: Silvan <[email protected]> |
||
  
|
4bc484dac9 |
perf(eventstore): read projection events per event type (#12753)
# Which Problems Are Solved - A projection that subscribes to several event types gets slower the further it falls behind. - The handler reads its events with one query: `aggregate_type = ANY(...) AND event_type = ANY(...)`, ordered by position and limited to the bulk limit. - The `es_projection` index returns each (aggregate type, event type) range in position order, but Postgres cannot merge those ranges. - So each batch reads every matching event after the projection's position, sorts them all, and keeps only the bulk limit. - A projection that is N events behind therefore reads about N rows per batch. Catching up costs about N² in total, and once events arrive faster than such batches can process them, the projection does not recover. - Every query that triggers the projection waits for that catch-up. Examples are `GetSession` and the notification handlers. # How the Problems Are Solved - **New opt-in, `SearchQueryBuilder.ScanEventTypesSeparately()`.** It applies to event queries for a single instance, ascending and limited, whose sub-queries filter only by aggregate and event types. - For such a query, the SQL layer reads each distinct (aggregate type, event type) combination in its own `ORDER BY … LIMIT` subquery, using `UNNEST … CROSS JOIN LATERAL`. - It then merges the results with the same sort key and limit. - Each subquery is one `es_projection` range read in position order, so it stops at the limit. A batch reads at most `limit × combinations` rows, however far behind the projection is. - Every other query renders exactly as before. - **The projection handler** now passes its real combinations, one sub-query per aggregate type instead of the cross product of all types, and sets the opt-in. - **Results are identical to the single query.** - The sort key (`position, in_tx_order, instance_id, aggregate_type, aggregate_id, sequence`) is a total order, and the global top n is contained in the union of each combination's top n. - The cursor from #12703 and the open-transaction cap apply inside every subquery. - A test against a real Postgres compares both shapes from every cursor and every position, for several limits. The test events include positions shared by several transactions, whose `in_tx_order` restarts. # Additional Changes This is a separate commit and can be split into its own PR: - `cmd/defaults.yaml` sets `BulkLimit: 1000` for the `sessions` projection. - `projection_state_latency` gets histogram buckets up to one week. Until now, every projection more than 30 minutes behind was reported in the same top bucket. # Additional Context These numbers come from a local, CPU-pinned sandbox, for comparing builds only. - **Database:** Postgres 17 in a VM with 8 vCPUs and 16 GiB. The `Eventstore.Autovacuum` and `Projections` settings mirror a production-like setup. - **Starting state:** `sessions8` 4.8M events behind, restored byte-identically for every run. Batch query for `sessions8`, `EXPLAIN ANALYZE`, median of 5 runs, parallel workers at the default: | | before | after | | --- | ---: | ---: | | at the backlog, limit 1000 | 832 ms | 4.0 ms | | at the backlog, limit 200 | 832 ms | 1.0 ms | | at the head (idle poll) | 0.14 ms | 0.19 ms | | generic plan (`plan_cache_mode=force_generic_plan`), at the backlog | 1,042 ms | 4.0 ms | Draining `sessions8` from that backlog for 600 s, 3 runs per build (median, with the range in brackets): | | before | after | | --- | ---: | ---: | | events/s | 975 (932-1,032) | 7,811 (7,523-7,955) | | mean batch time, including writes | 1.03 s | 0.13 s | | still behind after 600 s | 4.2M | 17k-277k | A new index `(instance_id, position, in_tx_order, aggregate_type, event_type)` with the old query was evaluated as an alternative and rejected. Under a generic plan, Postgres chose it for projections whose events are rare and walked the whole event stream: 430 ms instead of 0.15 ms. This is the behaviour that led to dropping `es_instance_position` in #10626. - Follow-up for #12703 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Opus 5 <[email protected]> Co-authored-by: Copilot Autofix powered by AI <[email protected]> |
||
|
|
3ff4fbc7e4 |
fix(eventstore): add owners to unique constraints (#12740)
<!-- CURSOR_AGENT_PR_BODY_BEGIN -->
# Which Problems Are Solved
Bulk unique-constraint deletes (org, IDP with many links, user, project)
reconstruct every uniqueness key in Go and emit N `DELETE`s. That can
OOM on large tenants.
A one-shot owner backfill is also not enough: during a rolling deploy,
older binaries still insert `owners = '{}'`, so a later owner-only
delete would miss those rows.
Owner-only org/user/grant delete only matches rows whose `owners` bag
contains that tag. Several grant and member types stamped the aggregate
resource owner, which is not always the user’s home org or the granted
org. Username-scope rewrites also dropped `user:` tags, so owner-only
user delete could miss rewritten username rows.
Command must not switch to owner-only delete on the first 79 of a
version, because leftover `'{}'` rows from rolling-update overlap are
only backfilled on a later 79. A failed 79 must not turn owner-delete
on, and backfill joins must not stamp the wrong org (unverified domains,
org-scoped usernames colliding with instance-scoped ones).
# How the Problems Are Solved
Uniqueness stays the existing primary key `(instance_id, unique_type,
unique_field)`. `owners` is only a lifecycle bag of tags (`org:<id>`,
`user:<id>`, `idp:<id>`, `project:<id>`, `grant:<id>`).
- Add `owners TEXT[] NOT NULL DEFAULT '{}'` on
`eventstore.unique_constraints` (init SQL for greenfield, setup 78 for
existing clusters).
- Create `unique_constraints_owners_gin` with `CREATE INDEX
CONCURRENTLY` **outside a transaction** (same pattern as step 76).
Built-in `GIN (owners)` only; no `btree_gin`.
- Stamp `WithOwners` on **add** events. Field-level removes ignore
owners. `RemoveByOwner` stores the tag in `Owners` and both v1/v3 and v2
pushers run `DELETE … WHERE instance_id = $1 AND owners @>
ARRAY[$tag]::text[]`.
- Setup 79 backfills `owners` from projections via numbered
`cmd/setup/79/*.sql` files. Table names are interpolated from
`internal/query/projection` constants. Matches use `unique_field IN (x,
lower(x))` so the primary key can be used. Missing projection tables
(`42P01`) are skipped.
- Org domains are tagged only when `is_verified`. Instance-scoped
usernames join only users that are not org-scoped (organization settings
or domain policy `user_login_must_be_domain`). Every username row still
gets `org:` + `user:` of that user.
- 79 is a **repeatable** migration. `Check` re-runs when
`lastRun["version"]` changed or when setup-only `ForceFinalize` is set
and `lastRun["finalized"]` is still false. `Finalized` is written only
after Execute succeeds.
- Failed setup events keep the last successful `lastRun`. Command reads
`finalized` only from `system.migration.repeatable.done`.
- Org / user / project remove skip N-key event replay **when that
`finalized` bit is true**. Otherwise they keep today’s N-key lists
**and** still emit `RemoveByOwner`. The “ready” result is cached in
memory once true.
Setup writes `finalized` on the 79 repeatable.done payload:
- First 79 of a version: backfill only, `finalized=false` (unless
`ForceFinalize` is already set).
- Later 79: version change or
`BackfillUniqueConstraintOwners.ForceFinalize` /
`ZITADEL_BACKFILLUNIQUECONSTRAINTOWNERS_FORCEFINALIZE` after the rolling
update is done. That run backfills leftovers and writes
`finalized=true`.
Member and grant owner bags now include every org that should delete the
row:
- Instance, org, and project members also stamp
`org:<user_resource_owner>`.
- User grants stamp grant, user, project, and granted orgs.
- Project grant members stamp the project org, user org, and granted
org.
- Project grants stamp both orgs plus `grant:<id>`. Grant remove emits
`RemoveByOwner(grant)`.
- Username-scope rewrites keep `user:<id>` as well as `org:`.
- User/grant orgs are carried as unexported add-event fields (not
persisted JSON). `WithOwners` skips empty and duplicate tags.
# Additional Changes
- Table-driven owner-tag tests, including project grant (two `org:` tags
plus `grant:`), cross-org members, user grants, and username-scope
rewrite `user:` tags.
- Table-driven 79 `Check` + `finalizeAfterSuccess` tests for version
change, sequential version bumps, ForceFinalize restart, and
already-finalized skip.
- Command tests: a failed 79 event with `finalized=true` does not enable
owner-delete.
- Integration fixtures (verified vs unverified org domain;
instance-scoped `bob` vs org-scoped `bob`).
- Instance-only and global unique types stay untagged (`'{}'`). Instance
wipe still uses `UniqueConstraintInstanceRemove`.
- Pusher owner cases live in `eventstore_pusher_owners_test.go` so
`eventstore_pusher_test.go` stays under 1k lines.
# Additional Context
A later cleanup can delete the N-key replay helpers once a release that
ran 79 a second time (or an operator who set `ForceFinalize`) is in
production. That is not required for correctness.
<!-- CURSOR_AGENT_PR_BODY_END -->
<div><a
href="https://cursor.com/agents/bc-44f9bc9b-9a93-4cd9-8dd1-663a7b9aad2a?cursor_ref=pr_footer&cursor_cta=open_in_web"><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-44f9bc9b-9a93-4cd9-8dd1-663a7b9aad2a&cursor_ref=pr_footer&cursor_cta=open_in_cursor"><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 <[email protected]>
Co-authored-by: Silvan <[email protected]>
|
||
|
|
fbe7b96138 |
fix(eventstore): prevent projections from skipping events (#12703)
<!-- CURSOR_AGENT_PR_BODY_BEGIN -->
# Which Problems Are Solved
Handler/v2 projections (`users14` and every other event projection) can
overlook committed events under load:
- `eventstore.commands_to_events` stamped `position` with `NOW()`
(transaction start). A slow create TX could commit after a faster TX
that started later, so the projector stored a later position and never
saw the earlier one.
- Resume used `OFFSET` on a **filtered** row count. That could skip
`in_tx_order = 1` at the next position (for example `user.human.added`).
- Two transactions in the same microsecond both start at `in_tx_order =
1`. A cursor of `in_tx_order > last` alone would drop the other TX’s
first event.
This does not repair rows that are already missing from projections.
# How the Problems Are Solved
- Setup step 77 replaces `eventstore.commands_to_events` so `created_at
= statement_timestamp()` and `position = EXTRACT(EPOCH FROM
clock_timestamp())` at INSERT time. Initialise `08_events_table.sql`
matches that for new databases. The function is `VOLATILE` to match
`clock_timestamp()`.
- The projector still drains in-flight writers with `pg_advisory_lock` /
`pg_advisory_unlock` in one statement, then caps with `position <=
EXTRACT(EPOCH FROM now())` (projector TX start). The exclusive lock is
**not** held across the event `SELECT`. The cap must stay `now()`, not
`clock_timestamp()`, or later inserts become a high-water mark.
- Resume is a lexicographic `EventSortKey` on `(position, in_tx_order,
instance_id, aggregate_type, aggregate_id, sequence)` via
`AfterEventSortKey`. SQL `OFFSET` is no longer used for projection
catch-up.
- Handler and FieldHandler persist the last event’s `in_tx_order` in
`current_states.filter_offset` (existing column, new meaning). Step 77
backfills matching rows from `events2`; unmatched rows keep their
previous value.
# Additional Changes
- `eventstore.Event` exposes `InTxOrder() uint32` (BaseEvent, repository
Event, v3 event, v1 models).
- v3 push SELECT/RETURNING now includes `in_tx_order` so newly written
events carry a real ordinal.
- `WithMinPosition` stays an inclusive `PositionAtLeast` floor and does
not smash the sort-key cursor.
- `SearchQuery.PositionAfter(decimal)` is unchanged (used by
access-token lookup). Do not confuse it with
`SearchQueryBuilder.AfterEventSortKey`.
- `commands_to_events` in setup 77 and initialise 08 is `VOLATILE
PARALLEL SAFE`. Shipped 40/64/70 stay `STABLE` because they still stamp
with `NOW()`.
- Drop unused `last_owner TEXT` DECLARE from setup 77 and initialise
`08_events_table.sql` (`i INTEGER` remains for the enforce-owner loop).
Shipped 40/64/70 keep the leftover.
- This PR’s migration is numbered 77 because `main` already has setup 76
(`76_users14_login_equality_indexes`). First-loop order is s70, then
main’s 76, then this 77.
# Additional Context
**Reviewer map**
| Start here | Why |
| --- | --- |
| `cmd/setup/77.sql` + `cmd/setup/77.go` | Position stamp + atomic
function replace/backfill after shipped `s70` and main’s s76 |
| `internal/eventstore/event_sort_key.go` + `search_query.go` |
Canonical cursor and `AfterEventSortKey` |
| `internal/eventstore/handler/v2/{state.go,handler.go}` |
Persist/resume cursor; `eventQuery` |
| `internal/eventstore/repository/sql/{query.go,postgres.go}` | Tuple
`>` filter, 6-column `ORDER BY`, await cap |
| `internal/eventstore/v3/push.go` / `push.sql` | Scan `in_tx_order` on
write |
Mechanical / low-risk: handler and query test fixtures, Event interface
stubs (`event_base.go`, repository Event, mocks, v1 models).
**Do not look for** a `70.sql` change — shipped migrations stay as on
`main`. Setup 40/64 still use `NOW()`; step 77 overwrites the function
afterward. Fresh `FirstInstance` (runs before `s70`) can still stamp
`NOW()` until 77; runtime pushes after 77 use `clock_timestamp()`.
Main’s `cmd/setup/76.go` is the users14 login-equality indexes, not this
stamp.
**Rollout**
1. Run setup so 77 replaces `commands_to_events` and backfills
`filter_offset`.
2. Restart ZITADEL so projectors load the new resume path.
3. Existing missing `users14` rows are not repaired by this PR.
**Tests**
- `go test ./internal/eventstore/... ./cmd/setup/`
- Sort-key SQL fixtures in `query_sort_key_test.go` (no `OFFSET`;
aggregate-id queries keep 6-column `ORDER BY`)
- `cmd/setup/77_test.go` asserts the embed has no unused `last_owner`
and still uses `clock_timestamp()` / `VOLATILE PARALLEL SAFE`
Related: #8509, #10480 (remove OFFSET), #10560 (advisory locks), #10710
(stable ORDER BY).
Out of scope: repairing already-missing projection rows, holding the
exclusive lock during SELECT, rewriting historical setup 40/64 SQL.
<!-- CURSOR_AGENT_PR_BODY_END -->
<div><a
href="https://cursor.com/agents/bc-3da6eb51-11d4-4a8a-8ba0-e190f116df05?cursor_ref=pr_footer&cursor_cta=open_in_web"><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-3da6eb51-11d4-4a8a-8ba0-e190f116df05&cursor_ref=pr_footer&cursor_cta=open_in_cursor"><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 <[email protected]>
Co-authored-by: Silvan <[email protected]>
|
||
|
|
75bc058bce |
feat(eventstore): autovacuum tuning for events2 table (#12449)
# Which Problems Are Solved
In Zitadel's append-only event-sourced architecture, the
`eventstore.events2` table grows indefinitely. PostgreSQL's default
autovacuum uses a percentage-based scale factor, so as the table grows,
the number of changed rows required to trigger a `VACUUM` or `ANALYZE`
drifts towards infinity. Without regular vacuums, the table's Visibility
Map becomes stale, disabling fast Index-Only Scans and forcing expensive
heap reads. Without regular analyzes, query planner statistics become
stale, leading to suboptimal execution plans.
This causes eventstore operations to progressively degrade as `events2`
grows, even without CPU, memory, or I/O saturation. A manual `VACUUM
ANALYZE` immediately restores performance, confirming the root cause.
- If `Eventstore.Autovacuum` is left at its default, `events2` keeps
using PostgreSQL's default, percentage-based autovacuum/autoanalyze
scale factors, which become impractically infrequent on large tables.
- There was previously no supported way to apply static, table-level
autovacuum tuning to `events2` through Zitadel's own configuration/setup
process.
# How the Problems Are Solved
- Added an `Eventstore.Autovacuum` runtime configuration block to
`cmd/defaults.yaml` (disabled by default):
```yaml
Eventstore:
Autovacuum:
Enabled: false # ZITADEL_EVENTSTORE_AUTOVACUUM_ENABLED
VacuumThreshold: 50000 # ZITADEL_EVENTSTORE_AUTOVACUUM_VACUUMTHRESHOLD
AnalyzeThreshold: 50000 # ZITADEL_EVENTSTORE_AUTOVACUUM_ANALYZETHRESHOLD
```
- Added a repeatable `zitadel setup` migration step
(`cmd/setup/eventstore_autovacuum.go`) that:
- When `Enabled: true`, disables the percentage-based
`autovacuum_vacuum_scale_factor`, `autovacuum_analyze_scale_factor`, and
`autovacuum_vacuum_insert_scale_factor` on `eventstore.events2`, and
applies static thresholds (`autovacuum_vacuum_insert_threshold`,
`autovacuum_vacuum_threshold`, `autovacuum_analyze_threshold`) from the
config instead.
- When `Enabled: false`, resets those storage parameters on
`eventstore.events2` back to the cluster defaults.
- Implements `Repeatable.Check()` so the step only re-runs when the
configuration actually changed since the last `zitadel setup` run.
- Added documentation in the new "Performance tuning" page.
# Additional Changes
- Move the projection documentation into performance tuning page
- Expand and update the projection documentation to the latest state in
zitadel. (contained some stale information)
# Additional Context
Several other issues describe read-performance symptoms consistent with
this same root cause (stale `events2` visibility map / planner
statistics at scale, without resource saturation). Since this PR
addresses the shared root cause:
- Closes #12448
- Closes #10754
- Closes #10260
- Closes #8585
- Closes #9239
---
_Generated by [Claude
Code](https://claude.ai/code/session_01HoKvEY7niCVgLCz7CajBwW)_
---------
Co-authored-by: Claude <[email protected]>
Co-authored-by: Copilot Autofix powered by AI <[email protected]>
Co-authored-by: Silvan <[email protected]>
|
||
 
|
f9995ee39c |
fix: increase performance of ListUser by login name ignore case (#12350)
# Which Problems Are Solved Login v2 uses the `users.v2.ListUsers`-endpoint to get a user by login name. This query had an inefficient `WHERE`-clause. # How the Problems Are Solved Update the view and use a specific clause for this query. # Additional information Added in https://github.com/zitadel/zitadel/pull/10475 --------- Co-authored-by: Marco A. <[email protected]> Co-authored-by: Livio Spring <[email protected]> |
||
|
6082e59d47 |
fix(eventstore): allow overwriting resource owner of events (#12261)
# Which Problems Are Solved - The eventstore did not support intentionally overwriting the resource owner when creating events for aggregates that may be reused across owners. - Resource owner handling was implicit and could not be controlled per command/event type. - We needed a safe way to distinguish between: - keeping the existing aggregate owner, and - explicitly setting a new owner for specific create-like events. # How the Problems Are Solved - Introduced a new eventstore command type with an explicit enforce_owner flag. - Updated eventstore.commands_to_events and eventstore.push so owner assignment is now explicit: - if enforce_owner is true, the command owner is written - if enforce_owner is false, the existing aggregate owner is retained when present - Added EnforceResourceOwnerCommand and wiring so command types can opt in to enforced owner behavior. - Wired the new behavior through the v3 eventstore push path, including compatibility fallback for older command type mapping. - Added migration/setup changes to register and use the new command type and SQL functions. - Added and updated tests for owner overwrite and aggregate ID reuse scenarios. # Additional Changes - Added small migration/setup robustness improvements related to eventstore setup ordering and helper reuse. - Added focused test coverage for enforced owner behavior and sequencing. - Events that currently allow owner changes (implement EnforceResourceOwner) are: - AddedEvent (action) - GroupAddedEvent - StartedEvent (idp intent) - ProjectAddedEvent - HumanAddedEvent - HumanRegisteredEvent - MachineAddedEvent - CreatedEvent (schema user) # Additional Context - Follow-up for eventstore owner-handling correctness in create flows and aggregate ID reuse cases. - No additional issue link was attached for this change. --------- Co-authored-by: abhishek kumar gupta <[email protected]> |
||
|
Livio SpringandGitHub
|
fbb6a406c8 |
chore: fix generators in crypto package (#12008)
# Which Problems Are Solved `@zitadel/api:generate-go` failed due to duplicate generated files. https://github.com/zitadel/zitadel/pull/11820 removed the old generated file and added a generate command to the `internal/crypto/crypto.go` file. However, there was already a `internal/crypto/generate.go` with a different output file name (the old file). # How the Problems Are Solved Removed the `internal/crypto/generate.go` file and moved the second generate into `internal/crypto/code.go`. # Additional Changes Noticed that mockgen is an old version and updated it. Also then checked all other tools and updated them. # Additional Context - relates to #11820 - noted internally |
||
|
de1cab9976 |
refactor: merge relational table projections into one handler (#11740)
## Problem description During tests we found concurrency issues in the relational table projections. Tests got flaky if a table (e.g. organization domains) had too many foreign keys to tables which were projected by another handler. ## Resolution The projections of the relational tables got merged into one handler to enforce sequential reduction of events. ## Additional info - closes https://github.com/zitadel/zitadel/issues/11703 - closes https://github.com/zitadel/zitadel/issues/10810 --------- Co-authored-by: Marco Ardizzone <[email protected]> |
||
 Wim Van LaerandGitHub
|
9b064d8b74 |
docs: fixed some typos, formatting, grammar (#11540)
# Which Problems Are Solved - Typos - Punctuation - Markdown table formatting - ... (A bunch of issues my editor gave) # How the Problems Are Solved # Additional Changes # Additional Context |
||
|
|
1f0d54a978 |
chore(docs): User (Human) naming consistency (#11512)
# Which Problems Are Solved Naming inconsistencies - User (Human) # How the Problems Are Solved Most of the occurrences have not been changed. `User (Human)` was mostly changed when talking about code objects and where I felt it was necessary to distinguish them from machine users. When both human and machine user occurrences were found, the machine user has been changed to service account (see https://github.com/zitadel/zitadel/issues/11285) # Additional Context - Closes #11284 --------- Co-authored-by: Wim Van Laer <[email protected]> |
||
|
|
11dbb1b277 |
feat(logging): add streams (#11435)
# Which Problems Are Solved Streams allow differentiating logs produced by different components of Zitadel. # How the Problems Are Solved The `backend/v3/instrumentation/logging` package now exposes convenience function for setting and getting a logger from the context. As well as high-level functions to emit log records at various levels. When constructing a new logger a "stream" needs to be specified: - **runtime**: General runtime logs, such as startup and shutdown messages. Default for logs that do not belong to the other categories. - **request**: Logs for incoming API and HTTP requests. - **event_handler**: Logs for event handling in projections. - **queue**: Logs for the job queue processing. - **event_pusher**: Logs for event pushing to the database. Disabled by default, contains sensitive information. Each line from the returned logger contains a `stream` field as well as a `version` field with the current Zitadel version. ## Runtime config Streams can be enabled by passing an array of stream names in the runtime config. Because some log streams may contain sensitive data (especially events), it is now also possible to mask values by their key. # Additional Changes - Wrap `slogctx` in the `logging` package. (Except API error converter packages, because of import cycle) - Add some docs to `logging` package so other devs understand how to add logging to Zitadel - Add `logging.OnError` and `logging.WithError` helper functions with `Panic()` and `Fatal()` methods, to preserve current calls in the `cmd` packages. - Add instance context extractor. - Only output request details in the request info log. Request ID remains propagated through context. - Moved middleware functionality into protocol specific packages. - Removed setting of URI to context in metric middleware. There were ony setters and no getters. (Unused value) - Reuse a single statusWriter in the middleware package for middlewares that need to know the response status. # Additional Context - Closes #11333 - Closes #11331 - Partly #11330 --------- Co-authored-by: Silvan <[email protected]> |
||
|
Tim MöhlmannandGitHub
|
eb22b58756 |
feat(telemetry): improved instrumentation for observability (#11159)
# Which Problems Are Solved Zitadel did not provide easy correlation between errors, logs, traces and metrics. The configuration for those instrumentations was also not consistent, with some supporting different exporters then others. Implementation and parsing of config was also spaghettified over multiple packages, with awkward parsing and inconsistent naming of options. # How the Problems Are Solved All telemetry is now merged under the name "instrumentation". Why? 1. We thought it was a good idea in the past to call the milestone exporter `Telemtry` in the runtime config. Calling this `TelemetryV2` looks weird. 2. Not everything is a meter and not everything is sent (tele...). 3. It's also [defined](https://opentelemetry.io/docs/concepts/instrumentation/) as such by the OTEL documentation. ## New features - Adds structured, context based logging with trace-ID awareness - Static log fields are added to the context, such as service and request path - Static log fields are injected in each logline emitted by the application - Structured logs can also be send to an otel exporter - Structured logs can be printed to StdErr in text and JSON format - Error sinks make sure every error is logged at the correct level: - Warnings for client side errors (HTTP 400 range, Invalid request etc) - Error for server side errors (Internal server errors) - Metrics can now also be send to a OTEL collector. (previously they could only be scraped from `/debug/metrics` with prometheus) ## Exporters This change adds all the exporters supported by OTEL upstream and some google specific exporters for our cloud deployment. - StdOut / StdErr: all instrumentations - OTEL gRPC / HTTP: all instrumentations - Google: all instrumentations except logging - Prometheus (pull-based): only metrics The exception is profiling, which only supports the google exporting due to lack of support by OTEL upstream. ## Configuration and structure - All instrumentation is moved into the new `backend/v3/instrumentation` package. It reuses configuration types, so both code and runtime configuration are easier to understand. - The `internal/telemetry` packages are removed. - Instrumentation is started with a single function and a proper shutdown function is now provided. - Legacy configuration is still parsed from the runtime config, as long as the new configuration is disabled. This allows backporting this feature to v4 without breaking existing configurations. # Additional Changes - Devcontainer: set `$PATH` variable so installed go binaries can be run individually, without NX. - NX: install GCI tool to fix imports # Additional Context - Closes https://github.com/zitadel/zitadel/issues/8408 - Closes https://github.com/zitadel/zitadel/issues/6664 - Backport to v4 |
||
|
SilvanandGitHub
|
345480faeb |
fix(projection): locking behavior based on configuration (#11014)
Ensure projections await running status if configured, improving synchronization during event processing. |
||
|
SilvanandGitHub
|
22ef817d5c |
fix(eventstore): Make Eventstore Compatible with Relational Table Package (#10687)
Improves compatibility of eventstore and related database components with the new relational table package. ## Which problems are solved 1. **Incompatible Database Interfaces**: The existing eventstore was tightly coupled to the database package, which is incompatible with the new, more abstract relational table package in v3. This prevented the new command-side logic from pushing events to the legacy eventstore. 2. **Missing Health Checks**: The database interfaces in the new package lacked a Ping method, making it impossible to perform health checks on database connections. 3. **Event Publishing Logic**: The command handling logic in domain needed a way to collect and push events to the legacy eventstore after a command was successfully executed. ## How the problems are solved 1. **`LegacyEventstore` Interface**: * A new `LegacyEventstore` interface is introduced in the new `database/eventstore` . This interface exposes a `PushWithNewClient` method that accepts the new `database.QueryExecutor` interface, decoupling the v3 domain from the legacy implementation. * The `internal/eventstore.Eventstore` now implements this interface. A wrapper, PushWithClient, is added to convert the old database client types (`*sql.DB`, `*sql.Tx`) into the new `QueryExecutor` types before calling `PushWithNewClient`. 2. **Database Interface Updates**: * The `database.Pool` and `database.Client` interfaces in `storage/eventstore` have been updated to include a Ping method, allowing for consistent health checks across different database dialects. * The `postgres` and `sql` dialect implementations have been updated to support this new method. 3. **Command and Invoker Refactoring**: * The `Commander` interface in domain now includes an `Events() []legacy_es.Command` method. This allows commands to declare which events they will generate. * The `eventCollector` in the invoker logic has been redesigned. It now ensures a database transaction is started before executing a command. After successful execution, it calls the `Events()` method on the command to collect the generated events and appends them to a list. * The `eventStoreInvoker` then pushes all collected events to the legacy eventstore using the new `LegacyEventstore` interface, ensuring that events are only pushed if the entire command (and any sub-commands) executes successfully within the transaction. 4. **Testing**: * New unit tests have been added for the invoker to verify that events are correctly collected from single commands, batched commands, and nested commands. These changes create a clean bridge between the new v3 command-side logic and the existing v1 eventstore, allowing for incremental adoption of the new architecture while maintaining full functionality. ## Additional Information closes https://github.com/zitadel/zitadel/issues/10442 |
||
 Livio SpringandGitHub
|
ee92560f32 |
fix(projections): handle reduce error by updating failed events (#10726)
# Which Problems Are Solved I noticed that a failure in the projections handlers `reduce` function (e.g. creating the statement or checking preconditions for the statement) would not update the `failed_events2` table. This was due to a wrong error handling, where as long as the `maxFailureCount` was not reached, the error was returned after updating the `failed_events2` table, which causes the transaction to be rolled back and thus losing the update. # How the Problems Are Solved Wrap the error into an `executionError`, so the transaction is not rolled back. # Additional Changes none # Additional Context - noticed internally - requires backport to v3.x and v4.x |
||
|
SilvanandGitHub
|
25ab6b2397 |
fix(projection): prevent skipped events written within the same microsecond (#10710)
This PR fixes a bug where projections could skip events if they were written within the same microsecond, which can occur during high load on different transactions. ## Problem The event query ordering was not fully deterministic. Events created at the exact same time (same `position`) and in the same transaction (`in_tx_order`) were not guaranteed to be returned in the same order on subsequent queries. This could lead to some events being skipped by the projection logic. ## Solution To solve this, the `ORDER BY` clause for event queries has been extended to include `instance_id`, `aggregate_type`, and `aggregate_id`. This ensures a stable and deterministic ordering for all events, even if they share the same timestamp. ## Additionally changes: * Replaced a manual slice search with the more idiomatic `slices.Contains` to skip already projected instances. * Changed the handling of already locked projections to log a debug message and skip execution instead of returning an error. * Ensures the database transaction is explicitly committed. |
||
|
Livio SpringandGitHub
|
79809d0199 |
fix(actions v2): send event_payload on event executions again (#10651)
# Which Problems Are Solved
It was noticed that on actions v2 when subscribing to events, the
webhook would always receive an empty `event_payload`:
```
{
"aggregateID": "336494809936035843",
"aggregateType": "user",
"resourceOwner": "336392597046099971",
"instanceID": "336392597046034435",
"version": "v2",
"sequence": 1,
"event_type": "user.human.added",
"created_at": "2025-09-05T08:55:36.156333Z",
"userID": "336392597046755331",
"event_payload":
{}
}
```
The problem was due to using `json.Marshal` on the `Event` interface,
where the underlying `BaseEvent` prevents the data to be marshalled:
https://github.com/zitadel/zitadel/blob/131f70db3423b80da1b038a822da59c908e4ffa6/internal/eventstore/event_base.go#L38
# How the Problems Are Solved
The `Event`s `Unmarshal` function is used with a `json.RawMessage`.
# Additional Changes
none
# Additional Context
- closes https://github.com/zitadel/zitadel/issues/10650
- requires backport to v4.x
|
||
|
|
0575f67e94 |
fix(projections): overhaul the event projection system (#10560)
This PR overhauls our event projection system to make it more robust and prevent skipped events under high load. The core change replaces our custom, transaction-based locking with standard PostgreSQL advisory locks. We also introduce a worker pool to manage concurrency and prevent database connection exhaustion. ### Key Changes * **Advisory Locks for Projections:** Replaces exclusive row locks and inspection of `pg_stat_activity` with PostgreSQL advisory locks for managing projection state. This is a more reliable and standard approach to distributed locking. * **Simplified Await Logic:** Removes the complex logic for awaiting open transactions, simplifying it to a more straightforward time-based filtering of events. * **Projection Worker Pool:** Implements a worker pool to limit concurrent projection triggers, preventing connection exhaustion and improving stability under load. A new `MaxParallelTriggers` configuration option is introduced. ### Problem Solved Under high throughput, a race condition could cause projections to miss events from the eventstore. This led to inconsistent data in projection tables (e.g., a user grant might be missing). This PR fixes the underlying locking and concurrency issues to ensure all events are processed reliably. ### How it Works 1. **Event Writing:** When writing events, a *shared* advisory lock is taken. This signals that a write is in progress. 2. **Event Handling (Projections):** * A projection worker attempts to acquire an *exclusive* advisory lock for that specific projection. If the lock is already held, it means another worker is on the job, so the current one backs off. * Once the lock is acquired, the worker briefly acquires and releases the same *shared* lock used by event writers. This acts as a barrier, ensuring it waits for any in-flight writes to complete. * Finally, it processes all events that occurred before its transaction began. ### Additional Information * ZITADEL no longer modifies the `application_name` PostgreSQL variable during event writes. * The lock on the `current_states` table is now `FOR NO KEY UPDATE`. * Fixes https://github.com/zitadel/zitadel/issues/8509 --------- Co-authored-by: Copilot <[email protected]> Co-authored-by: Tim Möhlmann <[email protected]> |
||
|
a9ebc06c77 |
perf(actionsv2): execution target router (#10564)
# Which Problems Are Solved The event execution system currently uses a projection handler that subscribes to and processes all events for all instances. This creates a high static cost because the system over-fetches event data, handling many events that are not needed by most instances. This inefficiency is also reflected in high "rows returned" metrics in the database. # How the Problems Are Solved Eliminate the use of a project handler. Instead, events for which "execution targets" are defined, are directly pushed to the queue by the eventstore. A Router is populated in the Instance object in the authz middleware. - By joining the execution targets to the instance, no additional queries are needed anymore. - As part of the instance object, execution targets are now cached as well. - Events are queued within the same transaction, giving transactional guarantees on delivery. - Uses the "insert many fast` variant of River. Multiple jobs are queued in a single round-trip to the database. - Fix compatibility with PostgreSQL 15 # Additional Changes - The signing key was stored as plain-text in the river job payload in the DB. This violated our [Secrets Storage](https://zitadel.com/docs/concepts/architecture/secrets#secrets-storage) principle. This change removed the field and only uses the encrypted version of the signing key. - Fixed the target ordering from descending to ascending. - Some minor linter warnings on the use of `io.WriteString()`. # Additional Context - Introduced in https://github.com/zitadel/zitadel/pull/9249 - Closes https://github.com/zitadel/zitadel/issues/10553 - Closes https://github.com/zitadel/zitadel/issues/9832 - Closes https://github.com/zitadel/zitadel/issues/10372 - Closes https://github.com/zitadel/zitadel/issues/10492 --------- Co-authored-by: Stefan Benz <[email protected]> |
||
|
Livio SpringandGitHub
|
67efddcbc6 |
fix: allow disabling projections for specific instances (#10421)
# Which Problems Are Solved The current handling of event subscriptions for actions is bad, esp. on instances with a lot of events (https://github.com/zitadel/zitadel/issues/9832#issuecomment-2866236414). This led to severe problems on zitadel.cloud for such instances. # How the Problems Are Solved As a workaround until the handling can be improved, we introduce an option for projections to be disabled completely for specific instances: `SkipInstanceIDs` # Additional Changes None # Additional Context - relates to https://github.com/zitadel/zitadel/issues/9832 |
||
|
SilvanandGitHub
|
20e7807ee5 |
fix(projections): pass context to statement execution method (#10328)
## Which problems are solved The execution of statements of projections did not have the context present. ## How the problems were solved Pass the context to the execute function ## Additional info This change is required to use the repositories of the relational tables in projections. |
||
|
8fc11a7366 |
feat: user api requests to resource API (#9794)
# Which Problems Are Solved This pull request addresses a significant gap in the user service v2 API, which currently lacks methods for managing machine users. # How the Problems Are Solved This PR adds new API endpoints to the user service v2 to manage machine users including their secret, keys and personal access tokens. Additionally, there's now a CreateUser and UpdateUser endpoints which allow to create either a human or machine user and update them. The existing `CreateHumanUser` endpoint has been deprecated along the corresponding management service endpoints. For details check the additional context section. # Additional Context - Closes https://github.com/zitadel/zitadel/issues/9349 ## More details - API changes: https://github.com/zitadel/zitadel/pull/9680 - Implementation: https://github.com/zitadel/zitadel/pull/9763 - Tests: https://github.com/zitadel/zitadel/pull/9771 ## Follow-ups - Metadata: support managing user metadata using resource API https://github.com/zitadel/zitadel/pull/10005 - Machine token type: support managing the machine token type (migrate to new enum with zero value unspecified?) --------- Co-authored-by: Copilot <[email protected]> Co-authored-by: Livio Spring <[email protected]> |
||
|
|
131f70db34 |
fix(eventstore): use decimal, correct mirror (#9914)
# Eventstore fixes - `event.Position` used float64 before which can lead to [precision loss](https://github.com/golang/go/issues/47300). The type got replaced by [a type without precision loss](https://github.com/jackc/pgx-shopspring-decimal) - the handler reported the wrong error if the current state was updated and therefore took longer to retry failed events. # Mirror fixes - max age of auth requests can be configured to speed up copying data from `auth.auth_requests` table. Auth requests last updated before the set age will be ignored. Default is 1 month - notification projections are skipped because notifications should be sent by the source system. The projections are set to the latest position - ensure that mirror can be executed multiple times --------- Co-authored-by: Livio Spring <[email protected]> |
||
|
SilvanandGitHub
|
60ce32ca4f |
fix(setup): reenable index creation (#9868)
# Which Problems Are Solved We saw high CPU usage if many events were created on the database. This was caused by the new actions which query for all event types and aggregate types. # How the Problems Are Solved - the handler of action execution does not filter for aggregate and event types. - the index for `instance_id` and `position` is reenabled. # Additional Changes none # Additional Context none |
||
+6    
|
07ce3b6905 |
chore!: Introduce ZITADEL v3 (#9645)
This PR summarizes multiple changes specifically only available with ZITADEL v3: - feat: Web Keys management (https://github.com/zitadel/zitadel/pull/9526) - fix(cmd): ensure proper working of mirror (https://github.com/zitadel/zitadel/pull/9509) - feat(Authz): system user support for permission check v2 (https://github.com/zitadel/zitadel/pull/9640) - chore(license): change from Apache to AGPL (https://github.com/zitadel/zitadel/pull/9597) - feat(console): list v2 sessions (https://github.com/zitadel/zitadel/pull/9539) - fix(console): add loginV2 feature flag (https://github.com/zitadel/zitadel/pull/9682) - fix(feature flags): allow reading "own" flags (https://github.com/zitadel/zitadel/pull/9649) - feat(console): add Actions V2 UI (https://github.com/zitadel/zitadel/pull/9591) BREAKING CHANGE - feat(webkey): migrate to v2beta API (https://github.com/zitadel/zitadel/pull/9445) - chore!: remove CockroachDB Support (https://github.com/zitadel/zitadel/pull/9444) - feat(actions): migrate to v2beta API (https://github.com/zitadel/zitadel/pull/9489) --------- Co-authored-by: Livio Spring <[email protected]> Co-authored-by: Stefan Benz <[email protected]> Co-authored-by: Silvan <[email protected]> Co-authored-by: Ramon <[email protected]> Co-authored-by: Elio Bischof <[email protected]> Co-authored-by: Kenta Yamaguchi <[email protected]> Co-authored-by: Harsha Reddy <[email protected]> Co-authored-by: Livio Spring <[email protected]> Co-authored-by: Max Peintner <[email protected]> Co-authored-by: Iraq <[email protected]> Co-authored-by: Florian Forster <[email protected]> Co-authored-by: Tim Möhlmann <[email protected]> Co-authored-by: Copilot <[email protected]> Co-authored-by: Max Peintner <[email protected]> |
||
|
|
817670f1f7 |
perf: improve scalability of session api (#9635)
This pull request improves the scalability of the session API by enhancing middleware tracing and refining SQL query behavior for user authentication methods. # Which Problems Are Solved - Eventstore subscriptions locked each other during they wrote the events to the event channels of the subscribers in push. - `ListUserAuthMethodTypesRequired` query used `Bitmap heap scan` to join the tables needed. - The auth and oidc package triggered projections often when data were read. - The session API triggered the user projection each time a user was searched to write the user check command. # How the Problems Are Solved - the `sync.Mutex` was replaced with `sync.RWMutex` to allow parallel read of the map - The query was refactored to use index scans only - if the data should already be up-to-date `shouldTriggerBulk` is set to false - as the user should already exist for some time the trigger was removed. # Additional Changes - refactoring of `tracing#Span.End` calls # Additional Context - part of https://github.com/zitadel/zitadel/issues/9239 --------- Co-authored-by: Tim Möhlmann <[email protected]> |
||
|
c1535b7b49 |
feat: add prometheus metrics on projection handlers (#9561)
# Which Problems Are Solved With current provided telemetry it's difficult to predict when a projection handler is under increased load until it's too late and causes downstream issues. Importantly, projection updating is in the critical path for many login flows and increased latency there can result in system downtime for users. # How the Problems Are Solved This PR adds three new prometheus-style metrics: 1. **projection_events_processed** (_labels: projection, success_) - This metric gives us a counter of the number of events processed per projection update run and whether they we're processed without error. A high number of events being processed can let us know how busy a particular projection handler is. 2. **projection_handle_timer** _(labels: projection)_ - This is the time it takes to process a projection update given a batch of events - time to take the current_states lock, query for new events, reduce, update_the projection, and update current_states. 3. **projection_state_latency** _(labels: projection)_ - This is the time from the last event processed in the current_states table for a given projection. It tells us how old was the last event you processed? Or, how far behind are you running for this projection? Higher latencies could mean high load or stalled projection handling. # Additional Changes I also had to initialize the global otel metrics provider (`metrics.M`) in the `setup` step additionally to `start` since projection handlers are initialized at setup. The initialization checks if a metrics provider is already set (in case of `start-from-setup` or `start-from-init` to prevent overwriting, which causes the otel metrics provider to stop working. # Additional Context ## Example Dashboards   --------- Co-authored-by: Silvan <[email protected]> Co-authored-by: Livio Spring <[email protected]> |
||
|
SilvanandGitHub
|
e36f402e09 |
fix(perf): simplify eventstore queries by removing or in projection handlers (#9530)
# Which Problems Are Solved [A recent performance enhancement]((https://github.com/zitadel/zitadel/pull/9497)) aimed at optimizing event store queries, specifically those involving multiple aggregate type filters, has successfully improved index utilization. While the query planner now correctly selects relevant indexes, it employs [bitmap index scans](https://www.postgresql.org/docs/current/indexes-bitmap-scans.html) to retrieve data. This approach, while beneficial in many scenarios, introduces a potential I/O bottleneck. The bitmap index scan first identifies the required database blocks and then utilizes a bitmap to access the corresponding rows from the table's heap. This subsequent "bitmap heap scan" can result in significant I/O overhead, particularly when queries return a substantial number of rows across numerous data pages. ## Impact: Under heavy load or with queries filtering for a wide range of events across multiple aggregate types, this increased I/O activity may lead to: - Increased query latency. - Elevated disk utilization. - Potential performance degradation of the event store and dependent systems. # How the Problems Are Solved To address this I/O bottleneck and further optimize query performance, the projection handler has been modified. Instead of employing multiple OR clauses for each aggregate type, the aggregate and event type filters are now combined using IN ARRAY filters. Technical Details: This change allows the PostgreSQL query planner to leverage [index-only scans](https://www.postgresql.org/docs/current/indexes-index-only-scans.html). By utilizing IN ARRAY filters, the database can efficiently retrieve the necessary data directly from the index, eliminating the need to access the table's heap. This results in: * Reduced I/O: Index-only scans significantly minimize disk I/O operations, as the database avoids reading data pages from the main table. * Improved Query Performance: By reducing I/O, query execution times are substantially improved, leading to lower latency. # Additional Changes - rollback of https://github.com/zitadel/zitadel/pull/9497 # Additional Information ## Query Plan of previous query ```sql SELECT created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision FROM eventstore.events2 WHERE instance_id = '<INSTANCE_ID>' AND ( ( instance_id = '<INSTANCE_ID>' AND "position" > <POSITION> AND aggregate_type = 'project' AND event_type = ANY(ARRAY[ 'project.application.added' ,'project.application.changed' ,'project.application.deactivated' ,'project.application.reactivated' ,'project.application.removed' ,'project.removed' ,'project.application.config.api.added' ,'project.application.config.api.changed' ,'project.application.config.api.secret.changed' ,'project.application.config.api.secret.updated' ,'project.application.config.oidc.added' ,'project.application.config.oidc.changed' ,'project.application.config.oidc.secret.changed' ,'project.application.config.oidc.secret.updated' ,'project.application.config.saml.added' ,'project.application.config.saml.changed' ]) ) OR ( instance_id = '<INSTANCE_ID>' AND "position" > <POSITION> AND aggregate_type = 'org' AND event_type = 'org.removed' ) OR ( instance_id = '<INSTANCE_ID>' AND "position" > <POSITION> AND aggregate_type = 'instance' AND event_type = 'instance.removed' ) ) AND "position" > 1741600905.3495 AND "position" < ( SELECT COALESCE(EXTRACT(EPOCH FROM min(xact_start)), EXTRACT(EPOCH FROM now())) FROM pg_stat_activity WHERE datname = current_database() AND application_name = ANY(ARRAY['zitadel_es_pusher_', 'zitadel_es_pusher', 'zitadel_es_pusher_<INSTANCE_ID>']) AND state <> 'idle' ) ORDER BY "position", in_tx_order LIMIT 200 OFFSET 1; ``` ``` Limit (cost=120.08..120.09 rows=7 width=361) (actual time=2.167..2.172 rows=0 loops=1) Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order InitPlan 1 -> Aggregate (cost=2.74..2.76 rows=1 width=32) (actual time=1.813..1.815 rows=1 loops=1) Output: COALESCE(EXTRACT(epoch FROM min(s.xact_start)), EXTRACT(epoch FROM now())) -> Nested Loop (cost=0.00..2.74 rows=1 width=8) (actual time=1.803..1.805 rows=0 loops=1) Output: s.xact_start Join Filter: (d.oid = s.datid) -> Seq Scan on pg_catalog.pg_database d (cost=0.00..1.07 rows=1 width=4) (actual time=0.016..0.021 rows=1 loops=1) Output: d.oid, d.datname, d.datdba, d.encoding, d.datlocprovider, d.datistemplate, d.datallowconn, d.dathasloginevt, d.datconnlimit, d.datfrozenxid, d.datminmxid, d.dattablespace, d.datcollate, d.datctype, d.datlocale, d.daticurules, d.datcollversion, d.datacl Filter: (d.datname = current_database()) Rows Removed by Filter: 4 -> Function Scan on pg_catalog.pg_stat_get_activity s (cost=0.00..1.63 rows=3 width=16) (actual time=1.781..1.781 rows=0 loops=1) Output: s.datid, s.pid, s.usesysid, s.application_name, s.state, s.query, s.wait_event_type, s.wait_event, s.xact_start, s.query_start, s.backend_start, s.state_change, s.client_addr, s.client_hostname, s.client_port, s.backend_xid, s.backend_xmin, s.backend_type, s.ssl, s.sslversion, s.sslcipher, s.sslbits, s.ssl_client_dn, s.ssl_client_serial, s.ssl_issuer_dn, s.gss_auth, s.gss_princ, s.gss_enc, s.gss_delegation, s.leader_pid, s.query_id Function Call: pg_stat_get_activity(NULL::integer) Filter: ((s.state <> 'idle'::text) AND (s.application_name = ANY ('{zitadel_es_pusher_,zitadel_es_pusher,zitadel_es_pusher_<INSTANCE_ID>}'::text[]))) Rows Removed by Filter: 49 -> Sort (cost=117.31..117.33 rows=8 width=361) (actual time=2.167..2.168 rows=0 loops=1) Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order Sort Key: events2."position", events2.in_tx_order Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on eventstore.events2 (cost=84.92..117.19 rows=8 width=361) (actual time=2.088..2.089 rows=0 loops=1) Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order Recheck Cond: (((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'project'::text) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) OR ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'org'::text) AND (events2.event_type = 'org.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) OR ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'instance'::text) AND (events2.event_type = 'instance.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1))) -> BitmapOr (cost=84.88..84.88 rows=8 width=0) (actual time=2.080..2.081 rows=0 loops=1) -> Bitmap Index Scan on es_projection (cost=0.00..75.44 rows=8 width=0) (actual time=2.016..2.017 rows=0 loops=1) Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'project'::text) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) -> Bitmap Index Scan on es_projection (cost=0.00..4.71 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=1) Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'org'::text) AND (events2.event_type = 'org.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) -> Bitmap Index Scan on es_projection (cost=0.00..4.71 rows=1 width=0) (actual time=0.045..0.045 rows=0 loops=1) Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = 'instance'::text) AND (events2.event_type = 'instance.removed'::text) AND (events2."position" > <POSITION>) AND (events2."position" > 1741600905.3495) AND (events2."position" < (InitPlan 1).col1)) Query Identifier: 3194938266011254479 Planning Time: 1.295 ms Execution Time: 2.832 ms ``` ## Query Plan of new query ```sql SELECT created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision FROM eventstore.events2 WHERE instance_id = '<INSTANCE_ID>' AND "position" > <POSITION> AND aggregate_type = ANY(ARRAY['project', 'instance', 'org']) AND event_type = ANY(ARRAY[ 'project.application.added' ,'project.application.changed' ,'project.application.deactivated' ,'project.application.reactivated' ,'project.application.removed' ,'project.removed' ,'project.application.config.api.added' ,'project.application.config.api.changed' ,'project.application.config.api.secret.changed' ,'project.application.config.api.secret.updated' ,'project.application.config.oidc.added' ,'project.application.config.oidc.changed' ,'project.application.config.oidc.secret.changed' ,'project.application.config.oidc.secret.updated' ,'project.application.config.saml.added' ,'project.application.config.saml.changed' ,'org.removed' ,'instance.removed' ]) AND "position" < ( SELECT COALESCE(EXTRACT(EPOCH FROM min(xact_start)), EXTRACT(EPOCH FROM now())) FROM pg_stat_activity WHERE datname = current_database() AND application_name = ANY(ARRAY['zitadel_es_pusher_', 'zitadel_es_pusher', 'zitadel_es_pusher_<INSTANCE_ID>']) AND state <> 'idle' ) ORDER BY "position", in_tx_order LIMIT 200 OFFSET 1; ``` ``` Limit (cost=293.34..293.36 rows=8 width=361) (actual time=4.686..4.689 rows=0 loops=1) Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order InitPlan 1 -> Aggregate (cost=2.74..2.76 rows=1 width=32) (actual time=1.717..1.719 rows=1 loops=1) Output: COALESCE(EXTRACT(epoch FROM min(s.xact_start)), EXTRACT(epoch FROM now())) -> Nested Loop (cost=0.00..2.74 rows=1 width=8) (actual time=1.658..1.659 rows=0 loops=1) Output: s.xact_start Join Filter: (d.oid = s.datid) -> Seq Scan on pg_catalog.pg_database d (cost=0.00..1.07 rows=1 width=4) (actual time=0.026..0.028 rows=1 loops=1) Output: d.oid, d.datname, d.datdba, d.encoding, d.datlocprovider, d.datistemplate, d.datallowconn, d.dathasloginevt, d.datconnlimit, d.datfrozenxid, d.datminmxid, d.dattablespace, d.datcollate, d.datctype, d.datlocale, d.daticurules, d.datcollversion, d.datacl Filter: (d.datname = current_database()) Rows Removed by Filter: 4 -> Function Scan on pg_catalog.pg_stat_get_activity s (cost=0.00..1.63 rows=3 width=16) (actual time=1.628..1.628 rows=0 loops=1) Output: s.datid, s.pid, s.usesysid, s.application_name, s.state, s.query, s.wait_event_type, s.wait_event, s.xact_start, s.query_start, s.backend_start, s.state_change, s.client_addr, s.client_hostname, s.client_port, s.backend_xid, s.backend_xmin, s.backend_type, s.ssl, s.sslversion, s.sslcipher, s.sslbits, s.ssl_client_dn, s.ssl_client_serial, s.ssl_issuer_dn, s.gss_auth, s.gss_princ, s.gss_enc, s.gss_delegation, s.leader_pid, s.query_id Function Call: pg_stat_get_activity(NULL::integer) Filter: ((s.state <> 'idle'::text) AND (s.application_name = ANY ('{zitadel_es_pusher_,zitadel_es_pusher,zitadel_es_pusher_<INSTANCE_ID>}'::text[]))) Rows Removed by Filter: 42 -> Sort (cost=290.58..290.60 rows=9 width=361) (actual time=4.685..4.685 rows=0 loops=1) Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order Sort Key: events2."position", events2.in_tx_order Sort Method: quicksort Memory: 25kB -> Index Scan using es_projection on eventstore.events2 (cost=0.70..290.43 rows=9 width=361) (actual time=4.616..4.617 rows=0 loops=1) Output: events2.created_at, events2.event_type, events2.sequence, events2."position", events2.payload, events2.creator, events2.owner, events2.instance_id, events2.aggregate_type, events2.aggregate_id, events2.revision, events2.in_tx_order Index Cond: ((events2.instance_id = '<INSTANCE_ID>'::text) AND (events2.aggregate_type = ANY ('{project,instance,org}'::text[])) AND (events2.event_type = ANY ('{project.application.added,project.application.changed,project.application.deactivated,project.application.reactivated,project.application.removed,project.removed,project.application.config.api.added,project.application.config.api.changed,project.application.config.api.secret.changed,project.application.config.api.secret.updated,project.application.config.oidc.added,project.application.config.oidc.changed,project.application.config.oidc.secret.changed,project.application.config.oidc.secret.updated,project.application.config.saml.added,project.application.config.saml.changed,org.removed,instance.removed}'::text[])) AND (events2."position" > <POSITION>) AND (events2."position" < (InitPlan 1).col1)) Query Identifier: -8254550537132386499 Planning Time: 2.864 ms Execution Time: 5.414 ms ``` |
||
|
SilvanandGitHub
|
b578137139 | fix(eventstore): optimise query hints for event filters (#9497) | ||
|
SilvanandGitHub
|
444f682e25 |
refactor(notification): use new queue package (#9360)
# Which Problems Are Solved The recently introduced notification queue have potential race conditions. # How the Problems Are Solved Current code is refactored to use the queue package, which is safe in regards of concurrency. # Additional Changes - the queue is included in startup - improved code quality of queue # Additional Context - closes https://github.com/zitadel/zitadel/issues/9278 |
||
|
|
e670b9126c |
fix(permissions): chunked synchronization of role permission events (#9403)
# Which Problems Are Solved Setup fails to push all role permission events when running Zitadel with CockroachDB. `TransactionRetryError`s were visible in logs which finally times out the setup job with `timeout: context deadline exceeded` # How the Problems Are Solved As suggested in the [Cockroach documentation](timeout: context deadline exceeded), _"break down larger transactions"_. The commands to be pushed for the role permissions are chunked in 50 events per push. This chunking is only done with CockroachDB. # Additional Changes - gci run fixed some unrelated imports - access to `command.Commands` for the setup job, so we can reuse the sync logic. # Additional Context Closes #9293 --------- Co-authored-by: Silvan <[email protected]> |
||
|
|
911200aa9b |
feat(api): allow Device Authorization Grant using custom login UI (#9387)
# Which Problems Are Solved The OAuth2 Device Authorization Grant could not yet been handled through the new login UI, resp. using the session API. This PR adds the ability for the login UI to get the required information to display the user and handle their decision (approve with authorization or deny) using the OIDC Service API. # How the Problems Are Solved - Added a `GetDeviceAuthorizationRequest` endpoint, which allows getting the `id`, `client_id`, `scope`, `app_name` and `project_name` of the device authorization request - Added a `AuthorizeOrDenyDeviceAuthorization` endpoint, which allows to approve/authorize with the session information or deny the request. The identification of the request is done by the `device_authorization_id` / `id` returned in the previous request. - To prevent leaking the `device_code` to the UI, but still having an easy reference, it's encrypted and returned as `id`, resp. decrypted when used. - Fixed returned error types for device token responses on token endpoint: - Explicitly return `access_denied` (without internal error) when user denied the request - Default to `invalid_grant` instead of `access_denied` - Explicitly check on initial state when approving the reqeust - Properly handle done case (also relates to initial check) - Documented the flow and handling in custom UIs (according to OIDC / SAML) # Additional Changes - fixed some typos and punctuation in the corresponding OIDC / SAML guides. - added some missing translations for auth and saml request # Additional Context - closes #6239 --------- Co-authored-by: Tim Möhlmann <[email protected]> |
||
|
Tim MöhlmannandGitHub
|
94cbf97534 |
fix(permissions_v2): add membership fields migration (#9199)
# Which Problems Are Solved Memberships did not have a fields table fill migration. # How the Problems Are Solved Add filling of membership fields to the repeatable steps. # Additional Changes - Use the same repeatable step for multiple fill fields handlers. - Fix an error for PostgreSQL 15 where a subquery in a `FROM` clause needs an alias ing the `permitted_orgs` function. # Additional Context - Part of https://github.com/zitadel/zitadel/issues/9188 - Introduced in https://github.com/zitadel/zitadel/pull/9152 |
||
|
SilvanandGitHub
|
4645045987 |
refactor: consolidate database pools (#9105)
# Which Problems Are Solved Zitadel currently uses 3 database pool, 1 for queries, 1 for pushing events and 1 for scheduled projection updates. This defeats the purpose of a connection pool which already handles multiple connections. During load tests we found that the current structure of connection pools consumes a lot of database resources. The resource usage dropped after we reduced the amount of database pools to 1 because existing connections can be used more efficiently. # How the Problems Are Solved Removed logic to handle multiple connection pools and use a single one. # Additional Changes none # Additional Context part of https://github.com/zitadel/zitadel/issues/8352 |
||
|
SilvanandGitHub
|
1949d1546a |
fix: set correct owner on project grants (#9089)
# Which Problems Are Solved In versions previous to v2.66 it was possible to set a different resource owner on project grants. This was introduced with the new resource based API. The resource owner was possible to overwrite using the x-zitadel-org header. Because of this issue project grants got the wrong resource owner, instead of the owner of the project it got the granted org which is wrong because a resource owner of an aggregate is not allowed to change. # How the Problems Are Solved - The wrong owners of the events are set to the original owner of the project. - A new event is pushed to these aggregates `project.owner.corrected` - The projection updates the owners of the user grants if that event was written # Additional Changes The eventstore push function (replaced in version 2.66) writes the correct resource owner. # Additional Context closes https://github.com/zitadel/zitadel/issues/9072 |
||
 SilvanandGitHub
|
77cd430b3a |
refactor(handler): cache active instances (#9008)
# Which Problems Are Solved Scheduled handlers use `eventstore.InstanceIDs` to get the all active instances within a given timeframe. This function scrapes through all events written within that time frame which can cause heavy load on the database. # How the Problems Are Solved A new query cache `activeInstances` is introduced which caches the ids of all instances queried by id or host within the configured timeframe. # Additional Changes - Changed `default.yaml` - Removed `HandleActiveInstances` from custom handler configs - Added `MaxActiveInstances` to define the maximal amount of cached instance ids - fixed start-from-init and start-from-setup to start auth and admin projections twice - fixed org cache invalidation to use correct index # Additional Context - part of #8999 |
||
|
|
a81d42a61a |
fix(eventstore): set created filters to exclusion sub-query (#9019)
# Which Problems Are Solved In eventstore queries with aggregate ID exclusion filters, filters on events creation date where not passed to the sub-query. This results in a high amount of returned rows from the sub-query and high overall query cost. # How the Problems Are Solved When CreatedAfter and CreatedBefore are used on the global search query, copy those filters to the sub-query. We already did this for the position column filter. # Additional Changes - none # Additional Context - Introduced in https://github.com/zitadel/zitadel/pull/8940 Co-authored-by: Livio Spring <[email protected]> |
||
+3   
|
dab5d9e756 |
refactor(eventstore): move push logic to sql (#8816)
# Which Problems Are Solved If many events are written to the same aggregate id it can happen that zitadel [starts to retry the push transaction](https://github.com/zitadel/zitadel/blob/48ffc902cc90237d693e7104fc742ee927478da7/internal/eventstore/eventstore.go#L101) because [the locking behaviour](https://github.com/zitadel/zitadel/blob/48ffc902cc90237d693e7104fc742ee927478da7/internal/eventstore/v3/sequence.go#L25) during push does compute the wrong sequence because newly committed events are not visible to the transaction. These events impact the current sequence. In cases with high command traffic on a single aggregate id this can have severe impact on general performance of zitadel. Because many connections of the `eventstore pusher` database pool are blocked by each other. # How the Problems Are Solved To improve the performance this locking mechanism was removed and the business logic of push is moved to sql functions which reduce network traffic and can be analyzed by the database before the actual push. For clients of the eventstore framework nothing changed. # Additional Changes - after a connection is established prefetches the newly added database types - `eventstore.BaseEvent` now returns the correct revision of the event # Additional Context - part of https://github.com/zitadel/zitadel/issues/8931 --------- Co-authored-by: Tim Möhlmann <[email protected]> Co-authored-by: Livio Spring <[email protected]> Co-authored-by: Max Peintner <[email protected]> Co-authored-by: Elio Bischof <[email protected]> Co-authored-by: Stefan Benz <[email protected]> Co-authored-by: Miguel Cabrerizo <[email protected]> Co-authored-by: Joakim Lodén <[email protected]> Co-authored-by: Yxnt <[email protected]> Co-authored-by: Stefan Benz <[email protected]> Co-authored-by: Harsha Reddy <[email protected]> Co-authored-by: Zach H <[email protected]> |
||
|
|
ff70ede7c7 |
feat(eventstore): exclude aggregate IDs when event_type occurred (#8940)
# Which Problems Are Solved
For truly event-based notification handler, we need to be able to filter
out events of aggregates which are already handled. For example when an
event like `notify.success` or `notify.failed` was created on an
aggregate, we no longer require events from that aggregate ID.
# How the Problems Are Solved
Extend the query builder to use a `NOT IN` clause which excludes
aggregate IDs when they have certain events for a certain aggregate
type. For optimization and proper index usages, certain filters are
inherited from the parent query, such as:
- Instance ID
- Instance IDs
- Position offset
This is a prettified query as used by the unit tests:
```sql
SELECT created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision
FROM eventstore.events2
WHERE instance_id = $1
AND aggregate_type = $2
AND event_type = $3
AND "position" > $4
AND aggregate_id NOT IN (
SELECT aggregate_id
FROM eventstore.events2
WHERE aggregate_type = $5
AND event_type = ANY($6)
AND instance_id = $7
AND "position" > $8
)
ORDER BY "position" DESC, in_tx_order DESC
LIMIT $9
```
I used this query to run it against the `oidc_session` aggregate looking
for added events, excluding aggregates where a token was revoked,
against a recent position. It fully used index scans:
<details>
```json
[
{
"Plan": {
"Node Type": "Index Scan",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "es_projection",
"Relation Name": "events2",
"Alias": "events2",
"Actual Rows": 2,
"Actual Loops": 1,
"Index Cond": "((instance_id = '286399006995644420'::text) AND (aggregate_type = 'oidc_session'::text) AND (event_type = 'oidc_session.added'::text) AND (\"position\" > 1731582100.784168))",
"Rows Removed by Index Recheck": 0,
"Filter": "(NOT (hashed SubPlan 1))",
"Rows Removed by Filter": 1,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "SubPlan",
"Subplan Name": "SubPlan 1",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "es_projection",
"Relation Name": "events2",
"Alias": "events2_1",
"Actual Rows": 1,
"Actual Loops": 1,
"Index Cond": "((instance_id = '286399006995644420'::text) AND (aggregate_type = 'oidc_session'::text) AND (event_type = 'oidc_session.access_token.revoked'::text) AND (\"position\" > 1731582100.784168))",
"Rows Removed by Index Recheck": 0
}
]
},
"Triggers": [
]
}
]
```
</details>
# Additional Changes
- None
# Additional Context
- Related to https://github.com/zitadel/zitadel/issues/8931
---------
Co-authored-by: adlerhurst <[email protected]>
|
||
|
SilvanandGitHub
|
7714af6f5b |
fix(eventstore): correct database type in PushWithClient (#8949)
# Which Problems Are Solved `eventstore.PushWithClient` required the wrong type of for the client parameter. # How the Problems Are Solved Changed type of client from `database.Client` to `database.QueryExecutor` |
||
|
|
1ee7a1ab7c |
feat(eventstore): accept transaction in push (#8945)
# Which Problems Are Solved Push is not capable of external transactions. # How the Problems Are Solved A new function `PushWithClient` is added to the eventstore framework which allows to pass a client which can either be a `*sql.Client` or `*sql.Tx` and is used during push. # Additional Changes Added interfaces to database package. # Additional Context - part of https://github.com/zitadel/zitadel/issues/8931 --------- Co-authored-by: Livio Spring <[email protected]> Co-authored-by: Copilot <[email protected]> |
||
|
Tim MöhlmannandGitHub
|
d4389ab359 |
feat(eventstore): add row locking option (#8939)
# Which Problems Are Solved We need a reliable way to lock events that are being processed as part of a job queue. For example in the notification handlers. # How the Problems Are Solved Allow setting `FOR UPDATE [ NOWAIT | SKIP LOCKED ]` to the eventstore query builder using an open transaction. - NOWAIT returns an errors if the lock cannot be obtained - SKIP LOCKED only returns row which are not locked. - Default is to wait for the lock to be released. # Additional Changes - none # Additional Context - [Locking docs](https://www.postgresql.org/docs/17/sql-select.html#SQL-FOR-UPDATE-SHARE) - Related to https://github.com/zitadel/zitadel/issues/8931 |
||
|
SilvanandGitHub
|
522c82876f |
fix(eventstore): set application name during push to instance id (#8918)
# Which Problems Are Solved Noisy neighbours can introduce projection latencies because the projections only query events older than the start timestamp of the oldest push transaction. # How the Problems Are Solved During push we set the application name to `zitadel_es_pusher_<instance_id>` instead of `zitadel_es_pusher` which is used to query events by projections. |
||
|
+3
|
9c3e5e467b |
perf(query): remove transactions for queries (#8614)
# Which Problems Are Solved
Queries currently execute 3 statements, begin, query, commit
# How the Problems Are Solved
remove transaction handling from query methods in database package
# Additional Changes
- Bump versions of `core_grpc_dependencies`-receipt in Makefile
# Additional info
During load tests we saw a lot of idle transactions of `zitadel_queries`
application name which is the connection pool used to query data in
zitadel. Executed query:
`select query_start - xact_start, pid, application_name, backend_start,
xact_start, query_start, state_change, wait_event_type,
wait_event,substring(query, 1, 200) query from pg_stat_activity where
datname = 'zitadel' and state <> 'idle';`
Mostly the last query executed was `begin isolation level read committed
read only`.
example:
```
?column? | pid | application_name | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | query
-----------------+-------+----------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
00:00:00 | 33030 | zitadel_queries | 2024-10-16 16:25:53.906036+00 | 2024-10-16 16:30:19.191661+00 | 2024-10-16 16:30:19.191661+00 | 2024-10-16 16:30:19.19169+00 | Client | ClientRead | begin isolation level read committed read only
00:00:00 | 33035 | zitadel_queries | 2024-10-16 16:25:53.909629+00 | 2024-10-16 16:30:19.19179+00 | 2024-10-16 16:30:19.19179+00 | 2024-10-16 16:30:19.191805+00 | Client | ClientRead | begin isolation level read committed read only
00:00:00.00412 | 33028 | zitadel_queries | 2024-10-16 16:25:53.904247+00 | 2024-10-16 16:30:19.187734+00 | 2024-10-16 16:30:19.191854+00 | 2024-10-16 16:30:19.191964+00 | Client | ClientRead | SELECT created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision FROM eventstore.events2 WHERE instance_id = $1 AND aggregate_type
00:00:00.084662 | 33134 | zitadel_es_pusher | 2024-10-16 16:29:54.979692+00 | 2024-10-16 16:30:19.178578+00 | 2024-10-16 16:30:19.26324+00 | 2024-10-16 16:30:19.263267+00 | Client | ClientRead | RELEASE SAVEPOINT cockroach_restart
00:00:00.084768 | 33139 | zitadel_es_pusher | 2024-10-16 16:29:54.979585+00 | 2024-10-16 16:30:19.180762+00 | 2024-10-16 16:30:19.26553+00 | 2024-10-16 16:30:19.265531+00 | LWLock | WALWriteLock | commit
00:00:00.077377 | 33136 | zitadel_es_pusher | 2024-10-16 16:29:54.978582+00 | 2024-10-16 16:30:19.187883+00 | 2024-10-16 16:30:19.26526+00 | 2024-10-16 16:30:19.265431+00 | Client | ClientRead | WITH existing AS ( +
| | | | | | | | | (SELECT instance_id, aggregate_type, aggregate_id, "sequence" FROM eventstore.events2 WHERE instance_id = $1 AND aggregate_type = $2 AND aggregate_id = $3 ORDER BY "sequence" DE
00:00:00.012309 | 33123 | zitadel_es_pusher | 2024-10-16 16:29:54.963484+00 | 2024-10-16 16:30:19.175066+00 | 2024-10-16 16:30:19.187375+00 | 2024-10-16 16:30:19.187376+00 | IO | WalSync | commit
00:00:00 | 33034 | zitadel_queries | 2024-10-16 16:25:53.90791+00 | 2024-10-16 16:30:19.262921+00 | 2024-10-16 16:30:19.262921+00 | 2024-10-16 16:30:19.263133+00 | Client | ClientRead | begin isolation level read committed read only
00:00:00 | 33039 | zitadel_queries | 2024-10-16 16:25:53.914106+00 | 2024-10-16 16:30:19.191676+00 | 2024-10-16 16:30:19.191676+00 | 2024-10-16 16:30:19.191687+00 | Client | ClientRead | begin isolation level read committed read only
00:00:00.24539 | 33083 | zitadel_projection_spooler | 2024-10-16 16:27:49.895548+00 | 2024-10-16 16:30:19.020058+00 | 2024-10-16 16:30:19.265448+00 | 2024-10-16 16:30:19.26546+00 | Client | ClientRead | SAVEPOINT exec_stmt
00:00:00 | 33125 | zitadel_es_pusher | 2024-10-16 16:29:54.963859+00 | 2024-10-16 16:30:19.191715+00 | 2024-10-16 16:30:19.191715+00 | 2024-10-16 16:30:19.191729+00 | Client | ClientRead | begin
00:00:00.004292 | 33032 | zitadel_queries | 2024-10-16 16:25:53.906624+00 | 2024-10-16 16:30:19.187713+00 | 2024-10-16 16:30:19.192005+00 | 2024-10-16 16:30:19.192062+00 | Client | ClientRead | SELECT created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision FROM eventstore.events2 WHERE instance_id = $1 AND aggregate_type
00:00:00 | 33031 | zitadel_queries | 2024-10-16 16:25:53.906422+00 | 2024-10-16 16:30:19.191625+00 | 2024-10-16 16:30:19.191625+00 | 2024-10-16 16:30:19.191645+00 | Client | ClientRead | begin isolation level read committed read only
```
The amount of idle transactions is significantly less if the query
transactions are removed:
example:
```
?column? | pid | application_name | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | query
-----------------+-------+----------------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
00:00:00.000094 | 32741 | zitadel_queries | 2024-10-16 16:23:49.73935+00 | 2024-10-16 16:24:59.785589+00 | 2024-10-16 16:24:59.785683+00 | 2024-10-16 16:24:59.785684+00 | | | SELECT created_at, event_type, "sequence", "position", payload, creator, "owner", instance_id, aggregate_type, aggregate_id, revision FROM eventstore.events2 WHERE instance_id = $1 AND aggregate_type
00:00:00 | 32762 | zitadel_es_pusher | 2024-10-16 16:24:02.275136+00 | 2024-10-16 16:24:59.784586+00 | 2024-10-16 16:24:59.784586+00 | 2024-10-16 16:24:59.784607+00 | Client | ClientRead | begin
00:00:00.000167 | 32742 | zitadel_queries | 2024-10-16 16:23:49.740489+00 | 2024-10-16 16:24:59.784274+00 | 2024-10-16 16:24:59.784441+00 | 2024-10-16 16:24:59.784442+00 | | | with usr as ( +
| | | | | | | | | select u.id, u.creation_date, u.change_date, u.sequence, u.state, u.resource_owner, u.username, n.login_name as preferred_login_name +
| | | | | | | | | from projections.users13 u +
| | | | | | | | | left join projections.l
00:00:00.256014 | 32759 | zitadel_projection_spooler | 2024-10-16 16:24:01.418429+00 | 2024-10-16 16:24:59.52959+00 | 2024-10-16 16:24:59.785604+00 | 2024-10-16 16:24:59.785649+00 | Client | ClientRead | UPDATE projections.milestones SET reached_date = $1 WHERE (instance_id = $2) AND (type = $3) AND (reached_date IS NULL)
00:00:00.014199 | 32773 | zitadel_es_pusher | 2024-10-16 16:24:02.320404+00 | 2024-10-16 16:24:59.769509+00 | 2024-10-16 16:24:59.783708+00 | 2024-10-16 16:24:59.783709+00 | IO | WalSync | commit
00:00:00 | 32765 | zitadel_es_pusher | 2024-10-16 16:24:02.28173+00 | 2024-10-16 16:24:59.780413+00 | 2024-10-16 16:24:59.780413+00 | 2024-10-16 16:24:59.780426+00 | Client | ClientRead | begin
00:00:00.012729 | 32777 | zitadel_es_pusher | 2024-10-16 16:24:02.339737+00 | 2024-10-16 16:24:59.767432+00 | 2024-10-16 16:24:59.780161+00 | 2024-10-16 16:24:59.780195+00 | Client | ClientRead | RELEASE SAVEPOINT cockroach_restart
```
---------
Co-authored-by: Tim Möhlmann <[email protected]>
Co-authored-by: Livio Spring <[email protected]>
Co-authored-by: Max Peintner <[email protected]>
Co-authored-by: Elio Bischof <[email protected]>
Co-authored-by: Stefan Benz <[email protected]>
Co-authored-by: Miguel Cabrerizo <[email protected]>
Co-authored-by: Joakim Lodén <[email protected]>
Co-authored-by: Yxnt <[email protected]>
Co-authored-by: Stefan Benz <[email protected]>
Co-authored-by: Harsha Reddy <[email protected]>
Co-authored-by: Zach H <[email protected]>
|
||
|
Tim MöhlmannandGitHub
|
32bad3feb3 |
perf(milestones): refactor (#8788)
# Which Problems Are Solved Milestones used existing events from a number of aggregates. OIDC session is one of them. We noticed in load-tests that the reduction of the oidc_session.added event into the milestone projection is a costly business with payload based conditionals. A milestone is reached once, but even then we remain subscribed to the OIDC events. This requires the projections.current_states to be updated continuously. # How the Problems Are Solved The milestone creation is refactored to use dedicated events instead. The command side decides when a milestone is reached and creates the reached event once for each milestone when required. # Additional Changes In order to prevent reached milestones being created twice, a migration script is provided. When the old `projections.milestones` table exist, the state is read from there and `v2` milestone aggregate events are created, with the original reached and pushed dates. # Additional Context - Closes https://github.com/zitadel/zitadel/issues/8800 |
||
|
Tim MöhlmannandGitHub
|
a84b259e8c |
perf(oidc): nest position clause for session terminated query (#8738)
# Which Problems Are Solved Optimize the query that checks for terminated sessions in the access token verifier. The verifier is used in auth middleware, userinfo and introspection. # How the Problems Are Solved The previous implementation built a query for certain events and then appended a single `PositionAfter` clause. This caused the postgreSQL planner to use indexes only for the instance ID, aggregate IDs, aggregate types and event types. Followed by an expensive sequential scan for the position. This resulting in internal over-fetching of rows before the final filter was applied.  Furthermore, the query was searching for events which are not always applicable. For example, there was always a session ID search and if there was a user ID, we would also search for a browser fingerprint in event payload (expensive). Even if those argument string would be empty. This PR changes: 1. Nest the position query, so that a full `instance_id, aggregate_id, aggregate_type, event_type, "position"` index can be matched. 2. Redefine the `es_wm` index to include the `position` column. 3. Only search for events for the IDs that actually have a value. Do not search (noop) if none of session ID, user ID or fingerpint ID are set. New query plan:  # Additional Changes - cleanup how we load multi-statement migrations and make that a bit more reusable. # Additional Context - Related to https://github.com/zitadel/zitadel/issues/7639 |
||
|
SilvanandGitHub
|
ddeeeed303 |
fix(handler): optimise snapshot hanlding (#8652)
# Which Problems Are Solved There are cases where not all statements of multiExec are succeed. This leads to inconsistent states. One example is [LDAP IDPs](https://github.com/zitadel/zitadel/issues/7959). If statements get executed only partially this can lead to inconsistent states or even break projections for objects which might not were correctly created in a sub table. This behaviour is possible because we use [`SAVEPOINTS`](https://www.postgresql.org/docs/current/sql-savepoint.html) during each statement of a multiExec. # How the Problems Are Solved SAVEPOINTS are only created at the beginning of an exec function not during every execution like before. Additionally `RELEASE` or `ROLLBACK` of `SAVEPOINTS` are only used when needed. # Additional Changes - refactor some unused parameters # Additional Context - closes https://github.com/zitadel/zitadel/issues/7959 |
||
|
Tim MöhlmannandGitHub
|
4eaa3163b6 |
feat(storage): generic cache interface (#8628)
# Which Problems Are Solved We identified the need of caching. Currently we have a number of places where we use different ways of caching, like go maps or LRU. We might also want shared chaches in the future, like Redis-based or in special SQL tables. # How the Problems Are Solved Define a generic Cache interface which allows different implementations. - A noop implementation is provided and enabled as. - An implementation using go maps is provided - disabled in defaults.yaml - enabled in integration tests - Authz middleware instance objects are cached using the interface. # Additional Changes - Enabled integration test command raceflag - Fix a race condition in the limits integration test client - Fix a number of flaky integration tests. (Because zitadel is super fast now!) 🎸 🚀 # Additional Context Related to https://github.com/zitadel/zitadel/issues/8648 |
||
|
Tim MöhlmannandGitHub
|
aeb379e7de | fix(eventstore): revert precise decimal (#8527) (#8679) |
