[Bug]: Podcast library latency/timeouts due to cache thrashing and query contention (v2.33.0+) #3273

Open
opened 2026-04-25 00:14:40 +02:00 by adam · 3 comments
Owner

Originally created by @drwggm on GitHub (Mar 20, 2026).

What happened?

Describe the bug

Large podcast libraries experience severe latency and socket timeouts when loading the home page and recent episodes page. The root causes are:

  1. Podcast cron clears entire API cache every minutePodcastManager.runEpisodeCheck() saves lastEpisodeCheck and updatedAt on every check, even when no new episodes are found. Each save triggers afterUpdate hooks that call ApiCacheManager.clear(), wiping the entire cache. With podcasts staggered across each minute, the cache is effectively useless.

  2. Promise.all() causes SQLite lock contention — PR #5073 parallelized personalized shelf queries. SQLite uses transactionType: 'IMMEDIATE', so parallel queries fight over the write lock rather than running concurrently. This adds overhead without improving throughput.

  3. getRecentEpisodes query is too heavy — The /api/libraries/:id/recent-episodes endpoint runs a single query joining podcastEpisodes → podcasts → libraryItems with a LEFT JOIN on mediaProgresses, filtering on $mediaProgresses.isFinished$ with subQuery: false. SQLite must materialize the entire joined result set (~2500+ episodes) before applying LIMIT, causing timeouts.

What did you expect to happen?

Expected behavior

  • Routine lastEpisodeCheck timestamp updates should not invalidate the entire API cache
  • Personalized shelf queries should run sequentially on SQLite to avoid lock contention
  • The recent-episodes query should be lightweight enough to complete within a reasonable timeout

Steps to reproduce the issue

To Reproduce

  1. Have a podcast library with 50+ podcasts and 2000+ episodes
  2. Configure auto-download with staggered cron schedules
  3. Navigate to the podcast library home page
  4. Observe slow shelf loading (~2-3s+) and socket disconnects
  5. Navigate to "Recent Episodes" — page may fail to load entirely

Audiobookshelf version

v2.33.0 / v2.33.1

How are you running audiobookshelf?

Docker

What OS is your Audiobookshelf server hosted from?

Linux

If the issue is being seen in the UI, what browsers are you seeing the problem on?

None

Logs

[ApiCacheManager] podcast.afterUpdate: Clearing cache        ← happens every minute per cron group
[ApiCacheManager] libraryItem.afterUpdate: Clearing cache    ← happens every minute per cron group
Loaded 10 of 2521 episodes for "Newest Episodes" in 0.61s
Loaded 10 of 1987 items for "Listen/Read Again" in 0.73s
Loaded 4 personalized shelves in 0.98s

Additional Notes

It might be viewed as a performance issue, but in my setup, the recent change made the app and webapp unusable. Basically won't load anything.

The following fixes my issue:

Suggested fix

1. Selective cache invalidation for podcast/libraryItem afterUpdate (server/managers/ApiCacheManager.js)

Add 'podcast' and 'libraryItem' to highChurnModels. For these models, only afterUpdate hooks should use selective clearing (e.g., personalized shelf slices). afterCreate/afterDestroy (actual content changes) should still clear the full cache.

2. Revert Promise.all() to sequential queries (server/models/LibraryItem.js)

In getPersonalizedShelves(), revert both the book and podcast paths from Promise.all() back to sequential await calls. SQLite's file-level locking means parallelism adds contention overhead without throughput gains.

3. Split getRecentEpisodes into a two-step query (server/utils/queries/libraryItemsPodcastFilters.js)

  • Step 1: Lightweight ID query — findAll with attributes: ['id'] and minimal join attributes, applying filter/sort/limit to get only the needed episode IDs
  • Step 2: Hydration query — findAll with id IN (...) to fetch full metadata for only the selected episodes
