safaehar opened a new pull request, #75216: URL: https://github.com/apache/airflow/pull/75216
### Motivation `GET /api/v2/pools` currently computes six slot fields for every returned pool by calling ORM methods decorated with `@provide_session`. Each call opens its own session and issues a separate aggregate query, so the endpoint's query and transaction count grows linearly with the number of pools. In a profile with roughly 50 pools, one request produced 607 SQL operations, including 303 commits and 303 rollbacks. Database round trips accounted for about 3.2 seconds of the 3.6-second request. Because the UI calls this endpoint, that work delays pool-dependent rendering and adds avoidable metadata database load. ### Changes - Fetch all slot counts once with `Pool.slots_stats()` on the request session. - Build each list response from the precomputed statistics while preserving deferred-slot and unlimited-pool behavior. - Skip a pool if it is deleted between reading the page and reading the aggregate statistics. - Keep single-pool endpoints on their existing per-pool methods. - Add a regression test proving the list endpoint uses five queries for 5, 10, and 20 pools. The endpoint now performs five queries regardless of the number of pools returned, eliminating six additional queries and their session lifecycle overhead per pool. ### Testing - Pool route test file on SQLite, PostgreSQL, and MySQL: 84 passed on each backend. - `prek run --from-ref upstream/main --stage pre-commit` - `prek run --from-ref upstream/main --stage manual --skip compile-ui-assets-dev --skip view-skill-eval --skip run-skill-eval-codex` --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes — Codex (GPT-5) Generated-by: Codex (GPT-5) following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) --- Drafted-by: Codex (GPT-5) (no human review before posting) -- 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]
