Missing index on sessions.replica_id (FK ON DELETE SET NULL) makes routings deletion pathologically slow
- Dominant language
- Python
- Stars
- 670
- Forks
- 183
- Avg merge
- 17h 7m
- Merged PRs (30d)
- 358
Description
## Summary
`sessions.replica_id` has a foreign key to `routings(id)` with `ON DELETE SET NULL` (`fk_sessions_replica_id_routings`), but there is **no index on** `sessions.replica_id`. Every `DELETE` on `routings` therefore triggers a sequential scan of `sessions` **per deleted row** to find referencing rows to null out.
## Observed impact
During an operational cleanup of accumulated dead `routings` rows (see BA-6768 / BA-6342 residue), a batched `DELETE` of 200k rows could not finish within 10 minutes and had to be terminated — the FK trigger was doing ~200k × full scans of a 35k-row `sessions` table. After creating the index manually (`CREATE INDEX CONCURRENTLY ix_sessions_replica_id ON sessions (replica_id);` — built in ~200ms), the same 200k-row batches completed in seconds each (~1.9M rows deleted in under a minute total).
Any future route eviction/cleanup (e.g. `RouteEvictionHandler`, retention jobs, or manual purges) hits the same cliff on clusters with accumulated routes.
## Proposed fix
Add an index on `sessions.replica_id` to the model + an Alembic migration (`CREATE INDEX CONCURRENTLY` for the upgrade path where supported). As a general rule, FK columns with `ON DELETE SET NULL` / `CASCADE` semantics on hot-delete parents should carry an index.
JIRA Issue: BA-6808
Contributor guide
Research direction
The issue identifies the sessions model and an Alembic migration as the main entry points; start by locating the sessions.replica_id foreign key and nearby migration conventions. Check the RouteEvictionHandler and retention-job deletion paths, then verify that the upgrade creates the index with the supported concurrent approach and that route deletions no longer trigger repeated sequential scans.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100