[AAP-78392] Optimize HostManager.active_count() with cache and functional index

active_count() runs a full sequential scan with LOWER()+DISTINCT on main_host for every license check. At customer scale this consumed 74.5 minutes of DB time over 4 hours (47K calls at 93ms avg).

Add a 60-second Redis-backed cache via the existing memoize decorator to reduce call volume by ~99.5%. Add a functional btree index on LOWER(name) to eliminate the sequential scan for the remaining calls.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

.github/workflows/spec-sync-on-merge.yml

@@ -113,11 +113,19 @@ jobs:
         env:
           GH_TOKEN: ${{ secrets.OPENAPI_SPEC_SYNC_TOKEN }}
           COMMIT_MESSAGE: ${{ github.event.head_commit.message }}
-          SPEC_REPO: ansible-automation-platform/aap-openapi-specs
         run: |
+          # Configure git
+          git config user.name "github-actions[bot]"
+          git config user.email "github-actions[bot]@users.noreply.github.com"
+
+          # Create branch for PR
           SHORT_SHA="${{ github.sha }}"
           SHORT_SHA="${SHORT_SHA:0:7}"
           BRANCH_NAME="update-Controller-${{ github.ref_name }}-${SHORT_SHA}"
+          git checkout -b "$BRANCH_NAME"
+
+          # Add and commit changes
+          git add "controller.json"
 
           if [ "${{ steps.compare.outputs.is_new_file }}" == "true" ]; then
             COMMIT_MSG="Add Controller OpenAPI spec for ${{ github.ref_name }}"
@@ -125,38 +133,15 @@ jobs:
             COMMIT_MSG="Update Controller OpenAPI spec for ${{ github.ref_name }}"
           fi
 
-          COMMIT_MSG="${COMMIT_MSG}
+          git commit -m "$COMMIT_MSG
 
           Synced from ${{ github.repository }}@${{ github.sha }}
-          Source branch: ${{ github.ref_name }}"
+          Source branch: ${{ github.ref_name }}
 
-          # Create branch via API
-          BASE_SHA=$(gh api "repos/${SPEC_REPO}/git/ref/heads/${{ github.ref_name }}" --jq '.object.sha')
-          gh api "repos/${SPEC_REPO}/git/refs" \
-            -f "ref=refs/heads/${BRANCH_NAME}" \
-            -f "sha=${BASE_SHA}"
+          Co-Authored-By: github-actions[bot] <github-actions[bot]@users.noreply.github.com>"
 
-          # Create blob and commit via API (commits created through the API are automatically signed by GitHub)
-          BLOB_SHA=$(gh api "repos/${SPEC_REPO}/git/blobs" \
-            -f "content=$(base64 -w 0 controller.json)" \
-            -f "encoding=base64" \
-            --jq '.sha')
-
-          TREE_SHA=$(gh api "repos/${SPEC_REPO}/git/trees" \
-            -f "base_tree=${BASE_SHA}" \
-            --input <(jq -n --arg blob "$BLOB_SHA" '{tree: [{path: "controller.json", mode: "100644", type: "blob", sha: $blob}]}') \
-            --jq '.sha')
-
-          NEW_COMMIT_SHA=$(gh api "repos/${SPEC_REPO}/git/commits" \
-            -f "message=${COMMIT_MSG}" \
-            -f "tree=${TREE_SHA}" \
-            -f "parents[]=${BASE_SHA}" \
-            --jq '.sha')
-
-          # Update branch ref to point to the new signed commit
-          gh api "repos/${SPEC_REPO}/git/refs/heads/${BRANCH_NAME}" \
-            -X PATCH \
-            -f "sha=${NEW_COMMIT_SHA}"
+          # Push branch
+          git push origin "$BRANCH_NAME"
 
           # Create PR
           PR_TITLE="[${{ github.ref_name }}] Update Controller spec from merged commit"
@@ -180,7 +165,6 @@ jobs:
           🤖 This PR was automatically generated by the OpenAPI spec sync workflow."
 
           gh pr create \
-            --repo "${SPEC_REPO}" \
             --title "$PR_TITLE" \
             --body "$PR_BODY" \
             --base "${{ github.ref_name }}" \

awx/main/managers.py

