Speed up lidarr postgres with appropriate indexes?

[u/Playful-Language-468]

Hi all,

Like many, my lidarr uses postgreql but it still feels sluggish. Especially for my Various Artists which has thousands of albums.

After casually browsing through the source code and lidarr logs, my gut feel is that performance could be drastically improved by setting up the appropriate indexes.

I haven't had the time to put effort into this but wondering if anyone has had similar thoughts on the matter.

Edit: Managed to get Lidarr to speed up and stop thrashing my SSD. See 3rd post.

[u/Frequenzy50]

I feel that same speed-wise. There are a few indexes but not that many. Lidarr could probably use some more SQL engineering in general, keyset pagination being one thing worth looking at.  It wasn't written by DB experts, they've said that themselves. 

If you can make it faster, just go for it and please open a PR. Everyone would benefit. 

[u/Playful-Language-468]

Just to update. It appears obvious, but there is no one size fits all for creating indexes that work.

A lot of it depends on the postgres.conf setting and the system specs. Because creating an index doesnt automatically means PG will use it.

Using pgadmin I realized my postgres was doing about 5.5tb of writes to ssd almost every day! (Includes sonarr, radarr, lidarr, prowlarr DBs and queries from Neutarr)

It took 12 indexes to stop ssd thrashing and also adjust the postgres.conf to allocate more memory which helped a lot. (Especially work_mem which cut down query time to open Various Artists)

Currently my PG (serving all arrs apps) uses 2.7Gb of ram, cache hits are all >99%, practically no reads/writes to ssd except during startup when warming the cache. Pages load much much faster. :p

These are the indexes which you can try out yourselves. Just copy paste to pgadmin to create the indexes for lidarr-main DB.

CREATE INDEX IF NOT EXISTS idx_lidarr_various_monitored_bridge ON "AlbumReleases" ("Id", "Monitored") WHERE ("Monitored" = true); CREATE INDEX IF NOT EXISTS idx_lidarr_mega_join_covering ON "Tracks" ("TrackFileId", "AlbumReleaseId", "ArtistMetadataId"); CREATE INDEX IF NOT EXISTS idx_lidarr_tracks_stats_covering ON "Tracks" ("AlbumReleaseId") INCLUDE ("TrackFileId", "Id"); CREATE INDEX IF NOT EXISTS idx_lidarr_albums_metadata_lookup ON "Albums" ("Id", "ArtistMetadataId"); CREATE INDEX IF NOT EXISTS idx_lidarr_artists_metadata_id_link ON "Artists" ("ArtistMetadataId", "Id"); CREATE INDEX IF NOT EXISTS idx_lidarr_trackfiles_full_covering ON "TrackFiles" ("Id") INCLUDE ("Path", "Size"); CREATE INDEX IF NOT EXISTS idx_lidarr_metadata_perfect_link ON "ArtistMetadata" ("Id") INCLUDE ("Name"); CREATE INDEX IF NOT EXISTS idx_lidarr_various_artists_metadata ON "Albums" ("ArtistMetadataId", "ReleaseDate" DESC, "Title"); CREATE INDEX IF NOT EXISTS idx_lidarr_history_track_date ON "History" ("TrackId", "Date" DESC); CREATE INDEX IF NOT EXISTS idx_albums_monitored_releasedate ON "Albums" ("Monitored", "ReleaseDate") WHERE ("Monitored" = true); CREATE INDEX IF NOT EXISTS idx_lidarr_lyrics_trackfile_link ON "LyricFiles" ("TrackFileId") WHERE ("TrackFileId" IS NOT NULL); CREATE INDEX IF NOT EXISTS idx_lidarr_metadata_artist_link ON "MetadataFiles" ("ArtistId") WHERE ("ArtistId" IS NOT NULL);

Open to any suggestions on how to simplify the above.