mirror of
https://github.com/andwati/zitadel.git
synced 2026-10-18 05:52:51 +00:00
75bc058bce
# 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]>
76 lines
2.7 KiB
Go
76 lines
2.7 KiB
Go
package setup
|
|
|
|
import (
|
|
"context"
|
|
"fmt"
|
|
|
|
"github.com/zitadel/zitadel/internal/database"
|
|
"github.com/zitadel/zitadel/internal/eventstore"
|
|
)
|
|
|
|
// eventstoreAutovacuum is a repeatable migration step that tunes PostgreSQL's
|
|
// autovacuum and autoanalyze behavior of the eventstore.events2 table,
|
|
// as configured by Eventstore.Autovacuum.
|
|
//
|
|
// It repeats whenever the configuration changed since the last run.
|
|
type eventstoreAutovacuum struct {
|
|
dbClient *database.DB
|
|
|
|
Enabled bool `json:"enabled"`
|
|
VacuumThreshold uint32 `json:"vacuumThreshold"`
|
|
AnalyzeThreshold uint32 `json:"analyzeThreshold"`
|
|
}
|
|
|
|
const eventstoreAutovacuumResetStmt = `ALTER TABLE eventstore.events2 RESET (
|
|
autovacuum_vacuum_scale_factor,
|
|
autovacuum_analyze_scale_factor,
|
|
autovacuum_vacuum_insert_scale_factor,
|
|
autovacuum_vacuum_insert_threshold,
|
|
autovacuum_analyze_threshold,
|
|
autovacuum_vacuum_threshold
|
|
)`
|
|
|
|
// minAutovacuumThreshold guards against configuring vacuum/analyze runs so
|
|
// frequently that they thrash the database with constant autovacuum activity.
|
|
const minAutovacuumThreshold = 10000
|
|
|
|
func (mig *eventstoreAutovacuum) Execute(ctx context.Context, _ eventstore.Event) error {
|
|
if !mig.Enabled {
|
|
_, err := mig.dbClient.ExecContext(ctx, eventstoreAutovacuumResetStmt)
|
|
return err
|
|
}
|
|
if mig.VacuumThreshold <= minAutovacuumThreshold || mig.AnalyzeThreshold <= minAutovacuumThreshold {
|
|
return fmt.Errorf("%s: VacuumThreshold and AnalyzeThreshold must be greater than %d", mig, minAutovacuumThreshold)
|
|
}
|
|
|
|
// autovacuum_vacuum_threshold covers rows changed by updates and deletes, while
|
|
// autovacuum_vacuum_insert_threshold only covers inserts. events2 is append-only, but
|
|
// VacuumThreshold is applied to both, so a manual UPDATE/DELETE does not fall back to
|
|
// the effectively disabled default scale factor.
|
|
stmt := fmt.Sprintf(`ALTER TABLE eventstore.events2 SET (
|
|
autovacuum_vacuum_scale_factor = 0.0,
|
|
autovacuum_analyze_scale_factor = 0.0,
|
|
autovacuum_vacuum_insert_scale_factor = 0.0,
|
|
autovacuum_vacuum_insert_threshold = %d,
|
|
autovacuum_analyze_threshold = %d,
|
|
autovacuum_vacuum_threshold = %d
|
|
)`, mig.VacuumThreshold, mig.AnalyzeThreshold, mig.VacuumThreshold)
|
|
_, err := mig.dbClient.ExecContext(ctx, stmt)
|
|
return err
|
|
}
|
|
|
|
func (mig *eventstoreAutovacuum) String() string {
|
|
return "repeatable_eventstore_autovacuum"
|
|
}
|
|
|
|
// Check implements [migration.RepeatableMigration].
|
|
func (mig *eventstoreAutovacuum) Check(lastRun map[string]interface{}) bool {
|
|
enabled, _ := lastRun["enabled"].(bool)
|
|
vacuumThreshold, _ := lastRun["vacuumThreshold"].(float64)
|
|
analyzeThreshold, _ := lastRun["analyzeThreshold"].(float64)
|
|
|
|
return enabled != mig.Enabled ||
|
|
uint32(vacuumThreshold) != mig.VacuumThreshold ||
|
|
uint32(analyzeThreshold) != mig.AnalyzeThreshold
|
|
}
|