Originally created by @drwggm on GitHub (Mar 20, 2026). ### What happened? ### Describe the bug Large podcast libraries experience severe latency and socket timeouts when loading the home page and recent episodes page. The root causes are: 1. **Podcast cron clears entire API cache every minute** — `PodcastManager.runEpisodeCheck()` saves `lastEpisodeCheck` and `updatedAt` on every check, even when no new episodes are found. Each save triggers `afterUpdate` hooks that call `ApiCacheManager.clear()`, wiping the entire cache. With podcasts staggered across each minute, the cache is effectively useless. 2. **`Promise.all()` causes SQLite lock contention** — PR #5073 parallelized personalized shelf queries. SQLite uses `transactionType: 'IMMEDIATE'`, so parallel queries fight over the write lock rather than running concurrently. This adds overhead without improving throughput. 3. **`getRecentEpisodes` query is too heavy** — The `/api/libraries/:id/recent-episodes` endpoint runs a single query joining `podcastEpisodes → podcasts → libraryItems` with a LEFT JOIN on `mediaProgresses`, filtering on `$mediaProgresses.isFinished$` with `subQuery: false`. SQLite must materialize the entire joined result set (~2500+ episodes) before applying LIMIT, causing timeouts. ### What did you expect to happen? ### Expected behavior - Routine `lastEpisodeCheck` timestamp updates should not invalidate the entire API cache - Personalized shelf queries should run sequentially on SQLite to avoid lock contention - The recent-episodes query should be lightweight enough to complete within a reasonable timeout ### Steps to reproduce the issue ### To Reproduce 1. Have a podcast library with 50+ podcasts and 2000+ episodes 2. Configure auto-download with staggered cron schedules 3. Navigate to the podcast library home page 4. Observe slow shelf loading (~2-3s+) and socket disconnects 5. Navigate to "Recent Episodes" — page may fail to load entirely ### Audiobookshelf version v2.33.0 / v2.33.1 ### How are you running audiobookshelf? Docker ### What OS is your Audiobookshelf server hosted from? Linux ### If the issue is being seen in the UI, what browsers are you seeing the problem on? None ### Logs ```shell [ApiCacheManager] podcast.afterUpdate: Clearing cache ← happens every minute per cron group [ApiCacheManager] libraryItem.afterUpdate: Clearing cache ← happens every minute per cron group Loaded 10 of 2521 episodes for "Newest Episodes" in 0.61s Loaded 10 of 1987 items for "Listen/Read Again" in 0.73s Loaded 4 personalized shelves in 0.98s ``` ### Additional Notes It might be viewed as a performance issue, but in my setup, the recent change made the app and webapp unusable. Basically won't load anything. The following fixes my issue: ### Suggested fix **1. Selective cache invalidation for podcast/libraryItem `afterUpdate`** (`server/managers/ApiCacheManager.js`) Add `'podcast'` and `'libraryItem'` to `highChurnModels`. For these models, only `afterUpdate` hooks should use selective clearing (e.g., personalized shelf slices). `afterCreate`/`afterDestroy` (actual content changes) should still clear the full cache. **2. Revert `Promise.all()` to sequential queries** (`server/models/LibraryItem.js`) In `getPersonalizedShelves()`, revert both the book and podcast paths from `Promise.all()` back to sequential `await` calls. SQLite's file-level locking means parallelism adds contention overhead without throughput gains. **3. Split `getRecentEpisodes` into a two-step query** (`server/utils/queries/libraryItemsPodcastFilters.js`) - **Step 1:** Lightweight ID query — `findAll` with `attributes: ['id']` and minimal join attributes, applying filter/sort/limit to get only the needed episode IDs - **Step 2:** Hydration query — `findAll` with `id IN (...)` to fetch full metadata for only the selected episodes
adam added the bug label 2026-04-25 00:14:40 +02:00
Author
Owner

@davidwilemski commented on GitHub (Mar 29, 2026):

I have ~2500 podcast episodes over ~30 shows on my install, some of which do have custom cron schedules. Though probably not that many recent episodes. I'm not seeing the performance problem you're seeing so I don't think a large library alone is sufficient to trigger this. I looked into this a bit (I have limited knowledge of audiobookshelf internals) and think this may be in part accidental due to the mediaProgresses index added in #5073.

In particular, I noticed on an explain plan for the getMediaFinished query ("Listen Again" shelf) seems like it probably got worse because sqlite is using the new index in this case for a condition of

WHERE 
  `mediaProgresses`.`isFinished` = 1 
