To some extent, this is true. Archers and crossbowmen were vulnerable to cavalry, and needed to be protected by friendly infantry or field fortifications. In turn, unarmoured spearmen/pikemen were very vulnerable to archers (and even armoured spearmen wouldn't like standing there being shot at), but, with good discipline, could be very effective against cavalry.

But it isn't just rock-paper-scissors. The different types can work together. Given disciplined spearmen/pikemen, ancient (Hellenistic, Roman, etc.) and medieval commanders would use spearmen/pikemen to stop cavalry, and use archers to shoot at those cavalry. While archers were vulnerable to cavalry, cavalry were vulnerable to archers too - especially the horses which were often less protected by armour than their riders. If the cavalry could close with the archers, the archers were in trouble, but if they cavalry couldn't close with the archers, the cavalry was in trouble.

This didn't change with the adoption of guns, but continued into the era of pike-and-shot warfare (e.g., Thirty Years War, English Civil War). Infantry (mostly spearmen/pikemen), missile troops (archers, crossbowmen, arquebusiers/musketeers), and cavalry were the main elements of armies for a long time. With gunpowder artillery in common use, we can add artillery as a fourth major arm. Eventually, the bayonet allowed pike and shot to be merged into a common infantry, leaving the main branches as infantry, cavalry, and artillery.

However, don't expect swordsmen/axemen to be particularly effective against spearmen. The Spanish experimented with sword-and-shield men (roleleros) against pikes in the 16th century, with some success, but good pikemen could defend themselves against them well enough, and rodeleros were vulnerable to cavalry.

One common tactic was to use cavalry to force the enemy infantry (mostly spearmen) to stay in formation to resist cavalry attack, and then shoot at them with archers. Either the archers break the enemy infantry formation, which then flees, pursued by cavalry, or the enemy infantry is goaded into charging the archers and, if the plan goes well, then stopped by the infantry protecting the archers. If they break and flee after their unsuccessful attack, then the cavalry pursues. If they don't break, attack them in the flank or rear with cavalry. This assumes that enemy archers can be ignored - either overwhelm them with your own archer (hopefully greatly superior in numbers) or hit their archers with your cavalry at the start of the battle.

So... "pre-gunpowder" is a pretty long period stretching from the rise of warfare as a concept to the widespread introduction of muskets and cannon sometime in the 15th century. What this means is that any statement is going to be extremely general because the long peroid of time includes massive leaps in both technology and strategy. That said, here we go:

I mean RTS-games are games. They generally try to balance units in such a way as to allow rudimentary startegy to be employed by the player and RPS is the most common way to do that. The game would quickly become very dull if the balance is disturbed. A good example of this occuring is competitive SC2 where, for a good long while, high level play was essentially all protoss "death balls" maneuvering around a map trying to get an advantageous position on the other and then, in a matter of seconds it was all over.

While that's boring, an actual campaign, for large periods of pre-gunpowder history, was a lot like that. Armies maneuvered around trying to get an advantageous position on each other but generally pitched battles were relatively rare because unlike RTS games there's a lot more at stake if you lose. Warfare was often centered around sieges and while we do remember and study the famous battles extensively war was more often determined and concluded based on more complex matters. How long can you hold out against the besiegers? Do you have allies that will come to your aid and break the siege? Will they? If so, will they come quick enough to lift the siege in time? Is your opponent prepared to negotiate? Will a sickness erupt in his camp or yours? Will someone else exploit his weakness at home to make a bid for power or indeed invade his lands while his army is away? This however makes for terribly dull RTS play.

Another aspect exaggerated in RTS games are casualties. Often two armies will come head to head, fight it out and one army will, based on composition, position or skill, win. That means it will kill all or almost all of the enemy army often suffering high casualties while doing so. Still, what remains of the winning army is enough to go into the enemy "base" and snatch victory. Reality isn't like that, obviously. If you win but lose a significant part of your army you may as well have lost. You're unable to exploit your victory and you've significantly weakened yourself. In essence a Phyrric victory.

But what about your questions?! Are spears a good counter to cavalry? Sure. On a very fundamental level these games have some grain of truth in them. Braced polearms are a deterrant to a cavalry charge, archers will do more damage to men without shields than men with shields but there is really no guarantee a man with a sword will beat a man with a spear for example. A polearm is an excellent weapon against a swordsman and when employed in formation by disciplined men there is almost no answer to it other than bigger formations of more disciplined men, preferably with longer polearms. Historical examples include the phalanx, the macedonian phalanx, the swiss pikemen and so on.

RTS-games also tend to overstate the battlefield usefulness of archers and siege equipment. Skirmisher did play a role in pre-gunpowder battles but for the most part they were there for the sieges, not pitched battles. This is especially true in ancient times when ranged troops seem to have had a very limited role in pitched battles. There are a lot of reasons for this that vary a little over time but very basically it is hard to deploy archers in such a way as to keep them safe while still being in a position to inflict any significant damage and even harder to deploy siege weapons. An archer can not stand toe to toe with a spearman or indeed a cavalryman. Given space to maneuver an archery unit can soundly beat heavy infantry but every commander would have known this and generally avoided deploying infantry in such a way as to make them vulnerable to massed skirmishers. (Again, generally! There are notable exceptions to this rule throughout history such as Agincourt, Sphakteria, Crécy and Lechaion).

Archers generally excel at screening an army, ambushing, siege defense, fighting in rough terrain and so on. They really only become prominent in pitched battles for a realtively short (in this context) period during the medieval "golden age of infantry". An archer can not stand toe to toe with infantry or cavalry but if the enemy can not reach them because they are on top of a wall, on the other side of a moat or in terrain where their lack of armor is advantageous they won't have to.

The same is generally true for all forms of troops. They each have their specific situations and conditions in which they shine, getting them into these positions is usually the difference between victory and defeat, not the exact composition of your army. Now, your ability to get your troops in that position on the other hand may well be dictated by other factors. How much space do you have to maneuver? Are there strategic locations you can not afford to lose of retreat from? Where can you supply your army and for how long?

As a fellow Total War enthusiast and an ancient history postgraduate student in the late Roman military, this is going to be a fun question to answer!

As the other answers have said, it is a little bit of yes, a little bit of no. A deep formation of spears or pikes was effective against a cavalry charge, but a two-handed spear or pike meant you could not hold a large shield to protect yourself from missiles. Cavalry could also close the distance the missile troops much quicker than other infantry, thus exposing them to less fire before engaging them in hand to hand combat.

However the biggest difference between reality and gameplay is the strategic element. When playing Total War, if you're playing as the Romans and you're going to be fighting some Germanics, you'll probably intentionally recruit more archers, because you know the troops available to the German factions are generally 'glass cannons' with high charge damage and low armour. You might also get some shock cavalry to charge them from behind and break them. Whatever your strategy is, you'll recruit an army for that specific purpose. Same goes if you want to invade Persia, knowing that faction relies heavily on heavy and missile cavalry, you might recruit your own archers and missile cavalry and plenty of spearmen. You might even choose to recruit a spear- and swordsmen army, with a large amount of artillery, for the siege battles.

The real Romans didn't have the ability to make similar strategic decisions. The armies of ancient societies were deeply rooted in cultural traditions and only made very slow and subtle changes. The Roman armies were always comprised of mainly heavy infantry that fought with swords in a close and orderly formation. For example, the Dacian soldiers that Trajan's armies fought during the First and Second Dacian Wars are depicted on a number of monuments, and what is conspicuous about these is that they are often shown armorless and wielding a falx, which was a massive scythe-like weapon. It is presumed that they would charge the Roman formations swinging these great scythes in a high arc, crashing through Roman shields and obliterating their heads, or in a low arc against their legs and severely maiming them. Again, in Total War, a smart commander would keep their infantry the hell away from an attack like that. The Romans? They could not recruit an unlimited amount of alternative troops, so instead, they upped the armour of the legionnaires, giving them new armour with stronger cheek and shin guards.

Similarly, against the Persians, the Romans invaded numerous times and came across the same difficulties each time. They could not sustain a massive, infantry-centric invasion force deep within hostile territory. The Persian cavalry would harass and raid the Roman force but not engage in open battle. The Persians would employ a 'scorched earth' strategy to deny the Romans resources, eventually wearing them out until they were so exhausted the could be more easily engaged in battle. The Romans did employ more cavalry on the Persian frontier than elsewhere, as well as making use of some camels, but never in large amounts and the heavy infantry was always the mainstay of the Roman army, even when they weren't the most effective element.

As stated with the armour upgrades and more cavalry in the east, the Romans were able to adapt and change. The gladius swords were increasingly replaced with the longer spatha. After the disastrous third century the Romans did make massive strategic changes, relying more on intricate systems of fortifications and reserves of soldiers deeper within the Empire. Additionally, as the Empire and especially the military became increasing 'barbarised', other things changed. For example, the early Roman army was famously silent in battle, but from the fourth century they began to use the Germanic battle-cry known as the barritus. They also began to use spears more than swords, and used the plumbata instead of the pilum. But these developments were tied into a changing culture, not necessarily a strategic decision.