Even a small hydroelectric dam generates gigawatts of power
Actually, multi-gigawatt hydro plant is a large hydro plant, e.g. Hoover Dam has a capacity of 2GW. A medium sized hydro plant might have a capacity of around 200 MW, e.g. Martin Dam in Alabama has a capacity of 182 MW.
Your point is well taken however; scale issues will probably prevent pumped storage hydro from being the one-and-only solution to intermittent energy sources. Just for comparison, the reservoir created by the above-mentioned Martin Dam covers 40,000 acres!
However, pumped storage hydro can still be a useful and economical part of the solution. Other components would be natural gas powered combustion turbines which can be brought online quickly as needed, and a mix of renewable sources. To this latter point, some areas tend to be windier at night than during the day; this suggests that a mix of wind and solar and wind might be a useful combination.
Still, it is hard to imagine that we'll be getting away from fossil and nuclear any time soon; renewables can help reduce the amount of fossil fuels that we consume, but won't (for now) be able to eliminate the need for fossil. Pumped storage hydro can be a valuable part of the solution by smoothing over irregularities in the supply and demand while reducing the use of natural gas powered generation.
This thread is for asking any questions that might seem obvious, tangential, silly or what-have-you. Don't be shy, everyone has holes in their knowledge, though the fewer and the smaller we can make them, the better.
Please be respectful of other people's admitting ignorance and don't mock them for it, as they're doing a noble thing.
To any future monthly posters of SQ threads, please remember to add the "stupid_questions" tag.