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]