vatsrahul1001 opened a new issue, #63545: URL: https://github.com/apache/airflow/issues/63545
### Apache Airflow version main (development) ### If "Other Airflow 3 version" selected, which one? _No response_ ### What happened? Migration 0102_3_2_0_make_external_executor_id_text changes external_executor_id from VARCHAR(250) to TEXT on task_instance and task_instance_history. The upgrade direction is instant on PostgreSQL since TEXT is a superset of VARCHAR — no validation or rewrite needed. However, the downgrade (TEXT → VARCHAR(250)) requires PostgreSQL to: Scan every row to validate no values exceed 250 characters Rewrite the table to apply the narrower type constraint This acquires an ACCESS EXCLUSIVE lock on both tables for the full duration of the rewrite, blocking all reads and writes — including task state updates from running DAGs. Observed impact: With 10.7M task_instance rows (5.8 GB) and 10M task_instance_history rows (3.6 GB), the downgrade took ~8 minutes. Both tables were fully locked for the entire duration. Additional risk: If any external_executor_id value was stored with more than 250 characters after the upgrade (which the TEXT type now allows), the downgrade will fail with a data truncation error partway through the table rewrite — leaving the migration in a partially applied state. ### What you think should happen instead? Shadow column pattern (addresses lock duration): Following the precedent from migrations 0049 and 0055, add a new VARCHAR(250) column, copy data, drop old, rename. This avoids holding ACCESS EXCLUSIVE for the full rewrite: ### How to reproduce 1. Start Airflow on 3.1.8 with PostgreSQL backend 2. Run enough tasks to build up rows in task_instance and task_instance_history 3. Upgrade to 3.2.0 (airflow db migrate) 4. Run airflow db downgrade -n 3.1.8 5. Observe that migration 0102 takes several minutes with exclusive locks on both TI tables ### Operating System MAc ### 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 - [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md) -- 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]
