but Lemmy really fucked up their database performance somehow.
Can confirm. I spent like 4 hours one day configuring auto vacuum and other shit on my Lemmy DB because it was bloating to hell and eating up resources. Runs much smoother now but it was a massive PITA to get there.
How to see if autovacuum is already enabled (these are commands you’d run while in sql):
SHOW autovacuum;
View current settings:
SELECT name, setting FROM pg_settings WHERE name LIKE'%autovacuum%';
Monitor which tables need attention:
SELECT schemaname, relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup >1000ORDERBY n_dead_tup DESC;
I wish I had documented it at the time because info about this for Lemmy specifically is pretty lacking. I was in kind of a dire situation though because my disk space had filled up quickly and my system was struggling. A lot of what I did was basic PostgreSQL maintenance stuff, so you should be able to find some general guides for that.
EDIT: I had Claude help me corral some optimization stuff into a PDF. This is along the lines of what I did to get my db back under control. Make sure to always backup first! https://u.orca.casa/1771250081
Can confirm. I spent like 4 hours one day configuring auto vacuum and other shit on my Lemmy DB because it was bloating to hell and eating up resources. Runs much smoother now but it was a massive PITA to get there.
Old post, but can you share what you did? I’m fighting the same problem.
I can’t remember exactly what I did but enabling autovacuum was one of the big ones. I’ll try to provide some info below:
Walks through VACUUM and autovacuum - https://oneuptime.com/blog/post/2026-01-25-use-vacuum-analyze-postgresql/view
How to see if autovacuum is already enabled (these are commands you’d run while in sql):
SHOW autovacuum;View current settings:
SELECT name, setting FROM pg_settings WHERE name LIKE '%autovacuum%';Monitor which tables need attention:
SELECT schemaname, relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;I wish I had documented it at the time because info about this for Lemmy specifically is pretty lacking. I was in kind of a dire situation though because my disk space had filled up quickly and my system was struggling. A lot of what I did was basic PostgreSQL maintenance stuff, so you should be able to find some general guides for that.
EDIT: I had Claude help me corral some optimization stuff into a PDF. This is along the lines of what I did to get my db back under control. Make sure to always backup first! https://u.orca.casa/1771250081