Missing indexes cause expensive/slow materialized view refreshes and periodic high CPU load

[Ellimistdev]

Describe the bug

Sustained high Postgres CPU usage (single core pegged ~80%) on the host. Traced this to two related schema/query issues:

1. Several columns that are frequently joined/filtered on in Jellystat's core tables (jf_playback_activity, jf_library_episodes, jf_library_seasons) have no indexes beyond their primary key, causing full sequential table scans on every reference.
2. js_library_stats_overview and js_latest_playback_activity (materialized views) refresh on every playback activity event rather than on a fixed schedule - during active playback, ActivityMonitor polling at 1s intervals can trigger refreshes faster than the previous refresh completes. Since REFRESH MATERIALIZED VIEW takes a lock, subsequent refreshes queue up and their reported duration balloons (observed up to ~23s for js_library_stats_overview under back-to-back triggering).

Both js_library_stats_overview and js_latest_playback_activity internally reference a plain (non-materialized) view, jf_playback_activity_with_metadata, which re-runs a full join across jf_playback_activity, jf_library_episodes, and jf_library_items on every reference - including once via an unfiltered window function (ROW_NUMBER() OVER (PARTITION BY ...)) with no WHERE clause at all.

Environment Details (please complete the following information):

• OS: Ubuntu (Proxmox LXC container)
• Browser: N/A - backend/database issue, not UI-specific
• Jellystat Version: cyfershepard/jellystat:latest (pulled ~Aug 2026)
• Jellyfin Version: not directly relevant to this issue
• (Additional) Jellystat DB: postgres:15.2 (official image, default schema as created by Jellystat)
• (Additional) Library size at time of investigation: Jellyfin reports ~8.6k movies / 763 series / 40.1k episodes; jf_library_episodes table contained 108,678 live rows (including archived/stale entries - see note below on archived-row accumulation), jf_library_seasons ~7.1k rows, jf_playback_activity ~1.8k rows.

To Reproduce
Steps to reproduce the behavior:

1. Run Jellystat against a moderately sized library (10k+ episodes) with periodic active playback.
2. Watch Postgres CPU usage in top/docker stats during and after playback sessions.
3. Enable Postgres statement logging to see refresh timing:

   docker exec jellystat-db psql -U postgres -d jfstat -c "ALTER SYSTEM SET log_min_duration_statement = 0;"
   docker exec jellystat-db psql -U postgres -d jfstat -c "SELECT pg_reload_conf();"
   docker logs -f jellystat-db

4. Observe REFRESH MATERIALIZED VIEW statements firing on essentially every playback activity write, with durations climbing under concurrent triggering.
5. Check pg_stat_user_tables for scan patterns:

   docker exec jellystat-db psql -U postgres -d jfstat -c "SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 10;"

Expected behavior

Materialized view refreshes should complete quickly, and not compound under concurrent triggering, and core join columns should be indexed so refreshes don't require full sequential scans of the affected tables.

Screenshots

N/A - this is a backend performance issue, not a visual bug.

Task Logs

Jellystat container logs show ActivityMonitor polling Jellyfin /sessions at 1s intervals during active playback, with each activity write apparently triggering a downstream materialized view refresh:

[ActivityMonitor] Switching to active polling mode (1000ms)
New Data Inserted:  1
Activity inserted/updated Count:  1
[ActivityMonitor] Switching to idle polling mode (5000ms)

Container Logs

Postgres statement logs (before any indexes added), showing refreshes stacking and durations climbing from sub-second to 6-23s within a ~30 second window as later refreshes queued behind lock-held earlier ones:

23:20:21.977  REFRESH MATERIALIZED VIEW js_latest_playback_activity            559 ms
23:20:28.035  REFRESH MATERIALIZED VIEW js_library_items_with_playcount_playtime  6,617 ms
23:20:31.213  REFRESH MATERIALIZED VIEW js_library_stats_overview              9,792 ms
23:20:31.671  REFRESH MATERIALIZED VIEW js_latest_playback_activity           10,171 ms
23:20:34.279  REFRESH MATERIALIZED VIEW js_library_items_with_playcount_playtime 12,830 ms
23:20:40.115  REFRESH MATERIALIZED VIEW js_library_stats_overview             18,697 ms
23:20:40.734  REFRESH MATERIALIZED VIEW js_library_items_with_playcount_playtime 19,235 ms
23:20:44.905  REFRESH MATERIALIZED VIEW js_library_stats_overview             23,405 ms

pg_stat_user_tables output showing extremely high seq_tup_read relative to n_live_tup, i.e., repeated full table scans:

