lablup / lablup/backend.ai

Missing index on sessions.replica_id (FK ON DELETE SET NULL) makes routings deletion pathologically slow

Open
#12,700 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.