perf(db): Add missing database indexes for common query patterns #201

New issue

Closed

opened 2026-01-24 17:14:39 +00:00 by jack · 0 comments

jack commented

2026-01-24 17:14:39 +00:00

Owner

Problem

Viele häufig verwendete Query-Pfade haben keine passenden Indexes, was zu Full Table Scans führt - besonders problematisch bei wachsenden Datenmengen.

Hinweis: Dieses Issue verwendet die neuen Tabellennamen aus #197 (Database Schema Redesign).

Abhängigkeit

Sollte nach #197 umgesetzt werden, da dort die Tabellen umbenannt werden.

Fehlende Indexes

observations Tabelle

-- Für repo_path Filterung (Git Worktree Support)
CREATE INDEX idx_observations_repo_path ON observations(repo_path);

-- Für Projekt + Zeitbereich Queries (Analytics, Dashboard)
CREATE INDEX idx_observations_project_epoch ON observations(project, created_at_epoch DESC);

-- Für working_directory Filterung
CREATE INDEX idx_observations_working_dir ON observations(working_directory);

tasks Tabelle (vorher task_queue)

-- Für Worker Task Lookups
CREATE INDEX idx_tasks_worker_status ON tasks(assigned_worker_id, status);

-- Für Capability-basierte Abfragen
CREATE INDEX idx_tasks_capability_status ON tasks(required_capability, status, priority DESC);

documents Tabelle

-- Für Projekt + Source Kombinationen
CREATE INDEX idx_documents_project_source ON documents(project, source);

-- Für Cleanup-Queries (alte Dokumente)
CREATE INDEX idx_documents_last_accessed ON documents(last_accessed_epoch);

sessions Tabelle (vorher sdk_sessions)

-- Für Projekt-Listen mit Sortierung
CREATE INDEX idx_sessions_project_started ON sessions(project, started_at_epoch DESC);

Auswirkung

Query	Ohne Index	Mit Index
Observations by repo_path	O(n) scan	O(log n)
Analytics by project + time	O(n) scan	O(log n)
Task dispatch	O(n) scan	O(log n)

Bei 10.000+ Observations kann das den Unterschied zwischen 100ms und 5ms ausmachen.

Implementierung

1. Entity-Klassen mit Index-Dekoratoren aktualisieren

// packages/database/src/entities/Observation.ts
@Entity({ tableName: 'observations' })
@Index({ properties: ['repoPath'] })
@Index({ properties: ['project', 'createdAtEpoch'] })
@Index({ properties: ['workingDirectory'] })
export class Observation {
  // ...
}

// packages/database/src/entities/Task.ts
@Entity({ tableName: 'tasks' })
@Index({ properties: ['assignedWorkerId', 'status'] })
@Index({ properties: ['requiredCapability', 'status', 'priority'] })
export class Task {
  // ...
}

2. Migration mit MikroORM CLI generieren

cd packages/database

# Migration mit Schema-Diff erstellen (automatisch aus Entity-Änderungen)
npx mikro-orm migration:create --name=add_performance_indexes

# Output:
# Migration20260126000001_add_performance_indexes.ts successfully created

# Pending Migrations anzeigen
npx mikro-orm migration:pending

# ┌──────────────────────────────────────────────────┐
# │ Name                                             │
# ├──────────────────────────────────────────────────┤
# │ Migration20260126000001_add_performance_indexes  │
# └──────────────────────────────────────────────────┘

3. Generierte Migration prüfen

Die generierte Migration sollte etwa so aussehen:

// Migration20260126000001_add_performance_indexes.ts
import { Migration } from '@mikro-orm/migrations';