ORDER BY 
  mediaProgresses.updatedAt DESC 

The query plan with the new media_progresses_user_item_finished_time index is:

QUERY PLAN
|--SEARCH mediaProgresses USING INDEX media_progresses_user_item_finished_time (userId=?)
|--SEARCH podcastEpisode USING INDEX sqlite_autoindex_podcastEpisodes_1 (id=?)
|--SEARCH podcast USING INDEX sqlite_autoindex_podcasts_1 (id=?)
|--SEARCH podcast->libraryItem USING INDEX library_items_media_id (mediaId=?)
`--USE TEMP B-TREE FOR ORDER BY

This indicates to me we're using that new index which means the db is finding all potentially matching items and then sorting them by updatedAt time afterwards in order to select the most recent 10.

Whereas if I modify the query manually to force using the older media_progresses_updated_at on the join to the media progresses, I see this query plan:

QUERY PLAN
|--SCAN mediaProgresses USING INDEX media_progresses_updated_at
|--SEARCH podcastEpisode USING INDEX sqlite_autoindex_podcastEpisodes_1 (id=?)
|--SEARCH podcast USING INDEX sqlite_autoindex_podcasts_1 (id=?)
`--SEARCH podcast->libraryItem USING INDEX library_items_media_id (mediaId=?)

I believe this one indicates we're instead traversing media progresses by updated at time so we only need to read as many progresses until find 10 that satisfy the isFinished condition. (This could still have pathlological query behavior if we had many progresses that were not finished).

