Abdel39 opened a new pull request, #75404: URL: https://github.com/apache/airflow/pull/75404
`SchedulerJobRunner._update_asset_orphanage` outer-joins `asset` to four independent one-to-many tables (`dag_schedule_asset_reference`, `task_outlet_asset_reference`, `task_inlet_asset_reference`, `asset_alias_asset`) and groups by `asset.id`. The joins multiply: each asset produces `schedule_refs * outlet_refs * inlet_refs * aliases` rows before the `GROUP BY`. The result is used in three statements (orphan `DELETE`, active `SELECT`, inactive `SELECT`). A handful of popular assets (many consumers, many producing tasks) is enough to push this past Postgres `statement_timeout`. On one of our deployments (~11.7k assets, 4 scheduler replicas) the activation query hit the timeout on every orphanage pass, about every 20s. Since the timer is not `non_fatal`, that stalled the scheduler loop and caused intermittent 503s. It got noticeably worse on 3.3, when the alias association join (#58058) added a fourth factor. This replaces the join and `GROUP BY` with one `EXISTS` per reference table. Each is served by an index led by `asset_id` (the three reference tables' primary keys and `idx_asset_alias_asset_asset_id`), stops at the first match, and doesn't multiply rows. The CTEs keep the same columns, so `_orphan_unreferenced_assets` and `_activate_referenced_assets` are unchanged. Tests: existing orphanage tests pass unchanged, plus a new case where one asset has several references in every table. Would appreciate the `backport-to-v3-3-test` label if this looks right, since 3.3 is where the regression shows up. --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes (please specify the tool below) Generated-by: Claude Code following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) --- 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
