I think he's fucking brilliant, tbh. He toes the line well between being a constant threat but also being escapable enough to not make him a complete nightmare. If you're very quiet or still and hide he'll even walk right past you... the worst thing you can do is panic. The game also does really well at giving you warning in that you'll very often hear his footsteps and hear the music that plays when he gets close fading in before you see him (apart from when it's an engineered jump scare), as once he's in the room the music will start and you'll hear the footsteps from a mile away.
The footsteps thing is a great tension-adder, too, since I got fooled more than once where I thought it was zombies, opened a door and wham, there he was - nearly shit myself. The inverse has been true, too, where I've been scared to open a door as I've thought it was him, then discovered it was just zombies.
The thing that makes it work is the design of the RPD. Rarely will you ever be backed into a corner and forced to go past him - usually you can turn tail and kite around to get to where you need to go in another way, losing him in the process. Later areas don't always play by these rules, but you're better equipped by then. I also really like the structure: he doesn't show up at RPD until you've explored the lion's share of the building, so you have plenty of time to become familiar with it all. When he shows up, it tests your knowledge of the building, mastery of the map and the routes around it - and also forces you to approach it in a different way, as suddenly you want to not fire guns unless absolutely necessary because it'll attract attention.