relname               | seq_scan  | seq_tup_read    | idx_scan   | n_live_tup
jf_library_episodes    | 1,095,263 | 105,058,674,190 | 13,651,078 | 108,678
jf_library_seasons     |   850,062 |   5,977,705,168 |    137,656 |   7,126
jf_playback_activity    |   111,261 |     185,969,923 |        685 |   1,812

Additional context

Root cause in the view definitions:

• jf_playback_activity_with_metadata (plain view, not materialized) joins on jf_playback_activity."EpisodeId"/"SeasonId", jf_library_episodes."EpisodeId"/"SeasonId", and jf_library_items."Id" = jf_playback_activity."NowPlayingItemId" - none of these columns are indexed on jf_playback_activity (only the PK on Id exists).
• jf_library_items_with_playcount_playtime (also a plain view feeding the js_ materialized view) left-joins jf_playback_activity on "NowPlayingItemId" and jf_library_seasons → jf_library_episodes → jf_item_info on "SeriesId"/"SeasonId"/"EpisodeId" - again unindexed on the activity/episodes/seasons side.
• jf_library_episodes and jf_library_seasons only have a PK index (Id), despite SeriesId/SeasonId/EpisodeId being used constantly for joins throughout Jellystat's own views.

Fix tested locally (workaround, not a PR):

CREATE INDEX idx_jf_library_episodes_seriesid ON jf_library_episodes ("SeriesId");
CREATE INDEX idx_jf_library_episodes_seasonid ON jf_library_episodes ("SeasonId");
CREATE INDEX idx_jf_library_episodes_episodeid_seasonid ON jf_library_episodes ("EpisodeId", "SeasonId");
CREATE INDEX idx_jf_library_seasons_seriesid ON jf_library_seasons ("SeriesId");
CREATE INDEX idx_jf_playback_activity_episodeid_seasonid ON jf_playback_activity ("EpisodeId", "SeasonId");
CREATE INDEX idx_jf_playback_activity_nowplayingitemid ON jf_playback_activity ("NowPlayingItemId");
CREATE INDEX idx_jf_playback_activity_activitydateinserted ON jf_playback_activity ("ActivityDateInserted");

Results (before → after, statement-logged refresh durations):

Materialized view	Before	After
js_latest_playback_activity	0.5s – 10.2s	0.4s
js_library_stats_overview	4.9s – 23.4s	1.7s
js_library_items_with_playcount_playtime	4.3s – 19.2s	4.4s (no change - see below)

seq_scan/idx_scan ratios on jf_library_episodes and jf_library_seasons also flipped decisively toward index scans after adding the indexes.

js_library_items_with_playcount_playtime didn't improve because its cost isn't join-related - it's a GROUP BY/COUNT/SUM aggregation across the full item set (16k+ rows) plus a correlated subquery per row for size fallback (jf_item_info lookup via jf_library_seasons/jf_library_episodes when Size isn't directly available on the item). That's inherent aggregation cost rather than a missing-index problem.

Suggested upstream fixes:

1. Add the above indexes (or equivalent) to the schema migration/init SQL so fresh and existing installs get them automatically.
2. Consider debouncing/throttling refreshes of js_library_stats_overview / js_latest_playback_activity / js_library_items_with_playcount_playtime so they don't re-trigger on every single playback activity tick during active sessions - e.g. a short cooldown (10–30s) between refreshes per view, since a single active playback session can currently cause several stacked/queued refreshes per minute.
3. Longer term, jf_playback_activity_with_metadata and jf_library_items_with_playcount_playtime might benefit from becoming materialized views themselves (refreshed independently on a coarser schedule) rather than plain views re-evaluated on every reference inside the js_* refresh chain.

Possible secondary issue: unbounded growth of archived rows in jf_library_episodes/jf_library_seasons

While investigating the indexing issue above, noticed jf_library_episodes contained 108,678 live rows in Postgres, while Jellyfin itself reports only 40,151 actual episodes - roughly 68k rows (~2.7x the real count) appear to be stale/archived entries that were never cleaned up. These accumulate via the Recently Added Sync task's bulk UPDATE ... SET archived='true' calls whenever items are removed/replaced (e.g. after a file gets renamed or replaced by a transcoding pipeline).

This compounds the indexing problem directly - every join/scan against these tables is working against ~2.7x more rows than the library actually contains. It may be worth either:

Periodically hard-deleting rows that have been archived for some time (rather than retaining them indefinitely), or
Excluding archived rows more aggressively from the views/queries that don't need them (worth checking whether all the join paths in jf_playback_activity_with_metadata and friends actually filter on archived = false, or if some scan archived rows unnecessarily).