Performance: Implement DataLoader for GraphQL N+1 Query Optimization

Summary

The GraphQL API has extensive N+1 query problems causing severe performance issues. A comprehensive analysis has identified 50+ N+1 patterns across 23 resolver files.

Impact: A typical query for 20 characters triggers 81+ database queries instead of ~5.

Analysis Document

Full analysis available in: .tmp/dataloader-analysis.md

Key Findings

• ❌ NO existing DataLoader implementations
• 🔴 Critical N+1 issues in Characters, Media, Users, Species resolvers
• 📊 Estimated 90% reduction in database queries with DataLoader
• ⚡ 5-10x performance improvement expected

Worst Offenders

1. CharactersResolver: 81+ queries for 20 characters (owner, creator, species, mainMedia, speciesVariant)
2. MediaResolver: 61+ queries for 20 media items (owner, character, gallery, image, textContent)
3. CommentsResolver: 61+ queries for 20 comments (author, parent, replies, commentable entity)
4. GalleriesResolver: 61+ queries for 20 galleries (owner, character, counts)

Recommended Implementation Plan

Phase 1: Core DataLoaders (High Priority)

• UserDataLoader - Used everywhere (owner, creator, author fields)
• SpeciesDataLoader - Used by Characters, Traits, Variants
• CharacterDataLoader - Used by Media, Galleries, Comments
• MediaDataLoader - Used by Characters (mainMedia)

Phase 2: Supporting DataLoaders

• CommunityDataLoader
• RoleDataLoader
• TraitDataLoader
• ImageDataLoader
• GalleryDataLoader

Phase 3: Batch Loaders for Collections

• TagsByCharacterLoader
• TagsByMediaLoader
• TraitsBySpeciesLoader
• EnumValuesByTraitLoader

Example Impact

Current Query:

query {
  characters(first: 20) {
    nodes {
      owner { username }
      species { name }
    }
  }
}

• Before: 1 + 20 (owners) + 20 (species) = 41 queries
• After: 1 + 1 (batched owners) + 1 (batched species) = 3 queries
• Improvement: 93% reduction

Files Affected

All resolver files need updates:

• apps/backend/src/characters/characters.resolver.ts
• apps/backend/src/media/media.resolver.ts
• apps/backend/src/users/users.resolver.ts
• apps/backend/src/species/species.resolver.ts
• apps/backend/src/traits/traits.resolver.ts
• apps/backend/src/galleries/galleries.resolver.ts
• apps/backend/src/comments/comments.resolver.ts
• And 16 more...

References

• Full analysis: .tmp/dataloader-analysis.md
• DataLoader Documentation
• NestJS GraphQL DataLoader Guide

---

Priority: High
Effort: 2-4 weeks
Impact: Critical performance improvement

Start with .tmp/dataloader-analysis.md, then inspect the listed resolver entry points such as apps/backend/src/characters/characters.resolver.ts and apps/backend/src/media/media.resolver.ts. The work spans the three proposed loader phases and 23 resolver files; done means the identified N+1 patterns are addressed and the representative characters query approaches the documented three-query result.