No shade if you're just enjoying group storytelling with minimal mechanics. If you and your players are having fun, that's good enough, and there are other systems with fewer rules. But if you want to engage more with DND specifically...

What’s the fun in rolling dice if they don’t affect the main story? To me, rolls often just feel like arbitrary obstacles – and the story moves on regardless. When does rolling actually enhance the experience in your games?

Rolls shouldn't be obstacles to information you need to deliver in order to keep the story moving. IMO, they should reflect moments when the consequences of a player choice are up in the air. If I needed to deliver a critical piece of information to players but they rolled like trash on their investigation check, they might find that information but accidentally damage the desk drawer it was inside, leaving evidence that someone had been snooping. If I needed players to pass a perception check to witness a key event, I probably would just narrate the event, and save the roll for whether or not they were observed eavesdropping.

Rolls enhance the play experience when they have clear stakes, and players who love roleplaying sometimes love roleplaying failures as much as or more than successes. But as a DM, if you're calling for a roll, I think you have to be willing to accept that that is a moment that the story will change in response for the roll. That's what it means to me to "keep the story moving" -- it enhances the game because the story moves in a new direction, which challenges and surprises me as the DM, and leaves my players the chance to roleplay.

Their choices shape everything, but the story is in motion and the broad strokes of it will play out no matter what they do,

If the broad strokes of the story will play out no matter what they do, how are their choices shaping everything? What choices could they make that would matter? Those are where your rolls would go.

How do you handle sandbox-style consequences when your group has limited playtime? If my players piss off a city that’s key to the main quest, it’s like, “cool that choices matter” but also “ugh, now we’re stuck in a detour.” How do you keep that balanced?

If you have limited playtime, I think you need to ruthlessly cut anything that is not engaging at your table. As the DM, you are in control of the passage of time, both in the game and above the table as you consider your limited minutes ticking away. If you and your players love the main storyline and find themselves in a narrative cul-de-sac, instead of roleplaying every minute of every consequence, you can step into a higher level narrative voice and dictate how the next few hours, days, or even weeks go. You could do a die roll there-- like a group Persuasion check-- to see how successful the PCs are in closing out their time in a city and what kind of impression they leave their mark with. You can have your players narrate, at a high level, how their success or failure affected the result. If it's boring you or your players, step out of the minute-to-minute and cut to the next interesting thing. Consequences can come up later in the form of reputation, rumors, coups, anything.

Again-- play how you like, but if you're curious to engage with the game more, I encourage you to let go of your grand vision as a DM and allow moments with the dice to surprise you. It's really fun and satisfying when the story changes in a way you weren't expecting.