I had to fill a couple in and did the concrete formwork to pour one new one.
They are really just a big water tank.
Yes the water can go stale but they are covered and usually stay pretty clear.
Overflow? Well newer ones normally have a provision for an overflow to some kind of drain or spillway.
Usually there is no provision to simply open a valve and drain them. You would likely pump them out with a sump pump or by bailing.

When cisterns were common people did not really know about water born diseases and the cisterns tended to be about as safe as their shallow, run off contaminated, hand dug wells were.
There is no algae if there is no sunlight, no algae means there is not much for bacteria or other microbes to feed on, unless a mouse falls into it.
Most pathogens sink in still water too, so water pulled from the top of the cistern with a bucket was likely cleaner than the well water they had then.
A lot of our grandparents died from wells contaminated by runoff every spring. That was what they called spring fever.

When rural electrification (beginning in 1936) made electric pumps available, and the ability to drive deep wells that were cased to prevent the water from the barnyard running into them every spring cisterns went out of fashion.

Today the biggest danger from old cisterns is that somebody might fall in.
The covers do get weak over time.
Old wells are dangerous the same way.

I only ever saw the one new cistern and it seemed like an expensive way to store water.
I have seen people bury the plastic Intermediate Bulk Carrier tanks for water storage, but the only real reason they buried them was to hide them from sight.

IBC tanks

The two stacked tanks in this picture are 220 gallons each.
I know they are available up to 550 gallons but they are not cheap.
A new 550 gallon tank with fittings would likely cost close to $3,000 and a recycled one would likely be between $1,000 and $1,500 to buy.

These have taps near the bottoms.

45 gallon drums are easier to handle and much cheaper to get.

I suppose you could chemically treat the water to keep it fresh with something like sodium bisulfite, but I don't know enough about water treatment to talk about it, and I would still want to boil it.