I think a fix for that particular query problem would be to either modify the query logic in getFilteredPodcastEpisodes to force the old index (not sure if that's possible here) or to add another new index to mediaProgresses for this case.

The Newest Episodes query didn't have anything that stood out to me when I looked at the query / ran an explain so I'm less sure why that would be taking longer, it seems to have a good index

I'm also not quite sure you'd be seeing your query cache reset every minute? Or are you saying you have a cron item occuring every minute, thus invalidating your cache? If so, that seems like potentially aggressive feed refreshing behavior, even if all 50+ podcasts published new episodes daily.

@davidwilemski commented on GitHub (Mar 29, 2026): I have ~2500 podcast episodes over ~30 shows on my install, some of which do have custom cron schedules. Though probably not that many recent episodes. I'm not seeing the performance problem you're seeing so I don't think a large library alone is sufficient to trigger this. I looked into this a bit (I have limited knowledge of audiobookshelf internals) and think this may be in part accidental due to the `mediaProgresses` index added in #5073. In particular, I noticed on an explain plan for the `getMediaFinished` query ("Listen Again" shelf) seems like it probably got worse because sqlite is using the new index in this case for a condition of ``` WHERE `mediaProgresses`.`isFinished` = 1 ORDER BY mediaProgresses.updatedAt DESC ``` The query plan with the new `media_progresses_user_item_finished_time` index is: ``` QUERY PLAN |--SEARCH mediaProgresses USING INDEX media_progresses_user_item_finished_time (userId=?) |--SEARCH podcastEpisode USING INDEX sqlite_autoindex_podcastEpisodes_1 (id=?) |--SEARCH podcast USING INDEX sqlite_autoindex_podcasts_1 (id=?) |--SEARCH podcast->libraryItem USING INDEX library_items_media_id (mediaId=?) `--USE TEMP B-TREE FOR ORDER BY ``` This indicates to me we're using that new index which means the db is finding all potentially matching items and then sorting them by `updatedAt` time afterwards in order to select the most recent 10. Whereas if I modify the query manually to force using the older `media_progresses_updated_at` on the join to the media progresses, I see this query plan: ``` QUERY PLAN |--SCAN mediaProgresses USING INDEX media_progresses_updated_at |--SEARCH podcastEpisode USING INDEX sqlite_autoindex_podcastEpisodes_1 (id=?) |--SEARCH podcast USING INDEX sqlite_autoindex_podcasts_1 (id=?) `--SEARCH podcast->libraryItem USING INDEX library_items_media_id (mediaId=?) ``` I believe this one indicates we're instead traversing media progresses by updated at time so we only need to read as many progresses until find 10 that satisfy the `isFinished` condition. (This could still have pathlological query behavior if we had many progresses that were not finished). I think a fix for _that_ particular query problem would be to either modify the query logic in `getFilteredPodcastEpisodes` to force the old index (not sure if that's possible here) or to add _another_ new index to `mediaProgresses` for this case. The Newest Episodes query didn't have anything that stood out to me when I looked at the query / ran an explain so I'm less sure why that would be taking longer, it seems to have a good index I'm also not quite sure you'd be seeing your query cache reset every minute? Or are you saying you have a cron item occuring every minute, thus invalidating your cache? If so, that seems like potentially aggressive feed refreshing behavior, even if all 50+ podcasts published new episodes daily.
Author
Owner

@drwggm commented on GitHub (Mar 30, 2026):

Thanks for digging into this — the query plan analysis is really insightful and I think you're onto something important that I missed.

On the index issue: You're right that the media_progresses_user_item_finished_time index from #5073 could be causing SQLite's query planner to pick a worse plan for getMediaFinished. The EXPLAIN output is compelling — with the new index, SQLite searches by userId then has to sort by updatedAt via a temp B-tree, whereas the old media_progresses_updated_at index lets it scan in updatedAt order and short-circuit once it finds 10 finished items. That's a big difference for the "Listen Again" shelf, especially with lots of progress rows.

I didn't look at EXPLAIN plans at all — I was mostly going off observed latency and the log output. This is probably the more correct root cause for at least that shelf's slowness.

On the cache invalidation: My podcasts have staggered cron schedules, so different groups fire every minute throughout the hour. In PodcastManager.runEpisodeCheck(), every podcast gets lastEpisodeCheck and updatedAt saved regardless of whether new episodes were found. Each save fires afterUpdate hooks. In the upstream code, podcast and libraryItem aren't in highChurnModels, so each afterUpdate calls cache.clear(), wiping the entire API cache. With staggered schedules, there's essentially always a cron group firing, which means the cache is being cleared nearly every minute. It never lives long enough to serve a hit.

You're right that a large library alone isn't sufficient. The severity depends on how many podcasts have auto-download enabled and how their cron schedules are staggered. With 50+ podcasts on staggered schedules the cache is effectively always invalidated, which compounds any underlying query performance issues.

On the "Newest Episodes" query: It's possible that was more of a compounding effect from cache invalidation + the index issue rather than the query itself being inherently slow. I'd be curious to see if the index fix alone resolves it for others.

Would a composite index like (userId, isFinished, updatedAt) be the right shape to give SQLite a plan that can both filter on isFinished and scan in updatedAt order without a temp B-tree? That seems like a more targeted fix than my two-step query split for the getMediaFinished case at least.

@drwggm commented on GitHub (Mar 30, 2026): Thanks for digging into this — the query plan analysis is really insightful and I think you're onto something important that I missed. **On the index issue**: You're right that the `media_progresses_user_item_finished_time` index from #5073 could be causing SQLite's query planner to pick a worse plan for `getMediaFinished`. The `EXPLAIN` output is compelling — with the new index, SQLite searches by userId then has to sort by `updatedAt` via a temp B-tree, whereas the old media_progresses_updated_at index lets it scan in `updatedAt` order and short-circuit once it finds 10 finished items. That's a big difference for the "Listen Again" shelf, especially with lots of progress rows. I didn't look at `EXPLAIN` plans at all — I was mostly going off observed latency and the log output. This is probably the more correct root cause for at least that shelf's slowness. **On the cache invalidation**: My podcasts have staggered cron schedules, so different groups fire every minute throughout the hour. In `PodcastManager.runEpisodeCheck()`, every podcast gets `lastEpisodeCheck` and `updatedAt` saved regardless of whether new episodes were found. Each save fires `afterUpdate` hooks. In the upstream code, `podcast` and `libraryItem` aren't in `highChurnModels`, so each `afterUpdate` calls `cache.clear()`, wiping the entire API cache. With staggered schedules, there's essentially always a cron group firing, which means the cache is being cleared nearly every minute. It never lives long enough to serve a hit. You're right that a large library alone isn't sufficient. The severity depends on how many podcasts have auto-download enabled and how their cron schedules are staggered. With 50+ podcasts on staggered schedules the cache is effectively always invalidated, which compounds any underlying query performance issues. **On the "Newest Episodes" query**: It's possible that was more of a compounding effect from cache invalidation + the index issue rather than the query itself being inherently slow. I'd be curious to see if the index fix alone resolves it for others. Would a composite index like `(userId, isFinished, updatedAt)` be the right shape to give SQLite a plan that can both filter on `isFinished` and scan in `updatedAt `order without a temp B-tree? That seems like a more targeted fix than my two-step query split for the `getMediaFinished` case at least.
Author
Owner

@davidwilemski commented on GitHub (Mar 30, 2026):

Ah I see what you mean on the cache invalidation thing. That makes sense now, I haven't looked into how the cache works here but yes it does seem like invalidating only if something changes could help you some there, if that's not already what is happening, like you mentioned in the original report. The query performance you reported isn't great but sub one second should be fine if the query result is able to be cached for longer than it is now...

On the Listen Again query I had been thinking an index on (userId, mediaItemId, isFinished, updatedAt) (perhaps with updatedAt in specifically DESC order) would help here but I tried that on a copy of my database and the explain plan isn't looking better.

For reference, here's the query for the listen again section, with the fields being selected removed. I found this via logs with the QUERY_PROFILING env var set.

SELECT 
   --- <All Fields from Related Tables>
FROM 
  `podcastEpisodes` AS `podcastEpisode` 
  INNER JOIN `podcasts` AS `podcast` ON `podcastEpisode`.`podcastId` = `podcast`.`id` 
  INNER JOIN `libraryItems` AS `podcast->libraryItem` ON `podcast`.`id` = `podcast->libraryItem`.`mediaId` 
  AND (
    `podcast->libraryItem`.`libraryId` = '4f9d2e41-e896-4d06-a757-46b714a5ab0e' 
    AND `podcast->libraryItem`.`mediaType` = 'podcast'
  ) 
  INNER JOIN `mediaProgresses` AS `mediaProgresses` ON `podcastEpisode`.`id` = `mediaProgresses`.`mediaItemId` 
  AND (
    `mediaProgresses`.`userId` = '0ab38668-6060-4264-b3f7-5a2c3ada0cee' 
    AND `mediaProgresses`.`mediaItemType` = 'podcastEpisode'
  ) 
WHERE 
  `mediaProgresses`.`isFinished` = 1 
ORDER BY 
  mediaProgresses.updatedAt DESC 
LIMIT 
  0, 10;

It turns out when applying an index like this:

CREATE INDEX `media_progresses_user_item_finished_updated` ON `mediaProgresses` (`userId`, `mediaItemId`, `isFinished`, `updatedAt` DESC);

That I still see a plan that involves the temporary table sort:

QUERY PLAN
|--SEARCH mediaProgresses USING INDEX media_progresses_user_item_finished_updated (userId=?)
|--SEARCH podcastEpisode USING INDEX sqlite_autoindex_podcastEpisodes_1 (id=?)
|--SEARCH podcast USING INDEX sqlite_autoindex_podcasts_1 (id=?)
|--SEARCH podcast->libraryItem USING INDEX library_items_media_id (mediaId=?)
`--USE TEMP B-TREE FOR ORDER BY

I think to fully address this we'd need to add my index and restructure the query, perhaps in a way similar to what you mentioned in your first post. I think we would want to select the podcasts that have mediaProgresses with isFinished = 1 ordered by updatedAt as a sub-query/CTE that we then used to build the rest of the query that joins all the tables together and selects the rows we found from the CTE. I can't currently think of a way to do this fully in the join but maybe it's possible.

I'm not entirely sure how to rewrite the query to be in this format with the Sequelize library, I'm not experienced with it. I may give it a go.

I hesitate to suggest this, only do it if you're fully confident in your ability to manage this, but one thought to validate whether any further work in this area would help is to consider dropping the new media_progresses_user_item_finished_time index. I'm not a maintainer of this project so definitely take what I'm saying here with a heavy grain of salt but if I were to try it I would: Make a copy of my sqlite database file, drop the table on the original sqlite file, and see whether performance got better on my instance (possibly stopping ABS before dropping the index and restarting it after that was done). I think it should be possible to drop and re-add the index with no other harmful effects but that is what the backup copy of the DB would be for, just in case.

If performance did get better with the new index removed, then that at least that's a signal that we're on the right track. (though obviously that index was added for a different issue so it can't just be totally removed)

@davidwilemski commented on GitHub (Mar 30, 2026): Ah I see what you mean on the cache invalidation thing. That makes sense now, I haven't looked into how the cache works here but yes it does seem like invalidating only if something changes could help you some there, if that's not already what is happening, like you mentioned in the original report. The query performance you reported isn't _great_ but sub one second should be fine if the query result is able to be cached for longer than it is now... On the Listen Again query I had been thinking an index on `(userId, mediaItemId, isFinished, updatedAt)` (perhaps with updatedAt in specifically DESC order) would help here but I tried that on a copy of my database and the explain plan isn't looking better. For reference, here's the query for the listen again section, with the fields being selected removed. I found this via logs with the `QUERY_PROFILING` env var set. ```sql SELECT --- <All Fields from Related Tables> FROM `podcastEpisodes` AS `podcastEpisode` INNER JOIN `podcasts` AS `podcast` ON `podcastEpisode`.`podcastId` = `podcast`.`id` INNER JOIN `libraryItems` AS `podcast->libraryItem` ON `podcast`.`id` = `podcast->libraryItem`.`mediaId` AND ( `podcast->libraryItem`.`libraryId` = '4f9d2e41-e896-4d06-a757-46b714a5ab0e' AND `podcast->libraryItem`.`mediaType` = 'podcast' ) INNER JOIN `mediaProgresses` AS `mediaProgresses` ON `podcastEpisode`.`id` = `mediaProgresses`.`mediaItemId` AND ( `mediaProgresses`.`userId` = '0ab38668-6060-4264-b3f7-5a2c3ada0cee' AND `mediaProgresses`.`mediaItemType` = 'podcastEpisode' ) WHERE `mediaProgresses`.`isFinished` = 1 ORDER BY mediaProgresses.updatedAt DESC LIMIT 0, 10; ``` It turns out when applying an index like this: ```sql CREATE INDEX `media_progresses_user_item_finished_updated` ON `mediaProgresses` (`userId`, `mediaItemId`, `isFinished`, `updatedAt` DESC); ``` That I still see a plan that involves the temporary table sort: ``` QUERY PLAN |--SEARCH mediaProgresses USING INDEX media_progresses_user_item_finished_updated (userId=?) |--SEARCH podcastEpisode USING INDEX sqlite_autoindex_podcastEpisodes_1 (id=?) |--SEARCH podcast USING INDEX sqlite_autoindex_podcasts_1 (id=?) |--SEARCH podcast->libraryItem USING INDEX library_items_media_id (mediaId=?) `--USE TEMP B-TREE FOR ORDER BY ``` I _think_ to fully address this we'd need to add my index and restructure the query, perhaps in a way similar to what you mentioned in your first post. I think we would want to select the podcasts that have mediaProgresses with `isFinished = 1` ordered by `updatedAt` as a sub-query/CTE that we then used to build the rest of the query that joins all the tables together and selects the rows we found from the CTE. I can't currently think of a way to do this fully in the join but maybe it's possible. I'm not entirely sure how to rewrite the query to be in this format with the Sequelize library, I'm not experienced with it. I may give it a go. I hesitate to suggest this, only do it if you're fully confident in your ability to manage this, but one thought to validate whether any further work in this area would help is to consider dropping the new `media_progresses_user_item_finished_time` index. **I'm not a maintainer of this project so definitely take what I'm saying here with a heavy grain of salt** but if I were to try it I would: Make a copy of my sqlite database file, drop the table on the original sqlite file, and see whether performance got better on my instance (possibly stopping ABS before dropping the index and restarting it after that was done). I _think_ it should be possible to drop and re-add the index with no other harmful effects but that is what the backup copy of the DB would be for, just in case. If performance did get better with the new index removed, then that at least that's a signal that we're on the right track. (though obviously that index was added for a different issue so it can't just be totally removed)
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/audiobookshelf#3273