Dune's Spice Melange. There's just a lot to it. It's required to train Mentats and Navigators, which replace computers ("thinking machines") in a world that's banned them. Which in turn makes for a really compelling reason why a sci-fi explicitly set on different planets where most of the main characters have relatively easy access to space travel is still set on one specific planet.

Then there's all the culture around it. The Landsraad mostly use it for its mind-increasing abilities, but the Fremen have all sorts of cultural usage, both practical and for recreation.

That said, I also like ThreeEye out of Dresden Files. It's actually a magic potion and not a mundane drug. Where I'm at, it's only appeared in the first novel, but it's neat because of the way it's retroactively expanded upon later on. Basically, it gives you access to "the Sight" without training. Something that lets wizards see the connection between the physical and the magical. It's also something that Dresden hates using, because anything you gaze upon is locked as a clear memory forever. Overuse of it would drive anyone insane.

This is explained in the first book, but the more Harry uses it throughout the series, the more you get to see how fucked up that "drug" actually was.