I think it’s rational in the same sense as insurance. Insurance is a small cost to cover the costs of a potential event that you can’t afford. Lotteries are a small cost for a potential event that you can’t afford not to benefit from.
The key is that in a well-functioning society and wisely-lived life, you don’t need to spend the cost on lottery because you can afford life/retirement without it’s winnings.
I don't think it's reasonable to compare the risk of suffering a large loss with the risk of missing out on a large gain.
If your annual income is $N, missing out on a gain of $N is bad, but not nearly as bad a suffering a loss of $N.
The median American has $8,000 in their bank accounts. Most of the individual people buying insurance generally can't lose $N, because they have <$N - whatever amount the insurance claim is for is a number that they would never be able to pay, e.g. crashing your car into a semi-truck/lorry carrying a lot of expensive stuff or generating medical bills for people in other vehicles, or insurance for an ER visit that costs $800,000. Some individuals buy insurance for things they could pay, and just want to avoid the potential risk, but this is not the majority of people who buy insurance.
Both cases (lottery and insurance) are paying an affordable amount of money for a chance to avoid complete financial ruin.
That is in interesting take. An underfunded insurance. "Your only chance is winning the lottery".