The game really doesn't give us good umm realistic deaths and probably never will. If I don't have mods but want the suffering.. I usually put them in a position to suffer (usually needs or even bad aura objects) before finally letting them die another way that the game provides. It's kind of hard to set up sometimes though, and can depend on the sims traits whether it will work also.
I think the worst one I had where I kind of felt bad setting it up was the one @babajayne mentioned... cardiac explosion..
Death by drowning is pretty creepy too... get them very low on needs first though because just sticking them in there with no way to get out really isn't good for most stories.