The current recommendation as documented for the 2020 DNS flag day for the default EDNS buffer size of 1232 bytes is selected to get the maximum buffer size while avoiding IP fragmentation in essentially any network.
The IPv6 spec mandates a 1280 bytes MTU as the baseline.
Counting backwards from that you have: 1280 (mandated minimum MTU for IPv6) - 48 (length of IP and UDP headers) = 1232 (available payload length).
IPv4 does not mandate a useful minimum MTU value, but in practice the expectations are not lower. For one thing, regular Ethernet has 1500 bytes MTU (various tunneling solutions may take away a little from that).
The reason why you want a large buffer size is to avoid getting a truncated response (
TC flag), which triggers the client to retry the same query over TCP. Opening a TCP connection is comparatively slow, so it's beneficial in terms of performance when this can be avoided.
Strategies like starting at a larger buffer size and shrinking it "if it didn't work" were there to maximize the benefits of avoiding truncation.
Avoiding IP fragmentation is essentially getting more priority with the explicit recommendation of having a default 1232 buffer size across the board.
The reason why you want to avoid fragmentation is simply that it causes problems. There are reliability concerns (fragments may simply never arrive) as well as security concerns (fragments are more spoofable).
The reason why the original EDNS0 buffer size specification was not as strongly worded regarding avoiding fragmentation (although this was mentioned) and the interpretation of the spec was in many cases not very conservative is, to my understanding, simply because the extent of the negative effects was not as clearly understood at the time.
Running DNS with often quite large buffer sizes (eg 4096 by default) has made these problems evident, though.