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.