I look at indexes first
Most slow queries are slow for one reason. Postgres reads every row in the table, one by one. I run EXPLAIN (ANALYZE, BUFFERS) on the slow query and look for a sequential scan on a big table. Then I add the right index. A query that took four seconds drops to four milliseconds. This one step fixes more problems than anything else.
I fix the memory settings
The default config is built for a small machine. Almost nobody changes it. On a real server I update four settings:
shared_bufferseffective_cache_sizework_memmaintenance_work_mem
These four often give the biggest jump in speed. The defaults waste the RAM you already paid for.
I keep autovacuum happy
Dead rows pile up over time. The stats go stale. The planner starts making bad choices. Performance drops slowly, and one day someone panics. I make autovacuum more aggressive on the busy tables and keep the stats fresh. The planner then picks good plans on its own.
I fix the N+1 in the app
Sometimes the database is fine and the app is the problem. An ORM fires 500 small queries to draw one page. No database setting saves you from that. I batch the reads. I use joins. I put PgBouncer in front so the app stops paying for a new connection on every request.
I measure before I guess
I turn on pg_stat_statements. It shows me which queries eat the most time. So I fix the real problem, not the one I imagine. Most of the load comes from just a few queries. I find them and I fix them.
The simple truth
PostgreSQL runs far on one good server before you need to shard or migrate. People jump to a new database too early. They blame Postgres before they configure it.
So before you move away from Postgres, try this list first. Most of the time, you stay.