export class Migration20260126000001_add_performance_indexes extends Migration {
  override async up(): Promise<void> {
    this.addSql('CREATE INDEX `idx_observations_repo_path` ON `observations` (`repo_path`)');
    this.addSql('CREATE INDEX `idx_observations_project_epoch` ON `observations` (`project`, `created_at_epoch` DESC)');
    this.addSql('CREATE INDEX `idx_observations_working_dir` ON `observations` (`working_directory`)');
    this.addSql('CREATE INDEX `idx_tasks_worker_status` ON `tasks` (`assigned_worker_id`, `status`)');
    this.addSql('CREATE INDEX `idx_tasks_capability_status` ON `tasks` (`required_capability`, `status`, `priority`)');
    this.addSql('CREATE INDEX `idx_documents_project_source` ON `documents` (`project`, `source`)');
    this.addSql('CREATE INDEX `idx_documents_last_accessed` ON `documents` (`last_accessed_epoch`)');
    this.addSql('CREATE INDEX `idx_sessions_project_started` ON `sessions` (`project`, `started_at_epoch` DESC)');
  }

  override async down(): Promise<void> {
    this.addSql('DROP INDEX `idx_observations_repo_path`');
    this.addSql('DROP INDEX `idx_observations_project_epoch`');
    this.addSql('DROP INDEX `idx_observations_working_dir`');
    this.addSql('DROP INDEX `idx_tasks_worker_status`');
    this.addSql('DROP INDEX `idx_tasks_capability_status`');
    this.addSql('DROP INDEX `idx_documents_project_source`');
    this.addSql('DROP INDEX `idx_documents_last_accessed`');
    this.addSql('DROP INDEX `idx_sessions_project_started`');
  }
}

4. Migration ausführen

# Migration anwenden
npx mikro-orm migration:up

# Output:
# Processing 'Migration20260126000001_add_performance_indexes'
# Applied 'Migration20260126000001_add_performance_indexes'
# Successfully migrated up to the latest version

# Alle ausgeführten Migrations auflisten
npx mikro-orm migration:list

Akzeptanzkriterien

Entity-Klassen mit @Index() Dekoratoren versehen
Migration mit npx mikro-orm migration:create generiert
Down-Migration zum Entfernen vorhanden
Query Performance vor/nach gemessen
Keine Breaking Changes

