Skip to content

perf(notes): reduce list response size and N+1 queries for faster Notes tab#21549

Closed
leandroyloli wants to merge 1 commit intoopen-webui:devfrom
leandroyloli:perf/notes-lightweight-list-and-batch
Closed

perf(notes): reduce list response size and N+1 queries for faster Notes tab#21549
leandroyloli wants to merge 1 commit intoopen-webui:devfrom
leandroyloli:perf/notes-lightweight-list-and-batch

Conversation

@leandroyloli
Copy link
Contributor

@leandroyloli leandroyloli commented Feb 17, 2026

Pull Request Checklist

Note to first-time contributors: Please open a discussion post in Discussions to discuss your idea/fix with the community before creating a pull request, and describe your changes before submitting a pull request.

Before submitting, make sure you've checked the following:

  • Target branch: Verify that the pull request targets the dev branch. PRs targeting main will be immediately closed.
  • Description: Provide a concise description of the changes made in this pull request down below.
  • Changelog: Ensure a changelog entry following the format of Keep a Changelog is added at the bottom of the PR description.
  • Documentation: Add docs in Open WebUI Docs Repository. Document user-facing behavior, environment variables, public APIs/interfaces, or deployment steps.
  • Dependencies: Are there any new or upgraded dependencies? If so, explain why, update the changelog/docs, and include any compatibility notes. Actually run the code/function that uses updated library to ensure it doesn't crash.
  • Testing: Perform manual tests to verify the implemented fix/feature works as intended AND does not break any other functionality. Include reproducible steps to demonstrate the issue before the fix. Test edge cases (URL encoding, HTML entities, types). Take this as an opportunity to make screenshots of the feature/fix and include them in the PR description.
  • Agentic AI Code: Confirm this Pull Request is not written by any AI Agent or has at least gone through additional human review AND manual testing. If any AI Agent is the co-author of this PR, it may lead to immediate closure of the PR.
  • Code review: Have you performed a self-review of your code, addressing any coding standard issues and ensuring adherence to the project's coding standards?
  • Design & Architecture: Prefer smart defaults over adding new settings; use local state for ephemeral UI logic. Open a Discussion for major architectural or UX changes.
  • Git Hygiene: Keep PRs atomic (one logical change). Clean up commits and rebase on dev to ensure no unrelated commits (e.g. from main) are included. Push updates to the existing PR branch instead of closing and reopening.
  • Title Prefix: To clearly categorize this pull request, prefix the pull request title using one of the following:
    • perf: Performance improvement

Changelog Entry

Description

The Notes tab becomes very slow when users have many notes. The list/search endpoints (get_notes, search_notes, get_notes_by_user_id) return the full data field (markdown, HTML, JSON, files, versions) for every note. For ~60 notes per page this results in a ~167 MB response and ~27 second load time. Additionally, each note triggers a separate DB query for access grants (N+1 problem).

This PR reduces list payload from ~167 MB to ~10 KB and eliminates the N+1 grant queries, making the Notes tab load in seconds instead of tens of seconds.

Added

  • get_grants_by_resources() in AccessGrantsTable for batch grant loading (single query instead of N separate queries)
  • _to_note_models_batch() in NoteTable for batch note-to-model conversion using the batch grant loader
  • _strip_data_for_list() in NoteTable to keep only a 200-character markdown preview in list/grid responses

Changed

  • get_notes(), search_notes(), and get_notes_by_user_id() now use batch conversion + data stripping for lightweight list responses
  • downloadHandler in Notes.svelte now fetches the full note via getNoteById() before generating TXT/MD/PDF files, since the list response no longer contains full content

Deprecated

  • N/A

Removed

  • N/A

Fixed

  • Notes tab extremely slow load time with many notes (~27s, ~167 MB payload for 60 notes)
  • N+1 query problem where each note in a list triggered a separate DB query for access grants

Security

  • N/A

Breaking Changes

  • N/A — GET /notes/:id still returns full data; only list endpoints are affected, and the frontend download flow was updated to compensate

