WAGERBABE DOCS
All Stories
2-4-database-query-optimization-indexingDoneEpic 2.4

Story 2.4: Database Query Optimization and Strategic Indexing

Status: done

Tasks

  • Task 1: Audit Existing Indexes and Query Patterns (AC: #1, #3)
    • 1.1: Query `pg_indexes` to document current index coverage
    • 1.2: Enable slow query logging in Supabase (threshold: 100ms)
    • 1.3: Identify top 10 slowest queries from application logs
    • 1.4: Map slow queries to missing indexes
    • 1.5: Document N+1 patterns in current codebase (search for lazy loading)
  • Task 2: Create Index Migration (AC: #1)
    • 2.1: Create Alembic migration `add_performance_indexes`
    • 2.2: Add users table indexes (email, role, created_at)
    • 2.3: Add bets table composite indexes (3 indexes)
    • 2.4: Add games table indexes including partial index for active games
    • 2.5: Add transactions table composite indexes
    • 2.6: Add agents table indexes
    • 2.7: Use `CREATE INDEX CONCURRENTLY` to avoid table locks
  • Task 3: Optimize Application Queries (AC: #2, #3)
    • 3.1: Refactor user bet queries to use explicit JOINs
    • 3.2: Add eager loading to agent customer relationship queries
    • 3.3: Optimize dashboard metrics with batched queries
    • 3.4: Add query hints where appropriate (e.g., index forcing)
    • 3.5: Ensure all queries specify ORDER BY to leverage index ordering
  • Task 4: Validate Index Usage (AC: #2, #5)
    • 4.1: Run EXPLAIN ANALYZE on all critical queries
    • 4.2: Document query plans showing index usage
    • 4.3: Verify no sequential scans on large tables
    • 4.4: Save before/after performance metrics
  • Task 5: Health Monitoring Integration (AC: #4)
    • 5.1: Add index statistics to `/health` endpoint
    • 5.2: Query `pg_stat_user_indexes` for usage data
    • 5.3: Implement index bloat detection query
    • 5.4: Document maintenance procedures (REINDEX, VACUUM)
  • Task 6: Testing and Load Validation (AC: #2, #5)
    • 6.1: Write integration tests for query performance assertions
    • 6.2: Run load test: 1,000 concurrent users for 10 minutes
    • 6.3: Verify p95 query time <50ms in load test results
    • 6.4: Confirm slow query count <5 per hour in logs

Progress

Tasks0/6
Acceptance Criteria0
Total Tasks6

Navigation

View Epic 2.4All Stories