starred/audiobookshelf
1
0
Fork 0
mirror of https://github.com/advplyr/audiobookshelf.git synced 2026-05-30 23:40:40 +02:00
Code Issues 1.0k Packages Projects Releases 10 Wiki Activity

[PR #5073] [MERGED] Improve personalized/discover query performance and cache invalidation behavior #4415

New Issue
Closed
opened 2026-04-25 00:19:39 +02:00 by adam · 0 comments
adam commented 2026-04-25 00:19:39 +02:00
Owner
Copy Link
Copy Source

📋 Pull Request Information

Original PR: https://github.com/advplyr/audiobookshelf/pull/5073
Author: @kevingatera
Created: 2/20/2026
Status: ✅ Merged
Merged: 3/12/2026
Merged by: @advplyr

Base: master ← Head: perf/minimal-upstream-patchset

📝 Commits (5)

  • 05d9ab8 Improve API cache invalidation for high-churn models
  • d2915e6 Speed up personalized shelves and reduce search payload size
  • f1a2e56 Add database indexes for discover query performance
  • c0319eb Adjust discover/search changes for API compatibility
  • e66ffb9 Add indexes to MediaProgress and BookSeries models

📊 Changes

6 files changed (+328 additions, -105 deletions)

View changed files

📝 server/managers/ApiCacheManager.js (+40 -1)
➕ server/migrations/v2.32.2-add-discover-query-indexes.js (+74 -0)
📝 server/models/BookSeries.js (+4 -0)
📝 server/models/LibraryItem.js (+131 -73)
📝 server/models/MediaProgress.js (+4 -0)
📝 server/utils/queries/libraryItemsBookFilters.js (+75 -31)

📄 Description

Brief summary

This PR improves API read performance (especially personalized/discover), reduces cache churn from high-frequency writes, and adds targeted DB indexes for the discover query path.

Which issue is fixed?

N/A (no linked issue yet)

In-depth Description

This started from investigating slow home screen loads tied to personalized/discover.

What changed:

  • Cache invalidation is now more targeted for high-churn models (session, mediaProgress, playbackSession, device) so frequent progress/session/device writes do not flush all API cache entries.
  • Personalized shelf loading was parallelized where shelf queries are independent.
  • Discover candidate queries were simplified to avoid expensive join-heavy filtering during candidate count/selection.
  • DB indexes were added for hot discover filters:
    • mediaProgresses(userId, mediaItemId, isFinished, currentTime)
    • bookSeries(seriesId, bookId)

Search payload compatibility note:

  • I reverted the search payload-minification change to preserve existing API behavior for third-party clients.
  • With compatibility preserved, search responses remain large in expected cases (around 2.4 MB in my probe for q=galaxy).

One note: I was unsure whether to include a package version bump only to force migration execution. I kept the migration changes in this patchset, but if maintainers prefer no version bump in this type of PR, I can adjust to match the project release/versioning flow.

How have you tested this?

I tested on a SQLite-backed deployment with repeated cold/warm probes and server-log timing checks, and then daily-drove the build for a couple of days.

Before vs after (representative)

  • Cold personalized (/api/libraries/:id/personalized?minified=1&include=rssfeed,numEpisodesIncomplete)

    • Before targeted discover/query improvements:
      • personalized: 12.38s worst observed
      • discover shelf: 10.35s worst observed
    • After query-path + index work:
      • cold cycles observed: 1.05s, 2.73s, 0.22s
      • discover shelf in those cycles: 0.90s, 2.35s, 0.19s

  • Additional direct API behavior after fixes

    • personalized cache-miss cases now often fall in sub-second to low single-digit seconds instead of prior double-digit spikes
    • warm personalized calls remain very fast

Correctness checks

  • Verified key endpoints still return 200 with expected response structure:
    • /api/libraries/:id/personalized
    • /api/libraries/:id/items
    • /api/libraries/:id/series
    • /api/libraries/:id/search
  • Verified search response keeps expanded shape (libraryFiles and media present on libraryItem) and observed ~2.4 MB payload in compatibility mode.
  • Verified migration execution and index creation using PRAGMA index_list/index_info.
  • Verified query planner usage with EXPLAIN QUERY PLAN (including use of the new mediaProgress index).

Screenshots

N/A - backend/server/query performance changes only.

🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.

## 📋 Pull Request Information **Original PR:** https://github.com/advplyr/audiobookshelf/pull/5073 **Author:** [@kevingatera](https://github.com/kevingatera) **Created:** 2/20/2026 **Status:** ✅ Merged **Merged:** 3/12/2026 **Merged by:** [@advplyr](https://github.com/advplyr) **Base:** `master` ← **Head:** `perf/minimal-upstream-patchset` --- ### 📝 Commits (5) - [`05d9ab8`](https://github.com/advplyr/audiobookshelf/commit/05d9ab81f98fffd8becbfca41aca22bf0e05dcf3) Improve API cache invalidation for high-churn models - [`d2915e6`](https://github.com/advplyr/audiobookshelf/commit/d2915e689fb6644fea7af3b216d1cfe7d8c24f0f) Speed up personalized shelves and reduce search payload size - [`f1a2e56`](https://github.com/advplyr/audiobookshelf/commit/f1a2e56054ab2c9e9ee9c518ee84b4b607ca5248) Add database indexes for discover query performance - [`c0319eb`](https://github.com/advplyr/audiobookshelf/commit/c0319ebbac6c5ba6f0473453b7ba0528036e2316) Adjust discover/search changes for API compatibility - [`e66ffb9`](https://github.com/advplyr/audiobookshelf/commit/e66ffb9c23cdc7f7b45538c443b1bc96828b1f7a) Add indexes to MediaProgress and BookSeries models ### 📊 Changes **6 files changed** (+328 additions, -105 deletions) <details> <summary>View changed files</summary> 📝 `server/managers/ApiCacheManager.js` (+40 -1) ➕ `server/migrations/v2.32.2-add-discover-query-indexes.js` (+74 -0) 📝 `server/models/BookSeries.js` (+4 -0) 📝 `server/models/LibraryItem.js` (+131 -73) 📝 `server/models/MediaProgress.js` (+4 -0) 📝 `server/utils/queries/libraryItemsBookFilters.js` (+75 -31) </details> ### 📄 Description ## Brief summary This PR improves API read performance (especially personalized/discover), reduces cache churn from high-frequency writes, and adds targeted DB indexes for the discover query path. ## Which issue is fixed? N/A (no linked issue yet) ## In-depth Description This started from investigating slow home screen loads tied to personalized/discover. What changed: - Cache invalidation is now more targeted for high-churn models (`session`, `mediaProgress`, `playbackSession`, `device`) so frequent progress/session/device writes do not flush all API cache entries. - Personalized shelf loading was parallelized where shelf queries are independent. - Discover candidate queries were simplified to avoid expensive join-heavy filtering during candidate count/selection. - DB indexes were added for hot discover filters: - `mediaProgresses(userId, mediaItemId, isFinished, currentTime)` - `bookSeries(seriesId, bookId)` Search payload compatibility note: - I reverted the search payload-minification change to preserve existing API behavior for third-party clients. - With compatibility preserved, search responses remain large in expected cases (around 2.4 MB in my probe for `q=galaxy`). One note: I was unsure whether to include a package version bump only to force migration execution. I kept the migration changes in this patchset, but if maintainers prefer no version bump in this type of PR, I can adjust to match the project release/versioning flow. ## How have you tested this? I tested on a SQLite-backed deployment with repeated cold/warm probes and server-log timing checks, and then daily-drove the build for a couple of days. Before vs after (representative) - Cold personalized (`/api/libraries/:id/personalized?minified=1&include=rssfeed,numEpisodesIncomplete`) - Before targeted discover/query improvements: - personalized: 12.38s worst observed - discover shelf: 10.35s worst observed - After query-path + index work: - cold cycles observed: 1.05s, 2.73s, 0.22s - discover shelf in those cycles: 0.90s, 2.35s, 0.19s - Additional direct API behavior after fixes - personalized cache-miss cases now often fall in sub-second to low single-digit seconds instead of prior double-digit spikes - warm personalized calls remain very fast Correctness checks - Verified key endpoints still return 200 with expected response structure: - `/api/libraries/:id/personalized` - `/api/libraries/:id/items` - `/api/libraries/:id/series` - `/api/libraries/:id/search` - Verified search response keeps expanded shape (`libraryFiles` and `media` present on `libraryItem`) and observed ~2.4 MB payload in compatibility mode. - Verified migration execution and index creation using `PRAGMA index_list/index_info`. - Verified query planner usage with `EXPLAIN QUERY PLAN` (including use of the new mediaProgress index). ## Screenshots N/A - backend/server/query performance changes only. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
adam added the pull-request label 2026-04-25 00:19:39 +02:00
adam closed this issue 2026-04-25 00:19:39 +02:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
authentication
backlog
bug
chapter editor
config-issue
ebooks
encoding/embedding
enhancement
help wanted
listening sessions & progress
planned
possible plugin
progress sync
pull-request
Mirrored from GitHub Pull Request
 sorting/filtering/searching
unable to reproduce
upload
users & permissions
waiting
No Label pull-request
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam (Adam Melkus)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/audiobookshelf#4415
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?