Apache Airflow version
3.1.6
If "Other Airflow 3 version" selected, which one?
3.1.5
What happened?
In SchedulerJobRunner._activate_referenced_assets(), the query that builds active_assets uses a tuple-based IN filter on (name, uri) constructed from a Python collection (assets). On PostgreSQL 17 with ~16,000 rows in the assets table, this pattern can generate a very large IN list (many bind parameters), which significantly increases parse/plan overhead and can make the scheduler appear to “hang” during asset orphanage/activation processing.
Repo: apache/airflow
File: airflow-core/src/airflow/jobs/scheduler_job_runner.py
Method: SchedulerJobRunner.activate_referenced_assets
DB engine: PostgreSQL 17
Scale: ~16,000 records in assets table (and the in-memory assets collection can be large)
Problematic pattern: tuple(AssetActive.name, AssetActive.uri).in_((a.name, a.uri) for a in assets)
What you think should happen instead?
Fetching the subset of AssetActive corresponding to referenced assets should be efficient even when the referenced set is large, without constructing massive parameter lists from Python.
Use JOIN / EXISTS against a subquery/CTE representing the referenced assets set, so the set is computed inside the database and avoids Python-materialized parameter lists.
Conceptual approach: AssetActive JOIN (subselect of referenced assets) ON (name, uri).
How to reproduce
This issue is most visible when the referenced-assets set is large, since the current implementation materializes the set in Python and feeds it back to Postgres as a giant IN list.
Specific blo
Query specifies blocker:
SELECT airflow.asset_active.name, airflow.asset_active.uri
FROM airflow.asset_active
WHERE (airflow.asset_active.name, airflow.asset_active.uri) IN (('s3://bucket-airflow/prod/dataset/8495.txt', 's3://bucket-airflow/prod/dataset/8495.txt'), ('s3://bucket-airflow/prod/dataset/10421.txt', 's3://bucket-airflow/prod/dataset/10421.txt'), .......
This query block the scheduler and need reboot.
Operating System
Ubuntu 22.04.4 LTS
Versions of Apache Airflow Providers
No response
Deployment
Official Apache Airflow Helm Chart
Deployment details
No response
Anything else?
No response
Are you willing to submit PR?
Code of Conduct
Apache Airflow version
3.1.6
If "Other Airflow 3 version" selected, which one?
3.1.5
What happened?
In SchedulerJobRunner._activate_referenced_assets(), the query that builds active_assets uses a tuple-based IN filter on (name, uri) constructed from a Python collection (assets). On PostgreSQL 17 with ~16,000 rows in the assets table, this pattern can generate a very large IN list (many bind parameters), which significantly increases parse/plan overhead and can make the scheduler appear to “hang” during asset orphanage/activation processing.
Repo: apache/airflow
File: airflow-core/src/airflow/jobs/scheduler_job_runner.py
Method: SchedulerJobRunner.activate_referenced_assets
DB engine: PostgreSQL 17
Scale: ~16,000 records in assets table (and the in-memory assets collection can be large)
Problematic pattern: tuple(AssetActive.name, AssetActive.uri).in_((a.name, a.uri) for a in assets)
What you think should happen instead?
Fetching the subset of AssetActive corresponding to referenced assets should be efficient even when the referenced set is large, without constructing massive parameter lists from Python.
Use JOIN / EXISTS against a subquery/CTE representing the referenced assets set, so the set is computed inside the database and avoids Python-materialized parameter lists.
Conceptual approach: AssetActive JOIN (subselect of referenced assets) ON (name, uri).
How to reproduce
This issue is most visible when the referenced-assets set is large, since the current implementation materializes the set in Python and feeds it back to Postgres as a giant IN list.
Specific blo
Query specifies blocker:
SELECT airflow.asset_active.name, airflow.asset_active.uri
FROM airflow.asset_active
WHERE (airflow.asset_active.name, airflow.asset_active.uri) IN (('s3://bucket-airflow/prod/dataset/8495.txt', 's3://bucket-airflow/prod/dataset/8495.txt'), ('s3://bucket-airflow/prod/dataset/10421.txt', 's3://bucket-airflow/prod/dataset/10421.txt'), .......
This query block the scheduler and need reboot.
Operating System
Ubuntu 22.04.4 LTS
Versions of Apache Airflow Providers
No response
Deployment
Official Apache Airflow Helm Chart
Deployment details
No response
Anything else?
No response
Are you willing to submit PR?
Code of Conduct