Tension is where the game is at a crucial moment, and there is much to be gained or lost. This is very engaging, though not everyone likes it.
Keeping your game a constant cliffhanger is not easy to do. Every turn needs to be a crossroads, not just a repetition of a previous turn.
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.
Keep constant time pressure on the players. This doesn't need to be a negative. It could just be an opportunity. Whatever it is, it matters, and it's approaching fast!
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!
Make players compete over things, and make those things scarce. You don't need to punish players, and have them lose things. It's generally better to make the tension around potential rewards, that they might miss out on.
In my farming game, there are lots of different action tokens the players can take. Two players can't take the same token in a round, but it's no big deal. You just wait for the next round, for it to come back. This wasn't tense enough, so I made the tokens take two turns to come back. Now, you really can miss out.
Tension is generally good, but don't put too much tension into your game, particularly if it's a complex game.
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. Get
it wrong, and your people will starve. You'll need animals, and a
fireplace of some sort, to cook the meat. You can plant grain, but it
will be worth huge amounts of food, if you have an oven, and use it to
bake bread. It's a struggle to get this all done before the harvest.
In Caverna, there isn't any baking of bread, and you can just eat animals as needed, without a fireplace.
I much prefer the very high tension and high
stakes of Agricola, but most people prefer the low-tension ease of play
of Caverna.