The only real issue is handling bad input, as you never get an error with decoding e.g. ISO-8859-1; for a lot of applications you need to handle potentially malicious input, so you can do it there, but even for trusted input, there is a lot of really-broken external programs that output "UTF-8" or "UTF-16" (scare quotes intentional).
I really think a lot of the problems with unicode is that a lot of languages/libraries try to handle it transparently, and that just doesn't work; encoding/decoding is part of dealing with external formats, and trying to do it transparently means that it will fail unexpectedly.
That said, if you think Unicode is a pain, try storing and retrieving "饾挏" in any other encoding. I'll stick to Unicode, thanks. :)
I think that depends heavily on what programming language and frameworks you are using.
Slides for that talk and two other Perl-related Unicode talks are at http://training.perl.com/OSCON2011/index.html
Specifically the talk titled "Unicode Support Shootout: The Good, The Bad, & the (mostly) Ugly"
It's a year old now, but it's still relevant. It gives a very detailed look at unicode support across JavaScript, PHP, Go, Ruby, Python, Java, and Perl.
But storing a stream of UTF-8 and retrieving it on command is not remotely difficult. You almost have to go out of your way to screw that up.