I don't have a proper cistern, but I do collect rainwater in several large above-ground tanks for watering parts of our garden.

The water does go a bit funky after a while. It's impossible to keep some bio debris from getting in, and algae will grow if there's any light at all.

If I had a very large tank, especially underground, I would add aeration to keep it relatively fresh. Otherwise it will smell sewer-ish. I'll bet that even an aquarium pump would do wonders.

BTW, the good part about an above-ground tank is that you can gravity-drain the water to where you need it. An underground cistern would require a pump and the juice to run it.