Skip to content

Drop stale old RBAC FK indexes from model tables#16392

Open
amasolov wants to merge 1 commit into
ansible:develfrom
amasolov:cleanup/remove-old-rbac-indexes
Open

Drop stale old RBAC FK indexes from model tables#16392
amasolov wants to merge 1 commit into
ansible:develfrom
amasolov:cleanup/remove-old-rbac-indexes

Conversation

@amasolov

@amasolov amasolov commented Apr 8, 2026

Copy link
Copy Markdown
SUMMARY

Sets db_index=False on ImplicitRoleField and adds migration 0206 with 38 AlterField operations to drop stale FK indexes on legacy *_role_id columns from model tables. These indexes have zero scans after the DAB RBAC migration (0192+) and add unnecessary write/VACUUM overhead.

This approach keeps Django's migration state in sync with the actual database schema (as suggested in review), rather than using raw RunSQL which would create a mismatch between the field definition and the database.

Related #16391

ISSUE TYPE
  • Bug, Docs Fix or other nominal change
COMPONENT NAME
  • API
STEPS TO REPRODUCE AND EXTRA INFO

Before migration — 38 indexes with zero scans:

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

Affected tables: main_organization (13), main_inventory (5), main_workflowjobtemplate (4), main_project (4), main_credential (3), main_team (3), main_jobtemplate (3), main_instancegroup (3).

After migration: all 38 indexes removed, write performance and VACUUM improved.

Summary by CodeRabbit

  • Chores
    • Removed database indexes from role-related fields across multiple models (credentials, instance groups, inventories, job templates, organizations, projects, teams, workflows) and made implicit role fields default to not creating DB indexes.
  • Tests
    • Added functional tests validating the migration, that affected fields no longer create DB indexes, and that the migration targets the expected set of fields.

@coderabbitai

coderabbitai Bot commented Apr 8, 2026

Copy link
Copy Markdown
📝 Walkthrough

Walkthrough

Modified ImplicitRoleField to set db_index=False by default. Added a migration that removes indexes from implicit role fields across eight models (credential, instancegroup, inventory, jobtemplate, organization, project, team, workflowjobtemplate). Added tests to validate the migration and field behavior.

Changes

Implicit role index removal

Layer / File(s) Summary
ImplicitRoleField default change
awx/main/fields.py
Updated ImplicitRoleField.__init__ to set db_index=False via kwargs.setdefault() before calling parent ForeignKey initializer.
Migration field list
awx/main/migrations/0206_remove_implicit_role_field_indexes.py
Introduces IMPLICIT_ROLE_FIELDS constant enumerating model/field/parent_role tuples to be altered.
Migration operations
awx/main/migrations/0206_remove_implicit_role_field_indexes.py
New Migration that applies migrations.AlterField for each implicit-role FK, setting db_index=False, editable=False, null='True', on_delete=SET_NULL, related_name='+', and to='main.role'.
Migration functional tests
awx/main/tests/functional/migrations/test_0206_remove_implicit_role_field_indexes.py
Added tests importing the migration and asserting dependencies, operation types, field count (38), all altered fields have db_index is False, excluded targets, model coverage across 8 tables, and that ImplicitRoleField defaults db_index=False.

Estimated code review effort

🎯 2 (Simple) | ⏱️ ~12 minutes

🚥 Pre-merge checks | ✅ 4 | ❌ 1

❌ Failed checks (1 warning)

Check name Status Explanation Resolution
Docstring Coverage ⚠️ Warning Docstring coverage is 5.00% which is insufficient. The required threshold is 80.00%. Write docstrings for the functions missing them to satisfy the coverage threshold.
✅ Passed checks (4 passed)
Check name Status Explanation
Description Check ✅ Passed Check skipped - CodeRabbit’s high-level summary is enabled.
Title check ✅ Passed The title clearly summarizes the main change: removing stale database indexes from RBAC foreign key columns in model tables by setting db_index=False.
Linked Issues check ✅ Passed Check skipped because no linked issues were found for this pull request.
Out of Scope Changes check ✅ Passed Check skipped because no linked issues were found for this pull request.

✏️ Tip: You can configure your own custom pre-merge checks in the settings.

✨ Finishing Touches
🧪 Generate unit tests (beta)
  • Create PR with unit tests

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

Comment @coderabbitai help to get the list of available commands and usage tips.

@coderabbitai coderabbitai Bot left a comment

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

