Add background migration for removing invalid org user records
What does this MR do and why?
This MR is part of a few MR's that will replace the use of Loose Foreign Keys for organization_users table
| MR | Description |
|---|---|
| !169382 (merged) | Add Foreign Key for user_id (NOT VALID) |
| !180028 (merged) | Add Foreign Key for organization_id (NOT VALID) |
| This one | Delete invalid organization_users records |
| TBD | Finalize the job |
| TBD | Make the FK's VALID |
This MR will delete invalidd organization_users records. "Invalid" means: records that refer to an organization or user that does not exist. We don't expect such records but they could exist.
Implementation
- Process all records in
organization_userstable. For each batch:- Find user_id that do not exist in
userstable and delete those records - Find organization_id that do not exist in
organizationstable and delete those records
- Find user_id that do not exist in
References
Related to #493506 (closed)
DELETE queries performance
Delete records because the associated organization does not exist: 18 ms
Delete records because the associated user does not exist: 7 ms
How to set up and validate locally
Created 50 000 random organization_users records with valid and invalid data.
Before:
?column? | count
----------+-------
invalid | 1976
valid | 48024
(2 rows)
[1] pry(main)> load 'db/post_migrate/20250304144127_queue_remove_invalid_organization_users.rb'
=> true
[2] pry(main)> QueueRemoveInvalidOrganizationUsers.new.up
The invalid records are removed:
?column? | count
----------+-------
invalid | 0
valid | 48024
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Edited by Rutger Wessels