1. What is the nature and description of the request?

The Activity Stream query in Ansible Automation Platform (AAP) executes frequently and has been observed to consume significant time and memory in large-scale environments where thousands of tasks are executed daily.

Example of the query pattern:

SELECT COUNT(*)
FROM (
  SELECT DISTINCT
    "main_activitystream"."id",
    "main_activitystream"."actor_id",
    "main_activitystream"."operation",
    "main_activitystream"."timestamp",
    "main_activitystream"."changes",
    "main_activitystream"."deleted_actor",
    "main_activitystream"."action_node",
    "main_activitystream"."object_relationship_type",
    "main_activitystream"."object1",
    "main_activitystream"."object2",
    "main_activitystream"."setting"
  FROM "main_activitystream"
  LEFT OUTER JOIN "main_activitystream_user"
    ON ("main_activitystream"."id" = "main_activitystream_user"."activitystream_id")
  LEFT OUTER JOIN "main_activitystream_ad_hoc_command"
    ON ("main_activitystream"."id" = "main_activitystream_ad_hoc_command"."activitystream_id")
  LEFT OUTER JOIN "main_adhoccommand"
    ON ("main_activitystream_ad_hoc_command"."adhoccommand_id" = "main_adhoccommand"."unifiedjob_ptr_id")
  LEFT OUTER JOIN "main_activitystream_inventory"
    ON ...
)

In high-activity environments (e.g., 3,000–4,000 tasks daily), this query executes repeatedly and can lead to high memory utilization and PostgreSQL instability.

Example log output:

2025-09-16 11:08:23.586 CEST [2715656] LOG:  server process (PID 2111447) was terminated by signal 9: Killed
2025-09-16 11:08:23.586 CEST [2715656] DETAIL:  Failed process was running: SELECT COUNT(*) FROM (SELECT DISTINCT ... FROM "main_activitystream" ...)

This RFE requests the engineering team to review and optimize the Activity Stream query and ORM logic to improve database efficiency and prevent excessive memory consumption.

2. Why does the customer need this? (List the business requirements here)

The customer currently executes between 3,000 and 4,000 tasks daily, with automation volume expected to increase further. When the Activity Stream query runs frequently on large datasets, it can trigger Out of Memory (OOM) events, leading to PostgreSQL crashes. This directly affects:

• Database availability (PostgreSQL restarts due to OOM),
• Scalability and reliability of large environments.

Due to all this, optimization is essential to maintain stability and performance as automation workloads grow.

3. How would you like to achieve this? (List the functional requirements here)

• Database Indexing: add or validate indexes on key Activity Stream columns and join fields.
• Query Simplification: review SQL to minimize redundant joins or DISTINCT operations.