## Problem Viele häufig verwendete Query-Pfade haben keine passenden Indexes, was zu Full Table Scans führt - besonders problematisch bei wachsenden Datenmengen. > **Hinweis:** Dieses Issue verwendet die neuen Tabellennamen aus #197 (Database Schema Redesign). ## Abhängigkeit Sollte nach #197 umgesetzt werden, da dort die Tabellen umbenannt werden. ## Fehlende Indexes ### observations Tabelle ```sql -- Für repo_path Filterung (Git Worktree Support) CREATE INDEX idx_observations_repo_path ON observations(repo_path); -- Für Projekt + Zeitbereich Queries (Analytics, Dashboard) CREATE INDEX idx_observations_project_epoch ON observations(project, created_at_epoch DESC); -- Für working_directory Filterung CREATE INDEX idx_observations_working_dir ON observations(working_directory); ``` ### tasks Tabelle (vorher task_queue) ```sql -- Für Worker Task Lookups CREATE INDEX idx_tasks_worker_status ON tasks(assigned_worker_id, status); -- Für Capability-basierte Abfragen CREATE INDEX idx_tasks_capability_status ON tasks(required_capability, status, priority DESC); ``` ### documents Tabelle ```sql -- Für Projekt + Source Kombinationen CREATE INDEX idx_documents_project_source ON documents(project, source); -- Für Cleanup-Queries (alte Dokumente) CREATE INDEX idx_documents_last_accessed ON documents(last_accessed_epoch); ``` ### sessions Tabelle (vorher sdk_sessions) ```sql -- Für Projekt-Listen mit Sortierung CREATE INDEX idx_sessions_project_started ON sessions(project, started_at_epoch DESC); ``` ## Auswirkung | Query | Ohne Index | Mit Index | |-------|------------|-----------| | Observations by repo_path | O(n) scan | O(log n) | | Analytics by project + time | O(n) scan | O(log n) | | Task dispatch | O(n) scan | O(log n) | Bei 10.000+ Observations kann das den Unterschied zwischen 100ms und 5ms ausmachen. ## Implementierung ### 1. Entity-Klassen mit Index-Dekoratoren aktualisieren ```typescript // packages/database/src/entities/Observation.ts @Entity({ tableName: 'observations' }) @Index({ properties: ['repoPath'] }) @Index({ properties: ['project', 'createdAtEpoch'] }) @Index({ properties: ['workingDirectory'] }) export class Observation { // ... } // packages/database/src/entities/Task.ts @Entity({ tableName: 'tasks' }) @Index({ properties: ['assignedWorkerId', 'status'] }) @Index({ properties: ['requiredCapability', 'status', 'priority'] }) export class Task { // ... } ``` ### 2. Migration mit MikroORM CLI generieren ```bash cd packages/database # Migration mit Schema-Diff erstellen (automatisch aus Entity-Änderungen) npx mikro-orm migration:create --name=add_performance_indexes # Output: # Migration20260126000001_add_performance_indexes.ts successfully created # Pending Migrations anzeigen npx mikro-orm migration:pending # ┌──────────────────────────────────────────────────┐ # │ Name │ # ├──────────────────────────────────────────────────┤ # │ Migration20260126000001_add_performance_indexes │ # └──────────────────────────────────────────────────┘ ``` ### 3. Generierte Migration prüfen Die generierte Migration sollte etwa so aussehen: ```typescript // Migration20260126000001_add_performance_indexes.ts import { Migration } from '@mikro-orm/migrations'; export class Migration20260126000001_add_performance_indexes extends Migration { override async up(): Promise<void> { this.addSql('CREATE INDEX `idx_observations_repo_path` ON `observations` (`repo_path`)'); this.addSql('CREATE INDEX `idx_observations_project_epoch` ON `observations` (`project`, `created_at_epoch` DESC)'); this.addSql('CREATE INDEX `idx_observations_working_dir` ON `observations` (`working_directory`)'); this.addSql('CREATE INDEX `idx_tasks_worker_status` ON `tasks` (`assigned_worker_id`, `status`)'); this.addSql('CREATE INDEX `idx_tasks_capability_status` ON `tasks` (`required_capability`, `status`, `priority`)'); this.addSql('CREATE INDEX `idx_documents_project_source` ON `documents` (`project`, `source`)'); this.addSql('CREATE INDEX `idx_documents_last_accessed` ON `documents` (`last_accessed_epoch`)'); this.addSql('CREATE INDEX `idx_sessions_project_started` ON `sessions` (`project`, `started_at_epoch` DESC)'); } override async down(): Promise<void> { this.addSql('DROP INDEX `idx_observations_repo_path`'); this.addSql('DROP INDEX `idx_observations_project_epoch`'); this.addSql('DROP INDEX `idx_observations_working_dir`'); this.addSql('DROP INDEX `idx_tasks_worker_status`'); this.addSql('DROP INDEX `idx_tasks_capability_status`'); this.addSql('DROP INDEX `idx_documents_project_source`'); this.addSql('DROP INDEX `idx_documents_last_accessed`'); this.addSql('DROP INDEX `idx_sessions_project_started`'); } } ``` ### 4. Migration ausführen ```bash # Migration anwenden npx mikro-orm migration:up # Output: # Processing 'Migration20260126000001_add_performance_indexes' # Applied 'Migration20260126000001_add_performance_indexes' # Successfully migrated up to the latest version # Alle ausgeführten Migrations auflisten npx mikro-orm migration:list ``` ## Akzeptanzkriterien - [ ] Entity-Klassen mit `@Index()` Dekoratoren versehen - [ ] Migration mit `npx mikro-orm migration:create` generiert - [ ] Down-Migration zum Entfernen vorhanden - [ ] Query Performance vor/nach gemessen - [ ] Keine Breaking Changes