tuantran0910 opened a new issue, #77237:
URL: https://github.com/apache/airflow/issues/77237

   ### Under which category would you file this issue?
   
   Airflow Core
   
   ### Apache Airflow version
   
   3.4.0 (the same code is still on `main`)
   
   ### What happened and how to reproduce it?
   
   **Issue description**
   
   Since 3.4.0, the rendered-fields cleanup that runs after every task 
execution reads the whole attempt history of that task to delete about one row. 
Its cost grows with the number of runs the task has ever had and never levels 
off, because `task_instance` keeps growing unless `airflow db clean` is run.
   
   `RenderedTaskInstanceFields.delete_old_records()` → 
`_do_delete_old_records()` (`airflow/models/renderedtifields.py`) runs on every 
RTIF write when `[core] num_dag_runs_to_retain_rendered_fields > 0` (default 
30):
   
   ```sql
   DELETE FROM rtif_v2
   WHERE EXISTS (
     SELECT 1 FROM task_instance
     WHERE task_instance.id = rtif_v2.task_instance_id
       AND task_instance.dag_id = :dag_id
       AND task_instance.task_id = :task_id
       AND task_instance.run_id NOT IN (
         SELECT run_id FROM dag_run WHERE dag_id = :dag_id
         ORDER BY run_after DESC LIMIT 30))
   ```
   
   Before 3.4.0, `rendered_task_instance_fields` carried `dag_id`, `task_id` 
