Repository navigation
Avoid large tuple IN query in SchedulerJobRunner._activate_referenced_assets on PostgreSQL (performance / perceived hanging) #61453
Description
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?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct
Activity
boring-cyborg commentedon Feb 4, 2026
Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.
wjddn279 commentedon Feb 5, 2026
Looks reasonable and interesting!
I try to fix it.
wjddn279 commentedon Feb 6, 2026
@pjavier29
I've created a PR to fix the issue. Could you take a look? I'd appreciate your review!
Also, please let me know if it can resolve your issue
pjavier29 commentedon Feb 6, 2026
@wjddn279
Are you missing the filter for the elements in the
assets: Collection[AssetModel]list? Because right now you're doing aSELECT *on the entireAssetActivetable, and new records might appear that you shouldn't be considering. Maybe you can filter in memory instead?wjddn279 commentedon Feb 9, 2026
@pjavier29
Yes, in my opinion, considering the usage scenario, I don't think additional filtering is necessary (refer to PR description), but in-memory filtering would be nice. However, I have a question. What was the main cause of the query performance degradation you observed?
In case of 1, the same (or very similar) network IO would have occurred when calling https://github.com/apache/airflow/blob/main/airflow-core/src/airflow/jobs/scheduler_job_runner.py#L2992.
If 2 is the problem, doing in-memory filtering in Python would be slower than the database if anything, not faster, wouldn't it?
pjavier29 commentedon Feb 9, 2026
@wjddn279
I don't think it's a network issue or a delay related to data volume. In my database, the
asset_eventtable is 1.3GB and theassettable is 6.8MB (16,000 records).The query reaches the database, but I think it gets blocked because it's too long. If you check the database for locks with this query:
SELECT
l.pid,
l.mode,
l.granted,
a.query,
a.state,
a.query_start
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'asset_event'::regclass
ORDER BY l.granted, l.pid;
you'll see that the query arrives successfully but seems to get stuck there. You wait an hour and the record continues. The query never finishes.
I don't think the in-memory filter is a problem because you already have those records in memory, and besides, filtering a list of 16000 records is not usually a problem for Python.
Regards
wjddn279 commentedon Feb 9, 2026
@pjavier29
The query you posted in the issue doesn't look related to
asset_event- did I miss something?If you observed that
asset_eventis large and experiencing lock contention, then that query doesn't seem to be the bottleneck.Nataneljpwd commentedon Feb 9, 2026
is not an issue compared to the amount of data you will get now without the filter, with a lot of assets it will be way worse than the current state
not that bad, planning does not take a long time
I think that either a proper query with a join (which is not that complex) is needed, or keep it as is and maybe paginate the request (though I think it will cause further performance degradation)
In memory python filtering will be slow, and I say that from experience, where I tried to do in memory filtering in python of SQL objects, which was way slower than doing it in sql
pjavier29 commentedon Feb 10, 2026
@wjddn279
That query is what I used to understand why the other query ""SELECT airflow.asset_active.name, airflow.asset_active.uri
FROM airflow.asset_activeWHERE (airflow.asset_active.name, airflow.asset_a"" was taking so long. That query shows you the locks on a specific table, in this case, the asset_active table.
Sorry, in the previous message I used the wrong table, I put 'asset_event' but it should be 'asset_active'.
Nataneljpwd commentedon Feb 18, 2026
Hello, I have created a pull request addressing the issue using a join and a cte, this should solve the issue
wjddn279 commentedon Feb 18, 2026
@choo121600 Could you please unassign me from this?
pjavier29 commentedon Feb 23, 2026
@Nataneljpwd
Hi,
do you have the link to the PR?
Regards
Nataneljpwd commentedon Feb 23, 2026
Sure, here you go
#62114
It is still a draft as some tests need to be fixed, hopefully I will get to it today
Nataneljpwd commentedon Feb 24, 2026
Hello, the pr is now ready for review, I need to fix a few mypy issues, other than that, everything is ready
Nataneljpwd commentedon Feb 24, 2026
Fixed the mypy issues, I would appreciate a review