# 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 <noreply@anthropic.com>
Co-authored-by: Copilot Autofix powered by AI <175728472+Copilot@users.noreply.github.com>
Co-authored-by: Silvan <27845747+adlerhurst@users.noreply.github.com>
75bc058bce