and `run_id`, so the same cleanup only touched the ~31 rows kept for that task. 
In 3.4.0 (#74222):
   
   - `rtif_v2` is keyed only by `task_instance_id`; its only indexes are 
`rtif_v2_pkey` and `rtif_v2_ti_uq (task_instance_id)`.
   - `task_instance_history` was merged into `task_instance`, so every retry is 
now a `task_instance` row.
   
   To find the rows for one task, PostgreSQL has to:
   
   1. read every `task_instance` row of `(dag_id, task_id)` through 
`ti_state_lkp`, one heap page per attempt;
   2. probe `rtif_v2_ti_uq` once per attempt.
   
   Almost all probes miss, because earlier cleanups already removed those rows.
   
   **Plan on stock 3.4.0 schema**
   
   PostgreSQL 16; task with 2,880 runs (15-minute schedule, 30 days) and ~12% 
retried, so 3,252 attempts; 30 runs retained:
   
   ```
   Delete on rtif_v2 (actual rows=1)
     Buffers: shared hit=12602
     ->  Nested Loop (actual rows=1)
           ->  Bitmap Heap Scan on task_instance (actual rows=3219)
                 Recheck Cond: ((dag_id = 'hot_dag') AND (task_id = 'task_07'))
                 Filter: (NOT (hashed SubPlan 1))
                 Heap Blocks: exact=2905
                 Buffers: shared hit=2942
                 ->  Bitmap Index Scan on ti_state_lkp (actual rows=3252)
                 SubPlan 1
                   ->  Limit (actual rows=30)
                         ->  Index Scan Backward using idx_dag_run_run_after on 
dag_run
                               Buffers: shared hit=5
           ->  Index Scan using rtif_v2_ti_uq on rtif_v2 (actual rows=0 
loops=3219)
                 Index Cond: (task_instance_id = task_instance.id)
                 Buffers: shared hit=9658
   ```
   
   That is **12,602 pages (~100 MB) to delete 1 row**: 2,905 heap pages of 
`task_instance` plus 9,658 pages of index probes into `rtif_v2`. The query runs 
once per task execution, so a single DAG with 40 tasks on a 15-minute schedule 
issues 3,840 of them a day.
   
   **Effect on a real deployment**
   
   Managed PostgreSQL 16, 2 vCPU / 4 GB RAM, after upgrading to 3.4.0:
   
   - **Disk reads:** they jumped right after the upgrade. This `DELETE FROM 
rtif_v2 …` became the top statement, at about 97% of database disk reads. Its 
working set does not fit in the buffer cache, so most of those pages come from 
disk.
   - **Latency:** each call took about 9 s on average.
   - **Effect on the UI and API server:** the API server's own queries queued 
behind these deletes, the UI became slow, and API-server health checks (10 s 
timeout) failed and restarted the pods.
   **Steps to reproduce**
   
   1. Run Airflow 3.4.0 on PostgreSQL with the default 
`num_dag_runs_to_retain_rendered_fields = 30`.
   2. Let any task accumulate a few thousand `task_instance` rows: a frequent 
schedule, retries and/or backfills.
   3. Run the cleanup statement above for that `(dag_id, task_id)` under 
`EXPLAIN (ANALYZE, BUFFERS)` inside `BEGIN … ROLLBACK`. The buffer count grows 
linearly with the task's history while only ~1 row is deleted.
   
   A self-contained synthetic dataset (1.75M `task_instance` rows, no Airflow 
needed) is in the details below.
   
   <details><summary>Synthetic dataset (PostgreSQL 16)</summary>
   
   ```sql
   CREATE TABLE dag_run (
     id serial PRIMARY KEY, dag_id varchar(250) NOT NULL, run_id varchar(250) 
NOT NULL,
     run_after timestamptz NOT NULL, state varchar(50), filler text,
     CONSTRAINT dag_run_dag_id_run_id_key UNIQUE (dag_id, run_id));
   CREATE TABLE task_instance (
     id uuid PRIMARY KEY, task_id varchar(250) NOT NULL, dag_id varchar(250) 
NOT NULL, run_id varchar(250) NOT NULL,
     map_index int NOT NULL DEFAULT -1, try_number int NOT NULL, state 
varchar(20), working_set boolean,
     start_date timestamptz, filler text);
   CREATE TABLE rtif_v2 (
     id uuid PRIMARY KEY, task_instance_id uuid NOT NULL,
     rendered_fields json NOT NULL, k8s_pod_yaml json,
     CONSTRAINT rtif_v2_ti_uq UNIQUE (task_instance_id),
     CONSTRAINT rtif_v2_ti_fkey FOREIGN KEY (task_instance_id) REFERENCES 
task_instance(id) ON DELETE CASCADE);
   
   -- 300 DAGs x 600 runs + one 15-minute DAG with 2,880 runs
   INSERT INTO dag_run (dag_id, run_id, run_after, state, filler)
   SELECT dag_id, 'scheduled__' || to_char(ra, 'YYYY-MM-DD"T"HH24:MI:SS'), ra, 
'success', repeat('x', 300)
   FROM (
     SELECT format('dag_%s', lpad(d::text, 4, '0')) dag_id,
            timestamptz '2026-10-19' + (r * ((d % 5) + 1) * interval '12 
minutes') + (d * interval '7 seconds') ra
     FROM generate_series(1, 300) d, generate_series(1, 600) r
     UNION ALL
     SELECT 'hot_dag', timestamptz '2026-10-19' + r * interval '15 minutes'
     FROM generate_series(1, 2880) r
   ) s ORDER BY ra;
   
   -- 8 tasks per DAG (40 for hot_dag); ~12% of task runs get a second attempt
   CREATE SEQUENCE ti_seq;
   INSERT INTO task_instance (id, task_id, dag_id, run_id, try_number, state, 
working_set, start_date, filler)
   SELECT ('01a1' || lpad(to_hex(nextval('ti_seq')), 28, '0'))::uuid, task_id, 
dag_id, run_id, try_number,
          'success', CASE WHEN try_number = max_try THEN true ELSE NULL END, 
run_after, repeat('y', 450)
   FROM (
     SELECT dr.dag_id, dr.run_id, dr.run_after, t.task_id, a.try_number,
            CASE WHEN hashtext(dr.run_id || t.task_id) % 8 = 0 THEN 2 ELSE 1 
END max_try
     FROM dag_run dr
     CROSS JOIN LATERAL (SELECT format('task_%s', lpad(n::text, 2, '0')) task_id
                         FROM generate_series(1, CASE WHEN dr.dag_id = 
'hot_dag' THEN 40 ELSE 8 END) n) t
     CROSS JOIN LATERAL generate_series(1, CASE WHEN hashtext(dr.run_id || 
t.task_id) % 8 = 0 THEN 2 ELSE 1 END) a(try_number)
   ) s ORDER BY run_after, dag_id, task_id, try_number;
   
   -- 3.4.0 indexes
   CREATE INDEX ti_dag_state ON task_instance (dag_id, state);
   CREATE INDEX ti_dag_run ON task_instance (dag_id, run_id);
   CREATE INDEX ti_state ON task_instance (state);
   CREATE INDEX ti_state_lkp ON task_instance (dag_id, task_id, run_id, state);
   CREATE UNIQUE INDEX task_instance_try_key ON task_instance (dag_id, task_id, 
run_id, map_index, try_number);
   CREATE UNIQUE INDEX task_instance_current_key ON task_instance (dag_id, 
task_id, run_id, map_index, working_set);
   CREATE INDEX idx_dag_run_dag_id ON dag_run (dag_id);
   CREATE INDEX idx_dag_run_run_after ON dag_run (run_after);
   CREATE INDEX dag_id_state ON dag_run (dag_id, state);
   
   -- steady state: rtif_v2 holds the newest 31 runs per task (run #31 is what 
the next cleanup deletes)
   INSERT INTO rtif_v2 (id, task_instance_id, rendered_fields)
   SELECT gen_random_uuid(), ti.id, json_build_object('sql', repeat('select 1; 
', 150), 'run', ti.run_id)
   FROM task_instance ti
   JOIN (SELECT dag_id, run_id, row_number() OVER (PARTITION BY dag_id ORDER BY 
run_after DESC) rn FROM dag_run) r
     ON r.dag_id = ti.dag_id AND r.run_id = ti.run_id
   WHERE r.rn <= 31;
   VACUUM ANALYZE;
   
   BEGIN;
   EXPLAIN (ANALYZE, BUFFERS)
   DELETE FROM rtif_v2 WHERE EXISTS (
     SELECT 1 FROM task_instance
     WHERE task_instance.id = rtif_v2.task_instance_id
       AND task_instance.dag_id = 'hot_dag' AND task_instance.task_id = 
'task_07'
       AND task_instance.run_id NOT IN (
         SELECT run_id FROM dag_run WHERE dag_id = 'hot_dag' ORDER BY run_after 
DESC LIMIT 30));
   ROLLBACK;
   ```
   
   </details>
   
   ### What you think should happen instead?
   
   The cleanup cost should depend only on the number of RTIF rows kept for the 
task (about `num_dag_runs_to_retain_rendered_fields`), as it did before 3.4.0, 
not on the task's full attempt history.
   
   **Proposed fix**
   
   Store `dag_id`, `task_id` and `run_id` on `rtif_v2` and index them. The 
cleanup can then filter `rtif_v2` directly:
   
   ```sql
   -- migration
   ALTER TABLE rtif_v2 ADD COLUMN dag_id …, ADD COLUMN task_id …, ADD COLUMN 
run_id …;
   UPDATE rtif_v2 r SET dag_id = ti.dag_id, task_id = ti.task_id, run_id = 
ti.run_id
     FROM task_instance ti WHERE ti.id = r.task_instance_id;   -- ~31 rows per 
task, cheap
   CREATE INDEX rtif_v2_dag_task_run ON rtif_v2 (dag_id, task_id, run_id);
   
   -- cleanup
   DELETE FROM rtif_v2
   WHERE dag_id = :dag_id AND task_id = :task_id
     AND run_id NOT IN (SELECT run_id FROM dag_run WHERE dag_id = :dag_id
                        ORDER BY run_after DESC LIMIT 30);
   ```
   
   - **No sync problem:** a task instance's `dag_id`, `task_id` and `run_id` 
never change after it is created, so the copies cannot drift.
   - **Same semantics:** `task_instance_id` stays the owner key and the FK with 
`ON DELETE CASCADE` is unchanged. The same rows are deleted, including all 
attempts and map indexes of a dropped run.
   - **MySQL:** the existing MySQL path (fetching the run IDs to keep first) 
works unchanged.
   
   Same synthetic dataset:
   
   | Variant | 15-minute task (3,252 attempts) | Task of a DAG with older runs 
(675 attempts) |
   |---|---|---|
   | 3.4.0 as is | 12,602 pages | 4,156 pages |
   | + `dag_run (dag_id, run_after)` index from #74481 | 12,602 | 2,573 |
   | + covering index on `task_instance (dag_id, task_id, run_id) INCLUDE (id)` 
| 12,602 (planner keeps `ti_state_lkp`) | 2,573 |
   | **Proposed: columns + index on `rtif_v2`** | **42** | **1,655** (72 with 
#74481) |
   
   Indexes on `task_instance` / `dag_run` alone cannot fix this: the 
per-attempt probes into `rtif_v2` remain.
   
   **Alternatives considered**
   
   - **Bound the candidate runs without a schema change**, e.g. only runs 
ranked 31…40 (`ORDER BY run_after DESC OFFSET 30 LIMIT 10`). It costs 92 / 130 
pages here. However, it misses stragglers, such as rows from backfilled old 
dates or rows written while the cleanup was disabled, so it needs a periodic 
sweep.
   - **Move the cleanup off the per-task path**, into the scheduler or `airflow 
db clean`. This is a bigger behaviour change.
   
   ### Operating System
   
   Linux (containers)
   
   ### Deployment
   
   Official Apache Airflow Helm Chart
   
   ### Apache Airflow Provider(s)
   
   _No response_
   
   ### Versions of Apache Airflow Providers
   
   Not relevant: the issue is in airflow-core.
   
   ### Official Helm Chart version
   
   Not Applicable
   
   ### Kubernetes Version
   
   Not Applicable
   
   ### Helm Chart configuration
   
   Not Applicable
   
   ### Docker Image customizations
   
   Not relevant: the issue is in airflow-core.
   
   ### Anything else?
   
   - **When it happens:** on every task execution, on PostgreSQL. It gets worse 
as history accumulates.
   - **Workaround:** `AIRFLOW__CORE__NUM_DAG_RUNS_TO_RETAIN_RENDERED_FIELDS=0` 
(makes `delete_old_records` return early), plus purging old `rtif_v2` rows 
separately (for example `airflow db clean --tables task_instance`; `rtif_v2` 
rows follow by cascade).
   - **Related:**
     - #74222 introduced `rtif_v2` and the merged `task_instance` history.
     - #60951 is the earlier cleanup performance work.
     - #74481 adds `idx_dag_run_dag_id_run_after` (3.5.0), which only speeds up 
the `dag_run` subquery.
     - #42928 and #24745 are older RTIF / cleanup performance reports.
   
   ### Are you willing to submit PR?
   
   - [x] 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]

Reply via email to