Trilarion: I tried to counter a large stack by artillery in Civ IV once, but the problem was that I would have needed as many artillery units as the enemy had stack units because I could attack only once.
That's not true.
Artillery did collateral damage to other units in the stack. I think it was 50% of base damage or so.
Meaning a single artillery unit would do 100% its attack value to the target unit, AND 50% to others in the stack. Combined arty strikes were easy way to wipe out stacks.
Trilarion: The "one unit per tile" is in principle also quite old and stood the test of time, see the SSI General series.
"General" did not include production. Not to mention that it took place on a vastly different scale.
A closer comparison would be Europa Universalis (or Crusader Kings), and both allow "stacking" without any balance issues.
Trilarion: With the right tile density, you could not simply rotate units in and out because you would not have enough of them.
That assumes the terrain allows the same access to enemy for all units. Or that the defender is not using forts.
I've defended cities in 3v12 situations (or similar enemy advantage) enough times to know it's perfectly possible in Civ V. Of course not so much if you put road (or even worse, rail) network all around your city, but that can be pillaged as needed.
People who had problems with "doom stacks" not only failed to appreciate just how hard artillery countered them, but also the fact that enemy's possession of "undefeatable" one was their own failure in not maintaining similar military production.
Trilarion: Civ V with a fixed tile size but spanning a huge time range with small amount of nits at the beginning and a large amount of units at the end shows the limits. They could have adapted (1 unit per tile in the ancient times, 2 units per tile in the renaissance, 3 units per tile in the modern time) and it would probably have been quite difficult to win against the AI.
And yet it was perfectly possible to win against large stacks in Civ iV. Go figure.