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_users table. For each batch:
    • Find user_id that do not exist in users table and delete those records
    • Find organization_id that do not exist in organizations table and delete those records

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

Merge request reports

Loading