Seen plenty of build stories on CloudyNights where they have put piers down a couple of stories into the base and had isolated floors so no vibration. It all comes down to how much resource ($$$) you can muster for the build.
My pier is an old unused 10" dia concrete pipe stood on end and secured with a meter long threaded rod to the concrete slab below. Cost, about $30 in plywood, rod, nuts, washers etc and a bit of McGyver modification of some existing hardware. I could fill it with a few bags of cement to really solidify it but my floor is isolated from the pier and I have no movement or vibration problems.
I have heard of piers being made from old tree trunks ... so you might have to check the alignment now and then but it can be vibration free if you don't kick it. And a tree stump once !!
Piers through upstairs floors seem to use reinforced block columns as they would place under an upstairs fireplace. Expensive and quite large footprint. I think you can get precast coloumn units as well. Idea is good, cost becomes the real issue. Although if you can work remotely on the scope then there is no floor vibration, Downstairs computer, Teamviewer etc.
Cheapest is your old dam idea for a pier and sounds like a good location LP wise. Could make a taller pier and add a platform around it now and a shed later. Gotta think ahead
How about a side or rear addition to the shed ? Probably cheaper to build than a mezzanine floor I'd say.
Keep thinking, collect resources slowly. Been there, done that.
I know the value of my OB and it isn't in $$$