🧹 Nitpick comments (2)
awx/main/tests/functional/migrations/test_0206_remove_old_rbac_indexes.py (2)

7-8: Consider removing @pytest.mark.django_db if no DB access is needed.

The tests only inspect the migration module's structure and don't interact with the database. The decorator adds overhead by setting up database transactions for each test.

🤖 Prompt for AI Agents
Verify each finding against the current code and only fix it if needed.

In `@awx/main/tests/functional/migrations/test_0206_remove_old_rbac_indexes.py`
around lines 7 - 8, The class-level `@pytest.mark.django_db` on
TestRemoveOldRbacIndexes is unnecessary if none of its tests access the
database; remove the decorator from the TestRemoveOldRbacIndexes class (or move
the marker to only those individual test methods that require DB access) so
tests that merely inspect the migration module's structure don't incur DB setup
overhead.

66-71: Dead code: parts and table variables are unused.

Lines 67-68 compute parts and table but the actual table extraction logic on lines 69-71 ignores them entirely, relying instead on idx.startswith(expected).

♻️ Proposed simplification
     def test_covers_all_model_tables(self):
         expected_tables = {
             'main_credential',
             'main_instancegroup',
             'main_inventory',
             'main_jobtemplate',
             'main_organization',
             'main_project',
             'main_team',
             'main_workflowjobtemplate',
             'main_activitystream_role',
         }
         actual_tables = set()
         for idx in self.migration_module.OLD_RBAC_FK_INDEXES:
-            parts = idx.rsplit('_role_id', 1)
-            table = parts[0].rsplit('_', 0)[0]
             for expected in expected_tables:
                 if idx.startswith(expected):
                     actual_tables.add(expected)
         assert actual_tables == expected_tables
🤖 Prompt for AI Agents
Verify each finding against the current code and only fix it if needed.

In `@awx/main/tests/functional/migrations/test_0206_remove_old_rbac_indexes.py`
around lines 66 - 71, The variables parts and table computed from each idx in
migration_module.OLD_RBAC_FK_INDEXES are unused; remove the dead computations
(the lines that set parts = idx.rsplit('_role_id', 1) and table =
parts[0].rsplit('_', 0)[0]) and keep the existing startswith-based check against
expected_tables that populates actual_tables, ensuring no other code depends on
parts/table.
🤖 Prompt for all review comments with AI agents
Verify each finding against the current code and only fix it if needed.

Nitpick comments:
In `@awx/main/tests/functional/migrations/test_0206_remove_old_rbac_indexes.py`:
- Around line 7-8: The class-level `@pytest.mark.django_db` on
TestRemoveOldRbacIndexes is unnecessary if none of its tests access the
database; remove the decorator from the TestRemoveOldRbacIndexes class (or move
the marker to only those individual test methods that require DB access) so
tests that merely inspect the migration module's structure don't incur DB setup
overhead.
- Around line 66-71: The variables parts and table computed from each idx in
migration_module.OLD_RBAC_FK_INDEXES are unused; remove the dead computations
(the lines that set parts = idx.rsplit('_role_id', 1) and table =
parts[0].rsplit('_', 0)[0]) and keep the existing startswith-based check against
expected_tables that populates actual_tables, ensuring no other code depends on
parts/table.

ℹ️ Review info
⚙️ Run configuration

Configuration used: Organization UI

Review profile: CHILL

Plan: Pro

Run ID: c81d98a5-c4d4-454d-b3a2-f4f501cd21e5

📥 Commits

Reviewing files that changed from the base of the PR and between b83019b and 611886d.

📒 Files selected for processing (2)
  • awx/main/migrations/0206_remove_old_rbac_indexes.py
  • awx/main/tests/functional/migrations/test_0206_remove_old_rbac_indexes.py

@AlanCoding AlanCoding left a comment

Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I like the spirit of this, but I have been bitten before by a mismatch between the Django field definition and what's in the database. Dropping in raw SQL is likely to cause that mismatch and the migration logic will often error long in the future when the next change is made.

AFAIK, the more correct way to do this is apply db_index=False to those fields when set on the fields. In this case, it is a custom field that inherits from ForeignKey, and then makemigrations should pick that up and drop these indexes on their own. This approach is likely to get us in a major bind in the future.

@amasolov

amasolov commented Apr 8, 2026

Copy link
Copy Markdown
Author

Cheers @AlanCoding , spot on. Raw SQL would definitely leave Django field state out of whack with the database, and that'd come back to bite us on the next migration touching these fields.

