Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that make PostgreSQL easier to operate.
Refreshing a materialized view from scratch is fine until the view gets too big. Then you need incremental updates. Postgres doesn't give them to you natively, but you can build them.
When Postgres CPU is high, the question is not whether the server is fast enough. It is which queries are using the cycles and whether they should be.
When your database is I/O-bound, the question is not whether the disk is fast enough. It is which queries are doing the I/O and whether they should be.
A Postgres OOM kill is one of the few crashes that is almost always preventable. The pattern is consistent enough to have a checklist.
When work_mem isn't enough, Postgres writes intermediate results to disk. This is invisible until you look for it, then it explains a lot of slow queries.
If the planner thinks a step returns 100 rows and it actually returns 100,000, every choice it makes after that is suspect. Here is how I track it down and fix it.
When inserts get slow, the cause is reliably one of: too many indexes, too many constraints, or vacuum starvation. Here is how to tell which.
When DELETE is slow, the cause is usually cascading work — child table updates, trigger fires, index maintenance. The fix is in the surrounding work.
Transaction ID wraparound is rare and dramatic. When you see the warnings, you have time to act, but not a lot. Here is the runbook.
A query that was fine yesterday and slow today usually means the planner picked a different plan. Diagnosing what changed and why is the actual job.
psql is more capable than people use it for. A handful of meta-commands and shortcuts make it the most productive shell for database work.
pg_stat_statements is cumulative since cluster start or last reset. If you reset it, you lose the data you would have used to debug the next incident.