Problem
Since #688, login looks the address up with LOWER(email) = ? (backend/internal/infrastructure/repository/gorm_user_repository.go, GetByEmail). The users.email unique index is on the raw column, so Postgres can't use it for this lookup and scans users on every sign-in, sign-up, reset and invitation. That's fine with a few hundred accounts. It gets worse linearly, and every login pays it.
This was held back because migration numbering was waiting on the TPRM stack (#687). That stack has merged. The next free number is 0068 (0066 is taken by #849's PR, 0067 is on master).
Acceptance criteria
- Migration
0068_users_email_lower_index adds a non-unique index on LOWER(email). The down migration drops it.
- On Postgres,
EXPLAIN of the GetByEmail query uses the index (plan pasted in the PR).
- The migration does not fail on a database that already holds case-variant duplicates, which is why the index is not unique.
- A read-only query lists any existing case-variant duplicates (
GROUP BY LOWER(email) HAVING COUNT(*) > 1). It is documented in docs/MIGRATIONS.md so an operator can check before a unique constraint is ever proposed.
Definition of Done
Out of scope: making the index unique. That needs the duplicate report first, and possibly a data decision.
Problem
Since #688, login looks the address up with
LOWER(email) = ?(backend/internal/infrastructure/repository/gorm_user_repository.go,GetByEmail). Theusers.emailunique index is on the raw column, so Postgres can't use it for this lookup and scansuserson every sign-in, sign-up, reset and invitation. That's fine with a few hundred accounts. It gets worse linearly, and every login pays it.This was held back because migration numbering was waiting on the TPRM stack (#687). That stack has merged. The next free number is
0068(0066is taken by #849's PR,0067is on master).Acceptance criteria
0068_users_email_lower_indexadds a non-unique index onLOWER(email). The down migration drops it.EXPLAINof theGetByEmailquery uses the index (plan pasted in the PR).GROUP BY LOWER(email) HAVING COUNT(*) > 1). It is documented indocs/MIGRATIONS.mdso an operator can check before a unique constraint is ever proposed.Definition of Done
EXPLAINoutput in the PR.go build ./... && go vet ./... && go test ./... -racegreen.Closes.Out of scope: making the index unique. That needs the duplicate report first, and possibly a data decision.