Slow migration from v2.66.18 to v3.3.5 (1.5M users)
Hi! We are using Zitadel in production on Kubernetes, installed via Helm Chart. We are currently on version v2.66.18 but are planning to upgrade to v3.3.5 because we are experiencing database performance issues (slow response on projection.users13 queries) that are currently "solved" with a large AWS RDS Postgres database
We have an organization with 1.5M users (and growing). Yes, it's really big.
One of the problems we faced with the upgrade is the slow migration time (approximately 24 hours), and we realized that it's caused by a slow query (~12s). Every 12 seconds, the migration adds only a few users to projections.users14. We are conducting pre-migration tests with a dump of the production database in another environment, and the RDS instance type we are using there is
Our eventstore.events2 table is really large:
How can we upgrade faster? Can we add any index to this table? Does Projections.BulkLimit help with the limit of that query to process more users at once?
Thanks!
(db.m6g.8xlarge(db.m6g.8xlarge). We also have our custom frontend for login.We have an organization with 1.5M users (and growing). Yes, it's really big.
One of the problems we faced with the upgrade is the slow migration time (approximately 24 hours), and we realized that it's caused by a slow query (~12s). Every 12 seconds, the migration adds only a few users to projections.users14. We are conducting pre-migration tests with a dump of the production database in another environment, and the RDS instance type we are using there is
db.m6g.4xlargedb.m6g.4xlarge. We don't see high CPU or IO usage in the database during the migration (nor in the Setup Job Pod). CPU usage is less than 10% and IOPS are quiet, but in AWS Performance Insights we see this slow query: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 = ANY($2) AND event_type = ANY($3) AND "position" >= $4 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($5) AND state <> 'idle') ORDER BY "position", in_tx_order LIMIT $6 OFFSET $7SELECT 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 = ANY($2) AND event_type = ANY($3) AND "position" >= $4 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($5) AND state <> 'idle') ORDER BY "position", in_tx_order LIMIT $6 OFFSET $7Our eventstore.events2 table is really large:
=> select count(*) from eventstore.events2 ;
count
----------
96100444=> select count(*) from eventstore.events2 ;
count
----------
96100444How can we upgrade faster? Can we add any index to this table? Does Projections.BulkLimit help with the limit of that query to process more users at once?
Thanks!
