safaehar opened a new pull request, #74113: URL: https://github.com/apache/airflow/pull/74113
`airflow db clean --skip-archive` currently copies every selected batch into an archive table, deletes the source rows, and then drops the archive table. This still incurs the archive writes and requires schema `CREATE`/`DROP` privileges even though the archive is not preserved. This change gives `--skip-archive` a direct-delete path: - PostgreSQL and SQLite delete primary keys selected by the existing limited cleanup query. - MySQL uses a derived primary-key selection with `EXISTS`, avoiding MySQL's unsupported `LIMIT` in an `IN` subquery. - Each batch remains a separate transaction and retains the current selection, keep-last, composite-primary-key, rollback, and progress-output behavior. - Cleanup without `--skip-archive` continues to create and retain archive tables. The CLI help and cleanup documentation now state that `--skip-archive` deletes directly without creating archive tables. ## PostgreSQL WAL comparison On a disposable PostgreSQL 13 database, deleting 1,000,000 rows from the same two-column table and measuring with `pg_wal_lsn_diff(pg_current_wal_lsn(), :start_lsn)` produced: - archive path (`CREATE TABLE AS`, joined `DELETE`, `DROP`): 172,750,560 bytes - direct-delete path: 100,445,792 bytes The direct path generated approximately 42% less WAL in this comparison. ## Tests - `uv run --project airflow-core pytest airflow-core/tests/unit/utils/test_db_cleanup.py -xvs` (SQLite: 99 passed, 3 backend-specific skipped) - Focused PostgreSQL run covering skip-archive batching, keep-last, composite XCom keys, archive regression, and failure handling (11 passed) - `prek run --stage pre-commit --files airflow-core/src/airflow/utils/db_cleanup.py airflow-core/tests/unit/utils/test_db_cleanup.py airflow-core/src/airflow/cli/cli_config.py airflow-core/docs/howto/usage-cli.rst` closes: #42003 related: #73417 related: #67187 related: #28051 related: #66177 --- ##### Was generative AI tooling used to co-author this PR? - [X] Yes (Codex) Generated-by: Codex following [the guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#gen-ai-assisted-contributions) --- * Read the **[Pull Request Guidelines](https://github.com/apache/airflow/blob/main/contributing-docs/05_pull_requests.rst#pull-request-guidelines)** for more information. Note: commit author/co-author name and email in commits become permanently public when merged. * For fundamental code changes, an Airflow Improvement Proposal (AIP) is needed. * When adding dependency, check compliance with the ASF 3rd Party License Policy. -- 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]
