Migration 0102 downgrade holds exclusive lock on task_instance for extended period during TEXT→VARCHAR(250) rewrite #63545
Description
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
- Start Airflow on 3.1.8 with PostgreSQL backend
- Run enough tasks to build up rows in task_instance and task_instance_history
- Upgrade to 3.2.0 (airflow db migrate)
- Run airflow db downgrade -n 3.1.8
- 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
- I agree to follow this project's Code of Conduct