This is one point we are actively working on all the time.
When Discourse was new, almost all sites had a brand new database so the database could fit in memory easily. Now, a few years later, some sites have over 100GB databases and RAM sizes that are not even a tenth of that.
One upcoming update in the next few weeks is the PostgreSQL 13 upgrade that will reduce the largest object size in half.
Other than that, step 0 into debugging your performance issues is gathering data with Prometheus exporter plugin for Discourse so we are not flying blind.