Thread PostgreSQL performance tuning best practices

I have been migrating our application database from MySQL to Postgres and am running into unexpected query plan regressions on complex joins. The EXPLAIN ANALYZE output shows nested loop scans where hash join would be more efficient, despite having adequate memory allocated for work_mem. I've already tried increasing work_mem globally but the planner still prefers the suboptimal path.

Most of my production wins came from these three in order:

1. Add the missing indexes first. EXPLAIN ANALYZE is your friend — if you see Seq Scan on a table with millions of rows where there should be an index, that's usually 80% of the problem solved right there. I still catch cases where we added columns but nobody added the corresponding index for the new query pattern.

2. Vacuum tuning. autovacuum is great but it defaults to conservative settings. For write-heavy tables I bump scale_factor down and vacuum_cost_limit up — keep those dead tuples clean or your bloat will eat everything no matter how well you've indexed.

3. Partitioning for huge

I used to spend hours manually tuning queries before I learned that 90% of perf issues are just missing indexes or stale stats. The first thing I do with any slow query is run EXPLAIN ANALYZE and see whether it's scanning the whole table or hitting an index — if you're seeing Seq Scan on a large table where there should be a lookup, that's your problem.

A few things that helped me out of holes: make sure autovacuum isn't throttled too badly because bloat will kill read performance over time; check for long-running transactions holding locks and blocking vacuum; use partial indexes for common query shapes (like where status = 'active') rather than indexing every row.

I'm going to push back on this framing because "best practices" in Postgres are a category error — the advice depends entirely on your shape of data, which this thread assumes away.

Let me take three points:

First, vacuum tuning is almost always over-aggressive for read-heavy workloads and under-tuned for write-heavy ones, yet people quote autovacuum=on as if it's a finished answer. For any table with significant bloat potential (high update/delete volume), the default autovacuum settings are insufficient by an order of magnitude. You need to tune scale factors and costs per tuple specifically for each hot table rather than setting global parameters based on what works for a generic ODS workload.

Second, I want to flag that "use partial indexes" is genuinely one of the most underutilized optimizations in Postgres — not just for WHERE status = 'active' patterns but for covering any frequently accessed subset with its own index structure rather than walking a larger B-tree and filtering. This should be front-and-center, not buried as an advanced tip.

Third, I'd argue that the standard advice to "increase work_mem" is dangerous if you don'