So looking at various benchmarks, reports and tuning guides, it does look like the spinning disks performance really suffers from zfs fragmentation. I haven't seen those before, but also haven't dealt with databases on zfs either. Something to keep in mind I guess.
Edit: after reviewing a few benchmarks, the outcome seems to be - even on SSD, make sure you actually want the zfs features, because ext4 will be a lot faster.
Yeah, it's a tradeoff. Zfs gives you easy data integrity verification (and recovery if you have redundancy), easy snapshotting, easy send/recv. But you lose out on modify in place, and unified kernel memory management (at least on FreeBSD and Linux, maybe it's different on Solaris?); both of those can reduce performance, especially in certain use cases.
IMHO, zfs is a clear win for durable storage for documents and personal media. It's not a clear win for ephermeral storage for a messaging service or a CDN. If you don't mind running multiple filesystems, zfs probably makes sense for your OS and application software even if your application data should be on a different filesystem.
Because there are various mitigations and configurations involved if you're trying to do lots of small random IO for ZFS, and I've not heard people giving the advice of "just don't" in most use cases.
Edit: after reviewing a few benchmarks, the outcome seems to be - even on SSD, make sure you actually want the zfs features, because ext4 will be a lot faster.