For somebody new to Bethesda games and RPGs, can it be overwhelming?
It's pretty much an action game with an RPG levelling system. There's aim assist in it that you typically won't even notice. Just click close-ish to heads and the game will make the shots work for you. If you're worried about dying a lot just build high Endurance. Lone Wanderer's also the second most powerful Fallout protagonist ever. And without trying, too. Everyone else has to somehow game the system but Wanderer's power comes very naturally from just applying some common sense. Any game where you can be potentially sitting on 10K effective HP in your pocket isn't gonna be very difficult.
Almost every Fallout protagonist plays different but Wanderer's probably the simplest. Fallout 3 has really effective armor, the max damage reduction you can reach is 94%(!!!) you can very easily reach 10 on every Special, you get a perk every level and it's easy to drown in more than enough skill points to get perfect scores on every skill. Most of the perks are just generally pretty damn good, as well. So are quest rewards. Clad in power armor, if you get your hands on Vengeance, the unique gatling laser, you're pretty much good to melt everything and everyone you meet with very minimal effort. Even the harder enemies like Deathclaws have easy ways to get around them, in their case, a dart gun that cripples both legs in one shot.
Even with the high potential a minmaxed Wanderer can reach, it's not at all necessary. The game showers you in Stimpaks and other drugs that make it easy to stay alive if you play conservatively. Companions are very effective fighters in most situations. There's quite a few unique and powerful weapons you can get early on (Lincoln's Repeater, Ol' Painless, Sydney's Ultra SMG) that will easily carry you through most of the game. And then there's VATS. VATS is a HUGE crutch.
The main thing is when in doubt just spam VATS. It makes you take only chip damage from whatever, and at level 20 you get this perk called Grim Reaper's Sprint that refunds all your points if you manage to kill even one dude with it. VATS has an increased critical chance, and it is fast enough that you can use it to parry missiles with your face with very little consequence. Most enemy groups have at least one or two scrubs you'll be able to take out in one VATS burst, which gives you more attempts to actually crit the big threats. You can stockpile Jet and coast through the game just spamming this. It's THAT strong. Come New Vegas it was nerfed, and even Fallout 4 didn't restore it to it's former invincible tank glory.
I went through all the DLCs in sort of a blurry haze. I vaguely remember the plots. The Pitt had Big Boss fighting a slaver boss and Point Lookout had us helping a ghoul kill a brain in a jar. Broken Steel was kind of endgame fights vs the Enclave but nothing posed much of a challenge to a T-60 power armor with Vengeance.
Even if let's say one is hypothetically hopeless at videogames it's easy enough to get bad karma early on and get carried through the early game by Jericho, get to Underworld and buy Charon's contract to get carried by him for a good, long while, then finish up with good karma and get carried by Fawkes who can literally solo the remainder of the game for a player no matter how hamhanded they are.
Broken Steel does introduce enemies you gotta cheese or just have top level gear to take down reliably. Super Mutant Masters have more health and are way more dangerous than even Behemoths. Feral Ghoul Reavers are much like Deathclaws: Extremely dangerous up close but not a threat if you dart gun them. Simultaneously, Broken Steel raises the level caps for companions so Fawkes becomes even more of a gamebreaker. Dogmeat also gets a gorillion HP so you can avoid the inevitable sad when the dumb mutt eventually rushes into a field full of explosives and gets oneshot.
New Vegas I'd say can be overwhelming. Courier Six can never manmode the way Lone Wanderer's able to. Even at max health and Damage Threshold Deathclaws can threeshot his ass, and there's no easy Dart Gun either. Courier also has to very carefully craft his build to specialize in something specific while Wanderer just kinda naturally ends up being a master of everything. Courier has to carefully minmax and do exploits to be able to have a 10 10 10 1 10 10 10 SPECIAL. Lone just has to hit Level 30 and then grab the Bobbleheads to get perfect 10s, a privilege usually reserved for final bosses only. New Vegas has the opposite of aim assist, in fact: You can have your sights dead on the enemy's head and your shots can go all over the place, as it takes weapon accuracy and a bunch of other stuff into account.. Fallout 3 is pretty easy and leans way more action-y than anything. NV's more of an RPG.
Even perk design in New Vegas is far more complex than anything F3 had. There's a lot of reading and planning ahead to build optimally in New Vegas. In F3 it's like... Remember to pick Demolitions Expert if you plan on using Explosives much, pick Damage Reduction perks, pick VATS perks, Finesse, Better Criticals, you're all good. You'll have enough skill points to max every weapon skill, damage seems to scale directly off of that, and there's no strength or skill requirements to use anything. Wanderer's literally enough of a badass to grab a Chinese Assault Rifle off the floor and go to town on a pack of supermutants and easily win. Courier would be plotting how to best attack them from stealth with a sniper rifle he took a month to master, needed to get two different implants to even lift, and two weeks gambling at the casinos to acquire after doing all the drugs known to man just to stand a chance.
TL;DR: Fallout 3 is easy peasy and very accessible, which is a big part of why it was so popular, basically an action game with RPG levelling. New Vegas is big brained game for big brained munchkins, much in the spirit of the originals. Fallout 4 is even more dumbed down than 3.