Delete doesn't fully reclaim the disk space unless vaccum is triggered. Even after vaccum not all the disk space is reclaimed.

That's why for the table videos it's better to truncate it because it will instantly reclaim the disk space.

Also you should allow the ability to turn on or off these jobs because if one is running invidious multiple times this could lead to some funky behavior if 10 invidious processes are deleting the same thing.

Delete doesn't fully reclaim the disk space unless vaccum is triggered. Even after vaccum not all the disk space is reclaimed.

That's why for the table videos it's better to truncate it because it will instantly reclaim the disk space.

The problem of TRUNCATE is that it entirely wipes the table, which, if done too often, makes the caching useless. My idea was to have a regular DELETE running (that only removes expired entries) so the autovacuuming worker would be trigerred more often, but would have less work to do (there won't be a million rows to reclaim every day).

Also you should allow the ability to turn on or off these jobs because if one is running invidious multiple times this could lead to some funky behavior if 10 invidious processes are deleting the same thing.

Afaik, a simple DELETE can be run by multiple instances in parallel. If it has already been run recently (in the last few minutes), there won't be much to delete anyway.

I considered to add such an option, but I'm not sure if that's really required; None of the commands locks the DB, and they're not run very often (even with 20 instances running, they'd be ran 20 times per hour).

This will indeed require some large scale testing.

Delete doesn't fully reclaim the disk space unless vaccum is triggered. Even after vaccum not all the disk space is reclaimed.
That's why for the table videos it's better to truncate it because it will instantly reclaim the disk space.

The problem of TRUNCATE is that it entirely wipes the table, which, if done too often, makes the caching useless. My idea was to have a regular DELETE running (that only removes expired entries) so the autovacuuming worker would be trigerred more often, but would have less work to do (there won't be a million rows to reclaim every day).

The issue on big instances is that autovacuum hammer the CPU like crazy, so doing a TRUNCATE avoid any CPU hammering by the autovacuum as the disk space is instantly reclaimed. I do agree with the cache purpose, but IMO the cache table is mildly useful because the cache table grows like crazy on big instances (6.7GB in 9 hours for me), and the video streams are often replaced with new one thus the rows cache are often updated.

The current cache system needs to be revisited.

I currently have a TRUNCATE every 12 hours, I can replace it with your query in order to see if that won't grow the DB too much.

Also you should allow the ability to turn on or off these jobs because if one is running invidious multiple times this could lead to some funky behavior if 10 invidious processes are deleting the same thing.

Afaik, a simple DELETE can be run by multiple instances in parallel. If it has already been run recently (in the last few minutes), there won't be much to delete anyway.
I considered to add such an option, but I'm not sure if that's really required; None of the commands locks the DB, and they're not run very often (even with 20 instances running, they'd be ran 20 times per hour).

People may prefer to return these queries outside of invidious, so I think an option is a good idea.

On top of that, channel_videos needs to be cleaned up too.

No idea how

The issue on big instances is that autovacuum hammer the CPU like crazy, so doing a TRUNCATE avoid any CPU hammering by the autovacuum as the disk space is instantly reclaimed. I do agree with the cache purpose, but IMO the cache table is mildly useful because the cache table grows like crazy on big instances (6.7GB in 9 hours for me), and the video streams are often replaced with new one thus the rows cache are often updated.

Damn D:

The current cache system needs to be revisited.

Yup, I'm definitely looking closely at solutions like Redis.

I currently have a TRUNCATE every 12 hours, I can replace it with your query in order to see if that won't grow the DB too much.

I'm wondering if a more regular deletion would have the same "hammering" impact...

If TRUNCATE-ing is the only way, then a cron job is the only viable way atm (until we can make invidious run for multiple hours without a hiccup).

People may prefer to return these queries outside of invidious, so I think an option is a good idea.

Okay. I'll add that.

On top of that, channel_videos needs to be cleaned up too.
[image]
No idea how

Ah, that's quite a lot of videos D:
I'll try to figure out how to clean that, then...

Invidious::Database::Videos.delete_expired: has been tested on my instance for 15 days and seems to work fine. The database size stay at around 5GB while having a hundred of new videos added to the table per seconds.

Great! Did you notice any CPU/RAM usage?

Invidious::Database::Nonces.delete_expired: The table is empty on my side, but why not.

I double checked: it only fills up if captcha are enabled on login/sign-up. I don't think yours has it enabled?

Invidious::Database::Channels.delete_not_subscribed: It adds too much load on the database as explained in https://github.com/iv-org/invidious/pull/3294/files#r961975096, or we only execute the query on instances with like < 100 users

I'll entirely remove the code. The channels cache is also used for RSS iircs, and tbh, we don't keep much data anyway (the channel_videos is much bigger).

Invidious::Database::Videos.delete_expired: has been tested on my instance for 15 days and seems to work fine. The database size stay at around 5GB while having a hundred of new videos added to the table per seconds.

Great! Did you notice any CPU/RAM usage?

Difficult to say because my servers are often overloaded during the night in EU time (something I plan to fix) but I haven't seen any spike in CPU usage from postgres autovacuum which is nice. So I think it's ok.

Invidious::Database::Nonces.delete_expired: The table is empty on my side, but why not.

I double checked: it only fills up if captcha are enabled on login/sign-up. I don't think yours has it enabled?

Indeed I deactivated the captcha for logins a while ago.

Before merging the PR I still would like the ability to disable this job in the config.

Before merging the PR I still would like the ability to disable this job in the config.

Sure, will do that tomorrow!

I should have fixed the CI now.