You know, nobody ever asks about weight so I'm happy you asked.
There is no hard and fast number wher everything goes to hell. However every pound has an impact. More weight means slower acceleration, slower deceleration, more brake wear, lower fuel economy, faster shock wear, less efficient shock function, etc.
My tactic is to minimize weight change from stock. When I picked my tires, I found that the manufacturer had the same sizes in P-metric (passenger car) and LT (light truck) tires, along with several load ratings. All of this lines out to weight carrying capacity, and by extension, mass from more rubber and more steel / reinforcing material.
Bottom line, the stock weight of tires was around 35 lbs each (don't recall the exact number), the P-metric were 42 lbs each and the lightest (C-load rating I believe) LT in the same size was 56 lbs. After looking at the load rating for the passenger car tire in lbs, I found the rating more than adequate for current and future needs, and saved 14 lbs per corner while increasing weight per corner by only 7 lbs (these are rough numbers, I don't recall the exact ones without looking.
Your mileage will vary... the tire you need should be the one that fulfills your requirements while not being overkill. Running around on heavy E Load LT tires on a mid-size SUV is overkill but I've seen it done and hey, if it makes you happy...!
2005 Grand Cherokee 4x4
1974 International 100 4x4