I think one of the definitions offered by the Merriam-Webster dictionary provides a clear picture of how this word applies to game design:
a balance maintained in an artistic work between opposing forces or elements
Civilization and Pirates! creator Sid Meier famously stated that “games are a series of interesting decisions.” To me, “interesting” is a particularly important word in that phrase. Any choice presented to the player can be considered a decision, but unless that decision is actually “interesting”, the decision does nothing to actually make the game more fun. Tension is a useful tool for making sure that the choices in your game are actually interesting.
In the context of games, you can create tension by giving the player multiple opposing goals. Successfully implemented, this will mean that your player will have to continuously choose between two or more actions each in service to a different goal. Pulling an example from Super Mario Bros., consider the interplay between the jumping obstacle-avoidance gameplay versus the time-limit applied to each stage. These systems represent two separate goals that encourage different behavior: moving carefully to avoid obstacles & moving quickly to reach the stage’s end before time runs out.
It’s important to note how the time-limit is balanced so as not to be overwhelmingly severe. At the start of a stage, the player rarely has to pay attention to the time-limit because there’s usually more than enough time to complete a stage if they proceed at a reasonable rate. This is good because the player often needs to concentrate on understanding whatever unique challenges or gimmicks the stage is presenting. That being said, the time-limit is not set so high so as to be irrelevant. It is set to be enough to enforce a certain level of risk-taking by the player and prevent too-slow, overly conservative gameplay (which will probably be dull for the player.) The time-limit later becomes a factor towards the end of a level with a chime and sped-up music informing the player that they need to hustle. At this point, the player has acclimated to the stage’s particular challenges so they are able to make an informed choice regarding how much faster they can go without taking on too much additional risk.
Similarly, I intentionally use this sort of tension in my February #OneGameAMonth entry. While the player must keep their ship out of the line-of-fire of their enemies, the only way to target the enemies is to pass through their firing lines. This way, the player can’t win by simply avoiding fire, they must gauge when it’s safe to expose themselves to enemy attack in order to get their own attacks in. Even Robot Legions uses the tried-and-true system of having defeated enemies drop collectibles. While defeating an enemy and moving on is the safest strategy, the collectibles tempt the player to linger in a spot where other enemies are likely to converge.
Tension is about the energy that results from pulling something in opposite directions. This energy is part of what makes a lot of games fun. If there is only one optimal choice or if all player choices are complimentary this reduces the tension of that decision. Tension is about making sure a player’s choices matter so the resulting game matters to the player.