Just got rid of our 40 gallon standard tank water heater this summer. Went with a Rinnai Sensei Tankless RU199IN 199,000 BTU unit.
Pros - endless hot water, no more standing pilot light, super efficient, super compact.
Cons - slight noise when on and bit more noise for a minute when hot water is turned off (this will also be dependent on where the unit is located, cost of install and the unit was a lot.
Should run a 3/4 gas line as close to the unit as possible and not a 1/2” line, and I added a vertical condensate neutralizer to the condensate drain line also.