You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "GaneshPatil7517 (via GitHub)" <gi...@apache.org> on 2026/01/09 02:44:03 UTC
[PR] Add missing indexes on dag_version_id columns for db cleanup performance [airflow]
GaneshPatil7517 opened a new pull request, #60307:
URL: https://github.com/apache/airflow/pull/60307
## What does this PR do?
Adds database indexes on `task_instance.dag_version_id` and `dag_run.created_dag_version_id` to fix the slow performance of `airflow db clean -t dag_version` command.
## Why is this needed?
When running `airflow db clean -t dag_version --batch-size 1000`, the cleanup process was taking ~6 minutes per batch on tables with 300K+ rows. The root cause was missing indexes on the foreign key columns that reference `dag_version.id`.
The cleanup code in `db_cleanup.py` defines `dag_version` with `dependent_tables=["task_instance", "dag_run"]`, meaning every delete operation needs to check both tables for FK violations. Without indexes, PostgreSQL performs full table scans.
## What changed?
1. **New migration** (`0098_3_2_0_add_dag_version_id_indexes_for_db_cleanup.py`):
- Adds `idx_task_instance_dag_version_id` on `task_instance(dag_version_id)`
- Adds `idx_dag_run_created_dag_version_id` on `dag_run(created_dag_version_id)`
2. **Updated ORM models**: Added indexes to `__table_args__` for schema consistency
## Performance Impact
| Scenario | Before | After |
|----------|--------|-------|
| `db clean -t dag_version` (300K rows, batch=1000) | ~6 min/batch | < 2 min total |
Fixes #60145
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Add missing indexes on dag_version_id columns for db cleanup performance [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #60307:
URL: https://github.com/apache/airflow/pull/60307#issuecomment-4151445522
This pull request has been automatically marked as stale because it has not had recent activity. It will be closed in 5 days if no further activity occurs. Thank you for your contributions.
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Add missing indexes on dag_version_id columns for db cleanup performance [airflow]
Posted by "boring-cyborg[bot] (via GitHub)" <gi...@apache.org>.
boring-cyborg[bot] commented on PR #60307:
URL: https://github.com/apache/airflow/pull/60307#issuecomment-3726800536
Congratulations on your first Pull Request and welcome to the Apache Airflow community! If you have any issues or are unsure about any anything please check our Contributors' Guide (https://github.com/apache/airflow/blob/main/contributing-docs/README.rst)
Here are some useful points:
- Pay attention to the quality of your code (ruff, mypy and type annotations). Our [prek-hooks]( https://github.com/apache/airflow/blob/main/contributing-docs/08_static_code_checks.rst#prerequisites-for-prek-hooks) will help you with that.
- In case of a new feature add useful documentation (in docstrings or in `docs/` directory). Adding a new operator? Check this short [guide](https://github.com/apache/airflow/blob/main/airflow-core/docs/howto/custom-operator.rst) Consider adding an example DAG that shows how users should use it.
- Consider using [Breeze environment](https://github.com/apache/airflow/blob/main/dev/breeze/doc/README.rst) for testing locally, it's a heavy docker but it ships with a working Airflow and a lot of integrations.
- Be patient and persistent. It might take some time to get a review or get the final approval from Committers.
- Please follow [ASF Code of Conduct](https://www.apache.org/foundation/policies/conduct) for all communication including (but not limited to) comments on Pull Requests, Mailing list and Slack.
- Be sure to read the [Airflow Coding style]( https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#coding-style-and-best-practices).
- Always keep your Pull Requests rebased, otherwise your build might fail due to changes not related to your commits.
Apache Airflow is a community-driven project and together we are making it better 🚀.
In case of doubts contact the developers at:
Mailing List: dev@airflow.apache.org
Slack: https://s.apache.org/airflow-slack
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Add missing indexes on dag_version_id columns for db cleanup performance [airflow]
Posted by "benrifkind (via GitHub)" <gi...@apache.org>.
benrifkind commented on PR #60307:
URL: https://github.com/apache/airflow/pull/60307#issuecomment-3891808188
@GaneshPatil7517 any chance you could rebase and get this PR merged?
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Add missing indexes on dag_version_id columns for db cleanup performance [airflow]
Posted by "Copilot (via GitHub)" <gi...@apache.org>.
Copilot commented on code in PR #60307:
URL: https://github.com/apache/airflow/pull/60307#discussion_r2691415811
##########
airflow-core/src/airflow/migrations/versions/0099_3_2_0_add_dag_version_id_indexes_for_db_cleanup.py:
##########
@@ -0,0 +1,75 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+"""
+Add indexes on dag_version_id columns for db cleanup performance.
+
+When running `airflow db clean -t dag_version`, the cleanup process needs to
+check for foreign key references in task_instance and dag_run tables. Without
+indexes on these columns, this results in full table scans that can take several
+minutes per batch on tables with hundreds of thousands of rows.
+
+This migration adds the missing indexes to speed up the cleanup operation.
+
+See: https://github.com/apache/airflow/issues/60145
+
+Revision ID: 62fb1d0a1252
+Revises: 0b112f49112d
Review Comment:
The docstring comment states "Revises: 0b112f49112d" but the code correctly sets down_revision to "e79fc784f145". The docstring should be updated to match the actual down_revision value to avoid confusion.
```suggestion
Revises: e79fc784f145
```
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Add missing indexes on dag_version_id columns for db cleanup performance [airflow]
Posted by "GaneshPatil7517 (via GitHub)" <gi...@apache.org>.
GaneshPatil7517 commented on PR #60307:
URL: https://github.com/apache/airflow/pull/60307#issuecomment-3891833096
ya give me some time ill work on it...
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] Add missing indexes on dag_version_id columns for db cleanup performance [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed pull request #60307: Add missing indexes on dag_version_id columns for db cleanup performance
URL: https://github.com/apache/airflow/pull/60307
--
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: commits-unsubscribe@airflow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org