seanmuth opened a new pull request, #65602:
URL: https://github.com/apache/airflow/pull/65602
## Summary
Adds three new migrations (0112–0114) targeting high-volume query patterns
on `task_instance` that currently cause full sequential scans on large
deployments.
### Indexes added
**`ti_state_updated_at`** (migration 0112) — partial index on `(state,
updated_at)`
filtered to terminal states (`success`, `failed`). Speeds up queries that
scan completed tasks by `updated_at` (e.g. OTel span cleanup, db-cleanup)
without indexing in-flight rows.
**`ti_span_status`** (migration 0113) — partial index on `(span_status)`
filtered to `span_status = 'should_end'`. Narrows the index to the small
fraction of rows the span-closing logic needs to find, avoiding full-table
scans on every span flush cycle.
**`ti_updated_at_dag_id`** (migration 0114) — full composite index on
`(updated_at, dag_id)`. Targets `GET /dags/~/dagRuns/~/taskInstances`
when called with an `updated_at` range filter. Without this index, the
query — which adds a `dag_id IN (...)` clause via `PermittedTIFilter` —
performs a full sequential scan on `task_instance`, observed at **~39s
avg latency** in RDS Performance Insights. `updated_at` is the leading
column so Postgres can bound the scan to the time window; `dag_id` is
the trailing column to narrow within that range without a heap fetch.
A partial index (like 0112) would not work here because this query
carries no `state` filter and must cover task instances in all states.
### Why not partial for 0114?
The `updated_at` polling query is issued by the UI/API for all task
states. A `WHERE state IN (...)` predicate would exclude in-progress
task instances from the results.
### Notes
- All three indexes use `postgresql_where`/`sqlite_where` for partial
semantics on supported backends where applicable.
- `CONCURRENTLY` is intentionally omitted from the migrations — Alembic
batch mode runs inside a transaction. Operators with large existing
tables who want a lock-free build can apply the indexes manually with
`CREATE INDEX CONCURRENTLY` before running `airflow db migrate`.
- Migration chain: 0111 → 0112 → 0113 → 0114
## Test plan
- [ ] `airflow db migrate` runs cleanly from a current `main` schema
- [ ] `airflow db downgrade` reverses each migration correctly
- [ ] Verify `EXPLAIN ANALYZE` on a loaded DB shows index scans for the
targeted queries after migration
- [ ] Existing task instance API tests pass (no schema regressions)
🤖 Generated with [Claude Code](https://claude.com/claude-code)
--
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]
