As for other connectors: for Li-Ion batteries I use JST-PH connectors, standardizing on the pinout used by AdaFruit for batteries they sell.
For any custom "future extension" type connectors with I2C or SPI: 0.1" header holes. I can use them to either solder wires or place pin headers if needed.
I can solder a set of pins (or pogo pins) on a .100 spacing and mate into the .100 header holes. Or I can offset the .100 holes very slightly so they friction grab a .100 2x2 male header in the holes.
Or I can buy a breakout board that does the same thing for $7.00 for 2 rather than $50 per cable like Tag-Connect: https://www.pcbway.com/project/gifts_detail/PogoProg_Model_D...
.100" headers are hard to beat for general effectiveness.
Then I start looking into various kinds of connectors, and wonder if I'm overthinking it.
First, if you have a small number of pins (up to about 4-6), a 2x2 or 2x3 .100 header isn't that much larger than any alternative. Compare this: https://www.tag-connect.com/product/tc2030-fp-footprint to a 2x2 of .100 headers. It's actually bigger, and now you need a special cable instead of that bag of .100" jumper wires you have.
If you have something like 20 genuinely used pins (not 6 active and 14 unused), okay, you may need a different connector. But are you really sure about this? 20 pins communicating simultaneously has signal integrity needs and small connectors have WAY more coupling than .100" spacing.
Second, through-hole is always way more stable than no-through hole. Once you give your smaller pitch connector through holes, is it really smaller than .100"?
Third, manufacturers have no problems with .100" headers. Smaller pitches may increase the cost of your board. Try costing out a board that can mount and route a modern USB-C connector which has both surface mount and through-hole at small pitch. You're probably going to get a cost bump.
Fourth, you can buy really long .100" headers which allow you to conect to them and put a scope probe underneath. That's really convenient for debugging.
So, go through-hole .100" header until you've got a good reason otherwise.