Additional Information

  • The GET /notes/:id endpoint and update_note_by_id are not modified — they still return full data for the note editor
  • The 200-character preview is sufficient for the grid view preview line (line-clamp-3)
  • No new dependencies were added
  • No database schema changes
  • getNoteById already existed in src/lib/apis/notes/index.ts — only the import in Notes.svelte was added

Contributor License Agreement

By submitting this pull request, I confirm that I have read and fully agree to the Contributor License Agreement (CLA), and I am providing my contributions under its terms.

Note

Deleting the CLA section will lead to immediate closure of your PR and it will not be merged in.

@leandroyloli @cursoragent
perf(notes): reduce list response size and avoid N+1 queries
d553176 
- Add get_grants_by_resources() for batch grant loading
- Add _to_note_models_batch() and _strip_data_for_list() in notes model
- List endpoints return only 200-char md preview; GET /notes/:id unchanged
- Notes list: fetch full note via getNoteById before download (txt/md/pdf)
- Reduces list payload from ~167MB to ~10KB for 60 notes, fixes slow Notes tab

Co-authored-by: Cursor <cursoragent@cursor.com>
@pr-validator-bot
Copy link

pr-validator-bot commented Feb 17, 2026

👋 Welcome and Thank You for Contributing!

We appreciate you taking the time to submit a pull request to Open WebUI!

⚠️ Important: Testing Requirements

We've recently seen an increase in PRs that have significant issues:

  • PRs that don't actually fix the bug they claim to fix
  • PRs that don't implement the feature they describe
  • PRs that break existing functionality
  • PRs that are clearly AI-generated without proper testing being done by the author
  • PRs that simply don't work as intended

These untested PRs consume significant time from maintainers and volunteer contributors who review and test PRs in their free time.
Time that could be spent testing other PRs or improving Open WebUI in other ways.

Before marking your PR as "Ready for Review":

Please explicitly confirm:

  1. ✅ You have personally tested ALL changes in this PR
  2. ✅ How you tested it (specific steps you took to verify it works)
  3. ✅ Visual evidence where applicable (screenshots or videos showing the feature/fix working) - if applicable to your specific PR

If you're not certain your PR works exactly as intended, please leave it in DRAFT mode until you've thoroughly tested it.

Thank you for helping us maintain quality and respecting the time of our community! 🙏

@pr-validator-bot
Copy link

pr-validator-bot commented Feb 17, 2026

⚠️ Warning: Possible Non-Atomic / Scope Creep PR Detected

Your PR was subjected to automated review by AI to determine if it could fall under Open WebUI's non-atomicity ruleset or scope creep.

This PR appears to contain multiple unrelated changes that could be split into separate pull requests.

🔍 AI Analysis Summary

Primary Intent: Performance optimization: Fix N+1 query problem when loading notes by batch-fetching access grants, and reduce payload size for list views by truncating content

Secondary Changes Detected:

  • Bug fix: Download handler now fetches full note content instead of using truncated preview data from list view
  • Backend utility: New get_grants_by_resources method for batch queries
📝 Detailed Analysis and Full Report (click to expand)

