I add pets to households just to give the families in my game some life. Some characteristics.
Actually playing those households? Quite rare.
I got tired of seeing too many puddles of throw up and having to take the pet to the vet. I also didn't like it when they always ran away. Always making my sims sad and they had to put out an alert for their missing pet.
Right now I'm playing a single sim and to give her more happiness I did give her a cat. So far so good, not really getting sick and the cat just wanders off every now and then to different neighborhoods. I'm fine with playing pets as long as it's chill like this. Maybe one or two pets per house. Playing with more than that would give me a headache.
The pets are insanely cute though. I don't mind that they're autonomous either, but that's just me.
Overall I feel like they add characteristics to the household and they're cute, but they're not extremely necessary.