A battery bank at your house and that same battery bank at the power station are effectively equivalent. There's no benefit to distributing batteries here since transmission costs are relatively small.

So while yes water storage systems need lots of space, that's really only a problem if you're trying to distribute it. But you don't need to, you can centralize power storage to alleviate time of day spikes. Which many power grids already do.