Mintplex-Labs/anything-llm
1
0
Fork 0
mirror of https://github.com/Mintplex-Labs/anything-llm.git synced 2026-07-19 22:23:50 -04:00
Code Issues 328 Packages Projects Releases 10 Wiki Activity

[GH-ISSUE #4048] Backend Performance: Database Query Optimization & Indexing #2576

New Issue
Closed
opened 2026-02-22 18:30:20 -05:00 by yindo · 0 comments
yindo commented 2026-02-22 18:30:20 -05:00
Owner
Copy Link
Copy Source

Originally created by @jezweb on GitHub (Jun 26, 2025).
Original GitHub issue: https://github.com/Mintplex-Labs/anything-llm/issues/4048

Overview

This issue tracks database performance optimizations needed in the AnythingLLM backend.

Related to upstream discussion: https://github.com/Mintplex-Labs/anything-llm/issues/2797

Current Issues

  1. Many models use multiple sequential queries instead of joins
  2. Missing database indexes on frequently queried fields
  3. No query result caching for frequently accessed data

Proposed Solutions

1. Query Optimization

  • Implement Prisma query optimization with include and select statements
  • Example: Workspace queries currently fetch relations separately

2. Database Indexes

Add indexes in /server/prisma/schema.prisma:

  • workspace_chats.workspaceId + createdAt (compound index)
  • document_vectors.docId
  • cache_data.name + expiresAt
  • workspace_documents.workspaceId
  • users.email (unique index exists, but optimize for lookups)

3. Query Result Caching

  • Implement query result caching for:
    • Workspace metadata (TTL: 5 minutes)
    • User permissions (TTL: 5 minutes)
    • Document counts (TTL: 1 minute)

4. Denormalization for Performance

Add computed columns:

  • workspaces.document_count
  • workspaces.total_vectors
  • users.total_chats

Implementation Steps

  1. Analyze current query patterns with query logging
  2. Create migration for new indexes
  3. Update Prisma schema with new indexes
  4. Implement query caching layer
  5. Add computed columns with triggers/scheduled updates
  6. Performance testing before/after

Performance Impact

  • Expected 50-70% reduction in query time for workspace operations
  • Reduced database load for repeated queries
  • Better scalability for large deployments

Labels: enhancement, performance, backend, database

Originally created by @jezweb on GitHub (Jun 26, 2025). Original GitHub issue: https://github.com/Mintplex-Labs/anything-llm/issues/4048 ## Overview This issue tracks database performance optimizations needed in the AnythingLLM backend. Related to upstream discussion: https://github.com/Mintplex-Labs/anything-llm/issues/2797 ## Current Issues 1. Many models use multiple sequential queries instead of joins 2. Missing database indexes on frequently queried fields 3. No query result caching for frequently accessed data ## Proposed Solutions ### 1. Query Optimization - Implement Prisma query optimization with `include` and `select` statements - Example: Workspace queries currently fetch relations separately ### 2. Database Indexes Add indexes in `/server/prisma/schema.prisma`: - `workspace_chats.workspaceId` + `createdAt` (compound index) - `document_vectors.docId` - `cache_data.name` + `expiresAt` - `workspace_documents.workspaceId` - `users.email` (unique index exists, but optimize for lookups) ### 3. Query Result Caching - Implement query result caching for: - Workspace metadata (TTL: 5 minutes) - User permissions (TTL: 5 minutes) - Document counts (TTL: 1 minute) ### 4. Denormalization for Performance Add computed columns: - `workspaces.document_count` - `workspaces.total_vectors` - `users.total_chats` ## Implementation Steps 1. Analyze current query patterns with query logging 2. Create migration for new indexes 3. Update Prisma schema with new indexes 4. Implement query caching layer 5. Add computed columns with triggers/scheduled updates 6. Performance testing before/after ## Performance Impact - Expected 50-70% reduction in query time for workspace operations - Reduced database load for repeated queries - Better scalability for large deployments Labels: enhancement, performance, backend, database
yindo closed this issue 2026-02-22 18:30:20 -05:00
yindo changed title from Backend Performance: Database Query Optimization & Indexing to [GH-ISSUE #4048] Backend Performance: Database Query Optimization & Indexing 2026-06-05 14:47:21 -04:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
Desktop
Docker
Integration Request
Integration Request
OS: Linux
OS: Mobile
OS: Windows
UI/UX
blocked
bug
bug
core-team-only
documentation
duplicate
embed-widget
enhancement
feature request
github_actions
good first issue
investigating
needs info / can't replicate
possible bug
pull-request
Mirrored from GitHub Pull Request
 question
stage: specifications
wontfix
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
yindo
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: Mintplex-Labs/anything-llm#2576
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?