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