We have friends who rent to live in SF and buy in LCOL as investment properties. Also seems to work well as a strategy.
I think of primary residence as a consumption expense. It's not really an investment because you can't cash out[1]. So unless you put value on the act of owning itself, it's best to do whatever gives you a lower monthly. In our case that's renting.
[1] yes I know about HELOCs. I'd rather get margin loans on index funds than risk getting kicked out of my home.