Sure. Many folks are on the spray foam bandwagon. Fact is, it’s pretty new on the scene. And installers make more money off spray foam than cellulose or fiberglass.
I started putting it into my homes over 15 years ago. I don't consider it "new" by any means. Yes, it's expensive, and yes it's more profitable. But it's also quite apparent in energy bills compared to "bat insulation" homes.
However, anything that traps water against wood is a problem for me. I’d 100% go foam on a metal building.
Open cell does not trap water any more than pink fiberglass does. And if it gets wet you can dry it out without damage to the insulation.
I’ve got a similar situation as you do. I could spray foam and it would probably work great for the rest of my lifetime. But I just can’t bring myself to do it.
I've got no beef with either choice, I just want to be clear about the negatives. My garage was mainly done in bat to save money. And the last home I helped with was "hybrid" insulation - both foam and bat... And that garage/house did have a fresh air system / humidity controls.
If you chose closed cell, it'd hold water. Open cell does not. But in either case, if the roof leaks you're kinda screwed regardless of insulation. Only difference is how quickly you'd know the roof was leaking.
fwiw-I don’t care for a “tight” house either. I want everything to breathe. These hyper efficient houses come with issues. I‘m much more interested in longevity.
It takes more than spray foam to build a tight house. It takes a level of attention to detail, high quality exterior doors, high quality windows, sill plate sealers or gaskets, attention to any exterior penetration, doors setup to seal correctly... It's a whole Matt Risinger thing. Build "quality" is probably better up north but down here in the south the trades slap **** together so fast and they ain't doing bids on "tightly sealed" houses because it'd take more time. First house had fresh air system, but turned out to be totally unnecessary and I disabled it. I did not like pulling "fresh air" from the top of my composite roof in the middle of summer.
It's a little different up north, especially if you're "mainly heating" as those systems tend to incur moisture rather than our 95% of the year running HVAC down south. They also tend to be better insulated and often have thicker walls and roofs, so more insulation in general.
But I'm not putting fresh air systems in houses anymore, not down south. At least not in the level of attention to detail that the contractors have around here. The only way you'd know is to do a leak test on a home to actually see how "tight" your house is, but for me it's been pretty unnecessary.
Regardless, whatever you do is going to be an improvement... And probably a big one.