[hylaride]

Having a good DB monitoring setup can also help a lot. We (over)pay for datadog's Database monitoring APM, but it is decent in letting you see how queries are run and change over time. It also checks query plans and lets you know when too many sequential scans are being performed, as well as indexes that are no longer used (in large DBs removing them can alleviate a lot of write IOPs).

It's not perfect and still requires thought and understanding (it'll recommend removing unused indexes on the primary keys, for example), but SOMETHING needs to continuously monitor anything but the smallest, simplest relational DBs.

[nijave]

Yeah slowly building up to that, we noped out of DD APM because leadership does not like seeing the $ amount of the bill (despite it being a small fraction of AWS and consisting of 10s of services in 1 platform)

[hylaride]

FWIW, there are alternatives: https://github.com/dhamaniasad/awesome-postgres#optimization

[nijave]

I'll have to take another look--last time all the free stuff was pretty underwhelming and all the commercial stuff... wasn't free...

So far I've had the best luck with pg_stat_statements + auto explain + Claude Code to go digging through those.