I've reworked the PR to go the route you suggested:

  • ImplicitRoleField.__init__ now sets db_index=False via kwargs.setdefault('db_index', False)
  • Migration 0206 swapped RunSQL for 38 AlterField operations, one per ImplicitRoleField across all 8 affected models. This matches what makemigrations would spit out.
  • Scope trimmed from 39 to 38. Dropped main_activitystream_role_role_id since that's a legit M2M through-table index (from ActivityStream.role = ManyToManyField("Role")), not an ImplicitRoleField.

Tests have been rewritten to validate the AlterField approach and check db_index=False on each operation.

@amasolov amasolov force-pushed the cleanup/remove-old-rbac-indexes branch from b94a36d to a511e32 Compare May 20, 2026 07:05
After the transition from legacy AWX RBAC to DAB RBAC (migration
0192+), the ImplicitRoleField FK columns on model tables retain 38
indexes that are never scanned.  These indexes accumulate through
upgrades and add unnecessary write and VACUUM overhead.

Set db_index=False on ImplicitRoleField.__init__ and add migration
0206 with 38 AlterField operations across Organization, Team,
Credential, Project, Inventory, JobTemplate, WorkflowJobTemplate,
and InstanceGroup.  This keeps Django's migration state in sync
with the actual database schema and avoids the mismatch that raw
RunSQL would cause.

Signed-off-by: Alexey Masolov <amasolov@redhat.com>
Signed-off-by: Alexey Masolov <alexey.masolov@gmail.com>
Co-authored-by: Cursor <cursoragent@cursor.com>
Signed-off-by: Alexey Masolov <alexey.masolov@gmail.com>
Co-authored-by: Cursor <cursoragent@cursor.com>
@amasolov amasolov force-pushed the cleanup/remove-old-rbac-indexes branch from a511e32 to e895059 Compare May 20, 2026 07:43

@coderabbitai coderabbitai Bot left a comment

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

🧹 Nitpick comments (1)
awx/main/tests/functional/migrations/test_0206_remove_implicit_role_field_indexes.py (1)

21-27: ⚡ Quick win

Assert operation count directly to avoid vacuous passes.

Current checks can still pass if Migration.operations is empty. Add a direct count assertion tied to IMPLICIT_ROLE_FIELDS.

Proposed test hardening
     def test_migration_targets_correct_field_count(self):
         assert len(self.migration_module.IMPLICIT_ROLE_FIELDS) == 38
+        assert len(self.migration_module.Migration.operations) == len(self.migration_module.IMPLICIT_ROLE_FIELDS)
🤖 Prompt for AI Agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

In
`@awx/main/tests/functional/migrations/test_0206_remove_implicit_role_field_indexes.py`
around lines 21 - 27, The test can vacuously pass if Migration.operations is
empty; add an explicit assertion that the number of migration operations equals
the number of implicit fields to harden the test. In
test_all_altered_fields_disable_db_index, assert len(Migration.operations) ==
len(self.migration_module.IMPLICIT_ROLE_FIELDS) (or == 38 to match
test_migration_targets_correct_field_count) before iterating, so you ensure
Migration.operations is populated and aligned with IMPLICIT_ROLE_FIELDS.
🤖 Prompt for all review comments with AI agents
Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Nitpick comments:
In
`@awx/main/tests/functional/migrations/test_0206_remove_implicit_role_field_indexes.py`:
- Around line 21-27: The test can vacuously pass if Migration.operations is
empty; add an explicit assertion that the number of migration operations equals
the number of implicit fields to harden the test. In
test_all_altered_fields_disable_db_index, assert len(Migration.operations) ==
len(self.migration_module.IMPLICIT_ROLE_FIELDS) (or == 38 to match
test_migration_targets_correct_field_count) before iterating, so you ensure
Migration.operations is populated and aligned with IMPLICIT_ROLE_FIELDS.

ℹ️ Review info
⚙️ Run configuration

Configuration used: Organization UI

Review profile: CHILL

Plan: Enterprise

Run ID: b767c317-b8fb-457a-bf24-fe076da7c310

📥 Commits

Reviewing files that changed from the base of the PR and between a511e32 and e895059.

📒 Files selected for processing (3)
  • awx/main/fields.py
  • awx/main/migrations/0206_remove_implicit_role_field_indexes.py
  • awx/main/tests/functional/migrations/test_0206_remove_implicit_role_field_indexes.py

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants