[Bug] Fetching Subscriptions timeout

[kmccmk9]

Describe the bug
I have an instance that I use for my home. It currently has one user, myself. I am hosting it through the production docker containers as described in the Installation Instructions. About 4 days ago, the subscriptions list (/feed/subscriptions) started experiencing a timeout. All other pages on the website seem to function as normal. I am currently subscribed to 1178 channels but I have not had this issue until now. What's interesting, is if I navigate to the subscriptions page and there are new unseen notifications, the page loads completely fine. However, once those notifications have been seen/acknowledged, the subscriptions feed fails to load. On the server side I was able to capture the log I posted in the relevant section below. And yes I have run the database maintenance sql commands just in case.

Steps to Reproduce

1. Have an account with 900+ subscriptions?
2. Allow the system to find new videos associated with the channels
3. Navigate to /feed/subscriptions
4. See a list of videos
5. Refresh the page, note the browser never completes loading the page

Logs
As seen in the screenshot I have attached, loading the /feed/subscriptions page triggers an API POST call to /auth/notifications which takes 26 seconds to return.

Screenshots

Additional context
Currently, the Invidious version is: Current version: 2024.08.26-4782a67 @ (HEAD detached at v2.20240825.2)

This happens regardless of device or browser that is accessing the subscriptions page. This is all tested locally so upload speed, ISP, tunnel software, etc are not relevant.

[unixfox]

• do you hourly restart invidious?
• do you have pubsub configured?

[kmccmk9]

• do you hourly restart invidious?

  • do you have pubsub configured?

• Yes I have a cron job configured
• it is not configured

[kmccmk9]

Here's a random thought. Hypothetically, if the root cause of the slow response is that there are too many videos in the "notifications feed", could we use a database command to remove videos from the feed older than a certain date?

[unixfox]

There is an option here to disable user notification if the page never completes it loading:

invidious/config/config.example.yml

Line 324 in 4782a67

#enable_user_notifications: true

For the moment I have no idea what would happen really. Maybe try running invidious in debug log level in order to understand more why it is timing out.

[kmccmk9]

invidious.log
Ok I got something on this one. I've attached the log file to this comment since it is really big. From what I gather, it tries to fetch subscriptions (which never completes, despite it saying it took 201000 ms). The relevant part I gathered is that the SELECT statement that runs underneath, is really large. So large that it causes something to shutdown which causes a cascades of process shutdowns.

STATEMENT: SELECT * FROM subscriptions_a75132ba9a7a2e0ca62503ad06ada833436e089eb416e2c96265383aaf4ab29b WHERE NOT id = ANY (VALUES ('cPUbFteFL5Q'),('FCSCq5rGxDI'),('6bODiZ5bP84')...

If this is the true cause, perhaps a solution would be to rethink how that select query is working. Perhaps we can tell PostgreSQL to reference those ids, instead of passing them in? Or perhaps using a not exists clause and reference a table of these ids? Granted I'm not sure if this is the database choking because so much data is being passed in the query or something else?

[unixfox]

There is a pending PR that has been opened for a long time that should solve the subscriptions issues: #2469

[kmccmk9]

Ok great, I'm following that PR and when it gets merged I will update this issue. Your changes look sensible to me and it looks like it should address this problem. Fingers crossed!