I've noticed that a lot of message queuing systems have an "delivery at least once" property. What exactly does the possibility that a message could be delivered more than once buy you?
For different applications differently delivery QoSes are tolerable. For instance, if your messages are idempotent (either by design or nature) then "at least once" is equivalent to "exactly once" and may be much faster/easier.
Generally, "exactly once" demands the least of the application and the most of the broker.
[0] I suppose you could have "any number of times" as well, but that's no guarantee at all.
The network could cut at any moment during a transaction.
If message consumers don't send acknowledgements, and the queue considers the message sent as soon as it has been successfully written to the network connection, then it might never really be delivered and processed. You can't be sure if it really got to the destination before the network cut out, or something crashed. This is "at most once" - it may have been processed once, or not at all.
If message consumers acknowledge messages after they've finished processing them, and the queue waits for that, then the acknowledgement might be the thing that is lost. The queue sends the message to a different consumer if it doesn't get an acknowledgement before a timeout, but the message might have been processed already, and the consumer crashed or the network partitioned just before the acknowledgement. This is "at least once".
You could consider a final scheme - consumers acknowledge receiving the message, before they process it. The process could crash just after the acknowledgement, or the ack could be lost without it knowing. So this isn't "at least once" or "at most once", it's "no idea".
Or maybe they even wait for an ack that the server got the ack, before processing. The process could still crash after sending its ack or after receiving the other ack, this is still just "at most once".
"At most once" is usually not useful. Usually you want "at least once", and then logic specific to your data to resolve "more than once" in an acceptable way.
However, it is a benefit in that allowing for more-than-once-delivery affords better performance. Think of it from an algorithmic perspective: if you want to send a message exactly once, you have to wait around for acknowledgement that your message was accepted.
If you instead don't mind sending duplicates, you can fire off the first copy, wait for an ack for a while, and then fire off again to another node (in case, perhaps, there was a network partition). If it turns out that both copies arrive at their final destination intact, your application now has to deal with duplicates. But, that's faster than the alternative.
So it puts more burden on the developer to deduplicate where necessary (or program in a model where duplicates don't matter anyway, such as with CRDTS), in exchange for greater throughput.