Environment
- Jellystat
unstable image, based on the current upstream unstable branch
- PostgreSQL 15.2
- Oracle ARM host with 2 vCPUs
- Library database with about 169,647 episode rows
Symptoms
jellystat-db reached 96% CPU while this query was running:
REFRESH MATERIALIZED VIEW js_library_items_with_playcount_playtime;
The refresh held a relation lock for more than 34 minutes. PostgreSQL logged this once per minute during the lock period:
skipping vacuum of "js_library_items_with_playcount_playtime" --- lock not available
The same container logs also showed sorry, too many clients already and connection timeouts from Jellystat.
Cause
The current backend/db.js starts a refresh for every jf_playback_activity bulk insert. The calls are not awaited or deduplicated, and the refresh uses the non-concurrent form. The expensive source view also contains a correlated episode-size subquery. Without indexes on jf_library_seasons."SeriesId" and jf_library_episodes."SeasonId", PostgreSQL planned a sequential scan of the episode table for each library item.
The planner estimated the source view at about 303 million cost units. After adding those join indexes, the estimate dropped to about 6.5 million.
Reproduction
- Use the current
unstable image with a populated database.
- Insert or update playback activity often enough to trigger the refresh path.
- Inspect
pg_stat_activity, container CPU, and the PostgreSQL logs while the materialized view refresh runs.
Proposed fix
I have a forked patch that:
- coalesces refresh requests already in flight;
- refreshes the materialized views sequentially;
- uses
REFRESH MATERIALIZED VIEW CONCURRENTLY for the two views with stable unique Id keys;
- adds the required unique materialized-view indexes;
- adds indexes for the source-view joins.
On the affected database, the three-view refresh batch completed in about 6 seconds after the patch, with no lingering refresh backend and near-zero idle CPU afterward. I will open a pull request with the patch after this issue is created.
Environment
unstableimage, based on the current upstreamunstablebranchSymptoms
jellystat-dbreached 96% CPU while this query was running:The refresh held a relation lock for more than 34 minutes. PostgreSQL logged this once per minute during the lock period:
The same container logs also showed
sorry, too many clients alreadyand connection timeouts from Jellystat.Cause
The current
backend/db.jsstarts a refresh for everyjf_playback_activitybulk insert. The calls are not awaited or deduplicated, and the refresh uses the non-concurrent form. The expensive source view also contains a correlated episode-size subquery. Without indexes onjf_library_seasons."SeriesId"andjf_library_episodes."SeasonId", PostgreSQL planned a sequential scan of the episode table for each library item.The planner estimated the source view at about 303 million cost units. After adding those join indexes, the estimate dropped to about 6.5 million.
Reproduction
unstableimage with a populated database.pg_stat_activity, container CPU, and the PostgreSQL logs while the materialized view refresh runs.Proposed fix
I have a forked patch that:
REFRESH MATERIALIZED VIEW CONCURRENTLYfor the two views with stable uniqueIdkeys;On the affected database, the three-view refresh batch completed in about 6 seconds after the patch, with no lingering refresh backend and near-zero idle CPU afterward. I will open a pull request with the patch after this issue is created.