...I think this is supposed to be some kind of auto thing that happens when the phone recognizes a situation that would benefit from it.
I get that, but now you run into the problem of what you think is needed, and what the programmer thinks is needed, are two separate things. If you look at the OP's example. The picture, on average, is decently exposed. Any less and the homes would be too dark. Any longer and the sky would start blowing out. The app doesn't know that the subject is the moving rocket, so it doesn't set the exposure for it specifically. That's why a decent understanding of a true manual mode is needed for something like this. According to Laura, the Pixel apparently doesn't have a manual mode, so you'd have to see what third party apps can do. Camera FV-5 is one of the most advanced third party camera apps for manual controls, but then you'll still be limited to firmware limitations of the phone. There's a free version to try before you buy.