Is it though? This is a genuine question. My intuition is that the investment of time / stress / risk to become good at this is unlikely to have high ROI to either the person putting in that time or to the business paying them to do so. But maybe that's not right.

I really do not think so. Most startups should rather focus on their core competency and direct engineering resources to their edge. When you are $100 mln ARR then feel free to mess around with whatever db setup you want.