Tension is where the game is at a crucial moment, and there is much to be gained or lost. Every move matters. This makes for very exciting games, if done correctly.
For there to be tension, there must be risk of loss (or of missing out on something great.) But how severe should this loss be? It shouldn't be "who cares?", nor should it be "I'm doomed." If the player suffers this loss, or misses out, they should have to change their strategy, to deal with it. That's my preferred amount of tension.
In train route-building game Ticket to Ride, you're collecting cards, in order to build routes. However, in many places, only one player can build a route. Another player could build there before you, at any moment. If that happens, you'll have to take a longer path. This is a good level of tension.
You probably don't want your game to be too cutthroat.
Agricola and Caverna are both best-selling
farm-building games. In Agricola, there is a sequence of steps you need
to take, in order to prepare for each of the regular harvest phases, so you get enough food. Get
it wrong, and your people will starve, and you've probably lost the game. It's very tense.
In Caverna, there isn't really a sequence of steps, that could go wrong. You can just get stuff to eat. If someone takes it, you can get something else. You don't need to cook things, so you can't be left with inedible food. Most people prefer the low-tension Caverna.
In adversarial games, make the players extremely vulnerable.
In my gangster game, players battle to the death.
Originally, players started on ten health points. They'd casually attack
each other for a while, reducing each others' health, but it didn't
really matter. However, once players got to five health or below, they
could die at any time.
A cliffhanger battle ensued, as players desperately tried to save their
last few points of health.
I changed the rules. Players now start the game on half as much health.
You can literally be eliminated after one turn. Choose your move
carefully. That's what I call tension!
In Radlands, the cards are so destructive that you can be half-destroyed in one turn. You must race to deal with the opponent's people immediately, or they'll ruin you.
In competitive games, make only one bundle of each resource available.
If someone takes a resource, it should mean that other players should have to wait to get that resource again, or they should get much less of it.
In most worker-placement games, if someone takes what you want, you'll have to wait for the next round, when their worker leaves, so you can take it. I wanted my farming game to have much more tension than other games in its genre. In my game, the workers never leave. When someone takes the horse, or the cabbage, no one else can ever get them again.
Keep constant time pressure on the players. Add in deadlines. This can be an impending punishment if they don't achieve something, or an opportunity that can be seized.
In my farm game, the players had objective cards, which would award points if completed by the end of the game. This wasn't very tense, so I let the players complete them at the end of any round, for vastly increased points. Now, the players are always rushing to try to complete their objectives before the end of the round.