@@ -10,6 +10,7 @@ from django.db.models.functions import Lower
 
 from ansible_base.lib.utils.db import advisory_lock
 
+from awx.main.utils.common import memoize
 from awx.main.utils.filters import SmartFilter
 from awx.main.constants import RECEPTOR_PENDING
 
@@ -85,6 +86,7 @@ class HostLatestSummaryQuerySet(models.QuerySet):
 class HostManager(models.Manager.from_queryset(HostLatestSummaryQuerySet)):
     """Custom manager class for Hosts model."""
 
+    @memoize(ttl=60, cache_key='host_active_count')
     def active_count(self):
         """Return count of active, unique hosts for licensing.
         Construction of query involves:

awx/main/migrations/0206_add_host_name_lower_index.py

@@ -0,0 +1,19 @@
+from django.db import migrations, models
+from django.db.models.functions import Lower
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('main', '0205_add_ordering_to_instancegroup_and_workflow_nodes'),
+    ]
+
+    operations = [
+        migrations.AddIndex(
+            model_name='host',
+            index=models.Index(
+                Lower('name'),
+                name='main_host_name_lower_idx',
+            ),
+        ),
+    ]

awx/main/models/inventory.py

@@ -19,6 +19,7 @@ from django.core.exceptions import ValidationError
 from django.urls import resolve
 from django.utils.timezone import now
 from django.db.models import Q, Subquery, OuterRef
+from django.db.models.functions import Lower
 
 # REST Framework
 from rest_framework.exceptions import ParseError
@@ -523,6 +524,9 @@ class Host(CommonModelNameNotUnique, RelatedJobsMixin):
         app_label = 'main'
         unique_together = (("name", "inventory"),)  # FIXME: Add ('instance_id', 'inventory') after migration.
         ordering = ('name',)
+        indexes = [
+            models.Index(Lower('name'), name='main_host_name_lower_idx'),
+        ]
 
     inventory = models.ForeignKey(
         'Inventory',

awx/main/tests/functional/models/test_inventory.py

@@ -92,6 +92,22 @@ class TestInventoryScript:
 
 @pytest.mark.django_db
 class TestActiveCount:
+    @pytest.fixture(autouse=True)
+    def _bypass_active_count_cache(self):
+        from django.core.cache import cache
+
+        cache.delete('host_active_count')
+        original_set = cache.set
+
+        def skip_host_cache(key, *args, **kwargs):
+            if key == 'host_active_count':
+                return
+            return original_set(key, *args, **kwargs)
+
+        with mock.patch.object(cache, 'set', side_effect=skip_host_cache):
+            yield
+        cache.delete('host_active_count')
+
     def test_host_active_count(self, organization):
         inv1 = Inventory.objects.create(name='inv1', organization=organization)
         inv2 = Inventory.objects.create(name='inv2', organization=organization)
@@ -141,6 +157,41 @@ class TestActiveCount:
         assert Host.objects.active_count() == 2
 
 
+@pytest.mark.django_db
+class TestActiveCountCache:
+    @pytest.fixture(autouse=True)
+    def _clear_active_count_cache(self):
+        from awx.main.utils.common import memoize_delete
+
+        memoize_delete('host_active_count')
+        yield
+        memoize_delete('host_active_count')
+
+    def test_active_count_cache(self, organization):
+        inv = Inventory.objects.create(name='inv1', organization=organization)
+        inv.hosts.create(name='host1')
+        assert Host.objects.active_count() == 1
+
+        inv.hosts.create(name='host2')
+        assert Host.objects.active_count() == 1  # still cached
+
+        from awx.main.utils.common import memoize_delete
+
+        memoize_delete('host_active_count')
+        assert Host.objects.active_count() == 2
+
+    def test_active_count_cache_after_delete(self, organization):
+        inv = Inventory.objects.create(name='inv1', organization=organization)
+        h = inv.hosts.create(name='host1')
+        assert Host.objects.active_count() == 1
+
+        h.delete()
+        from awx.main.utils.common import memoize_delete
+
+        memoize_delete('host_active_count')
+        assert Host.objects.active_count() == 0
+
+
 @pytest.mark.django_db
 class TestSCMUpdateFeatures:
     def test_source_location(self, scm_inventory_source):