This PR contains two distinct units of work: (1) A performance optimization for the notes list view - adding batch query for access grants to fix N+1, and truncating content for list payloads. (2) A bug fix in the frontend where the download functionality was using truncated data (first 200 chars) instead of full content. The frontend change requires the new API to work properly, but the performance optimization in the backend could have been merged independently. The download bug fix ('Pre-factor test': could the list optimization have been merged first? YES - the list view changes don't require the download fix) should have been a separate PR. Additionally, the new get_grants_by_resources method in access_grants.py is a reusable utility that could be justified as its own PR, but it's primarily in service of the batch optimization. The PR violates atomicity by bundling a feature change (optimization) with a bug fix (download uses full data).

Why Atomic PRs With Narrow Scopes Matter

Atomic PRs (single-purpose PRs) are:

  • Easier to review - Reviewers can focus on one thing at a time
  • Easier to test - Each change can be verified independently
  • Easier to revert - If something breaks, we can revert just the problematic change
  • Faster to merge - Smaller, focused PRs get reviewed and merged quicker

What Makes a PR Atomic / Narrow in Scope?

An atomic PR should contain one semantic change:

  • ✅ Just one bug fix (even if it touches multiple files)
  • ✅ Just one feature (even if it requires changes across multiple files)
  • ✅ Just i18n/translation updates
  • ✅ Just documentation updates
  • ✅ Just refactoring of one specific thing
  • ✅ Just one performance improvement

What To Do

This is an automated analysis. If you believe this assessment is incorrect and your PR is actually atomic (all changes serve one unified purpose), please explain in a comment below.

Consider splitting this PR into separate, focused pull requests. Each PR should address one specific thing.

For example, if you have a bug fix and a new feature, submit them as two separate PRs.

@leandroyloli
Copy link
Contributor Author

leandroyloli commented Feb 17, 2026

Testing Confirmation

✅ I have personally tested ALL changes in this PR.

How I tested

This fix originated from a real production scenario where the Notes tab was unusably slow with ~60 notes (many from meeting audio transcriptions), resulting in ~167 MB responses and ~27 second load times.

Steps taken:

  1. Before the fix: Opened the Notes tab with ~60 notes → observed ~167 MB network response and ~27s load time in browser DevTools.
  2. After the fix — List loading: Opened the Notes tab → confirmed response dropped to ~10 KB and loaded in under 2 seconds. Verified both list and grid views render correctly with the 200-char markdown preview.
  3. After the fix — Grid preview: Switched to grid display → confirmed preview text still renders properly (line-clamp-3 with the 200-char preview).
  4. After the fix — Download TXT: From the notes list, clicked the menu on a note → Download as TXT → confirmed the downloaded file contains the full note content (not just the 200-char preview).
  5. After the fix — Download MD: Same as above for Markdown download → full content present.
  6. After the fix — Download PDF: Same as above for PDF download → full content rendered correctly.
  7. After the fix — Single note view: Clicked into a note to open the editor → confirmed full data is still returned by GET /notes/:id and the editor works normally.
  8. After the fix — Edit and save: Edited a note's content and saved → confirmed update_note_by_id still works correctly with full data.
  9. Regression check: Verified search, pagination (infinite scroll), sorting, and filtering (All / Created by you / Shared with you) all still work correctly.

@leandroyloli
Copy link
Contributor Author

leandroyloli commented Feb 17, 2026

This PR is atomic — all changes serve a single unified purpose: make the Notes list endpoints lightweight for performance.

The AI analysis incorrectly flagged the download handler change as a "secondary bug fix" and get_grants_by_resources as a "secondary utility." Here's why they are integral parts of the same change:

  1. _strip_data_for_list() removes full content from list responses — this is the core performance fix.
  2. downloadHandler fetching via getNoteById() — this is a direct consequence of (1), not a separate bug fix. Since the list no longer contains full content, the download handler must fetch the full note before generating TXT/MD/PDF. Without this change in the same PR, downloads from the list would be broken. Splitting it into a separate PR would introduce a regression.
  3. get_grants_by_resources() batch method — this is the mechanism that enables the N+1 fix. _to_note_models_batch() calls it to load grants for all notes in one query. It cannot exist independently; it only makes sense as part of this performance optimization.

All three pieces are required together for the optimization to work without regressions. Splitting them would either break functionality or leave the N+1 problem unsolved.

@tjbck
Copy link
Contributor

tjbck commented Feb 19, 2026

Addressed in dev to apply to all workspace items, thanks!

@tjbck tjbck closed this Feb 19, 2026
@leandroyloli
Copy link
Contributor Author

leandroyloli commented Feb 20, 2026

Addressed in dev to apply to all workspace items, thanks!

I am so happy!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@leandroyloli @pr-validator-bot @tjbck