I have an air mattress for guests in a pinch.
It's a twin that inflates to a decent height, which is preferable to the ones close to the ground. It, along with the sheets, pillow, and blankets, are stored in a cubby in the guest suite. I charge $30 (total, not per night) for the 5th person who uses it.
My experience differs from @Robin4 & @Gordon0's. Guests really do seem happy to have the option.
Perhaps that's down to location. Any kind of last minute lodging is exceedingly hard to find in my little vacation destination town. It's often wedding guests who ask about adding a fifth person. Typically it's a person who couldn't schedule PTO until the last minute, or their lodging fell through. Were it not for the air bed, they wouldn't be able to attend the wedding at all.
Since it isn't an ideal bedding situation, the 5-person listing isn't publicly searchable. When there's an ask, I do a "change reservation" to move them from my regular 4-person listing to this "hidden" one that describes the twin air mattress and allows a guest count of 5.
How often do you use your mattresses? Does the foldable type you're talking about require a mattress? The storage and schlepping would figure heavily into what I'd choose next. If you could find reviews of the style you saw at Costco, that might tell you how comfortable they are compared to inflatibles.