39 stale old RBAC FK indexes remain after DAB RBAC migration

[amasolov]

Bug Summary

After upgrading from AWX versions that used the legacy ImplicitRoleField RBAC system to the current DAB RBAC system, 39 FK indexes on *_role_id columns remain on model tables (Organization, Team, Credential, Project, Inventory, JobTemplate, WorkflowJobTemplate, InstanceGroup, ActivityStream). These indexes are never scanned because the DAB RBAC system does not use these columns, yet they persist indefinitely across upgrades.

In a controlled AAP 2.4 → 2.5 → 2.6 upgrade test, all 39 indexes showed idx_scan = 0 after the DAB RBAC migration. In production environments with years of accumulated data, these unused indexes slow writes, increase WAL volume, and extend VACUUM runtime.

Environment

• AWX version: devel
• Installation method: openshift
• Modifications: no

Steps to reproduce

1. Deploy AWX (or AAP Controller) on a version that uses the old ImplicitRoleField RBAC
2. Upgrade to a version using DAB RBAC (migration 0192+)
3. Query:

   SELECT indexrelname, idx_scan
   FROM pg_stat_user_indexes
   WHERE indexrelname LIKE '%role_id%'
     AND schemaname = 'public'
   ORDER BY idx_scan;

4. Observe ~39 indexes with idx_scan = 0

Expected results

Migration 0192+ (or a follow-up) should drop the FK indexes on old RBAC columns that are no longer used.

Actual results

All 39 FK indexes persist with zero scans, adding write overhead and planner noise.

Additional information

Affected tables and index counts:

Table	Stale indexes
main_organization	13
main_inventory	5
main_workflowjobtemplate	4
main_project	4
main_credential	3
main_team	3
main_jobtemplate	3
main_instancegroup	3
main_activitystream_role	1
Total	39