Add the missing foreign-key indexes #1860

Closed
opened 2026-11-10 15:26:30 +00:00 by sam · 1 comment
Owner

Six foreign keys have no index on the referencing column, so deleting the parent scans the child table: identity_aliases and identity_subjects on identity_id, memberships and org_agents on org_id, installed_package_requirements on (org_id, required_name), and tasks on handoff_from_task_id.

Wanted: one index per foreign key that needs it. The small tables matter little today, but tasks.handoff_from_task_id grows with task volume.

Done when: each listed foreign key has a supporting index, and the migration that adds them passes the integrity checks.

Six foreign keys have no index on the referencing column, so deleting the parent scans the child table: identity_aliases and identity_subjects on identity_id, memberships and org_agents on org_id, installed_package_requirements on (org_id, required_name), and tasks on handoff_from_task_id. Wanted: one index per foreign key that needs it. The small tables matter little today, but `tasks.handoff_from_task_id` grows with task volume. Done when: each listed foreign key has a supporting index, and the migration that adds them passes the integrity checks.
Author
Owner

Already fixed on main by 0033933a (perf(store): index the six unindexed foreign keys). Migration 0074_fk_indexes.sql adds one index per listed key: identity_aliases(identity_id), identity_subjects(identity_id), memberships(org_id), org_agents(org_id), installed_package_requirements(org_id, required_name), tasks(handoff_from_task_id). No further change needed.

Already fixed on main by 0033933a (perf(store): index the six unindexed foreign keys). Migration 0074_fk_indexes.sql adds one index per listed key: identity_aliases(identity_id), identity_subjects(identity_id), memberships(org_id), org_agents(org_id), installed_package_requirements(org_id, required_name), tasks(handoff_from_task_id). No further change needed.
sam closed this issue 2026-11-13 16:46:57 +00:00
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: sam/archie#1860