I have been working with pg_stat_statements extension to PG and it give us a way to see the actual SQL statements being executed by lemmy_server and the number of times they are being called.
This has less overhead than cranking up logging and several cloud computing services enable it by default (example) - so I don’t believe it will have a significant slow down of the server.
A DATABASE RESTART WILL BE REQUIRED
It does require that PostgreSQL be restarted. Which can take 10 or 15 seconds, typically.
Debian / Ubuntu install steps
https://pganalyze.com/docs/install/self_managed/02_enable_pg_stat_statements_deb
Following the conventions of “Lemmy from Scratch” server install commands:
sudo -iu postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"
Followed by a restart of the PostgreSQL service.
IMPORTANT NOTE: I am rusty on my PostgreSQL, it’s been over 10 years since I designed and ran a major time-critical website with it. That is not “SECONDS”, it is “MILLISECONDS”, but it is still a slow query being called frequently.