It's a bit of both.
Security
When toproping, an experienced belayer should have the necessary training to adjust slack/tension depending on the route itself.
For example, to avoid any swings that would crash the climber into the rock.
There are many examples on safety-related criteria for determining tension (safety for both the climber and the belayer), but the list is long and I don't think I'd be able to sum it all here, so I won't be listing examples.
Learning to belay requires you to learn many things, and actually teaches you to think differently about safety and security. It's also a lot of fun and very exciting.
Do consider taking proper training for this though; it's a good investment. If available, face-to-face training is irreplaceable, IMO. There are many books and videos but you'll gain more experience by discussing with other people.
And regardless of whether you get face-to-face training or not, my advice is always the same: keep asking, always assume someone knows something you don't, never take an answer blindly as true, read, research, ask again, ask more.
Personal preference
Safety issues being said, the rest may be just a matter of personal preference.
If the rope is travelling perfectly in line with your climb, excessive tension might just be reassurance to novice climbers (make them feel that the rope is really there to catch them). However, if the rope is travelling sideways, for example, it's usually counterproductive for the climber. Like you say, it effectively pulls you off the wall.
Most of the experienced climbers I know (if not all) agree that you shouldn't need tension in the rope (unless the belayer identifies an actual security risk). The rationale is that the rope is there to catch you only in the event that you fall, but not to help pull you upwards (nor to help you keep balance).
When I was taught to rock-climb, the program included "trust games". These were meant to build confidence on the climber regarding the belayer, rope, harness, and the whole security chain.
Personally, I still like some tension when I feel I'm still too close to the ground but will ask for "slack" when at a reasonable height. Sometimes I'll also ask for tension when trying out the crux in a route.
TL;DR version
There might be a "safe zone" on a given route. A belayer should have proper training to identify actual security reasons for determining tension. Other than that, tension is mostly a means of providing reassurance to novice climbers (or experienced climbers who just prefer it one way or another).