What is position:relative useful for?

I've used posotion: relative in the past for a container element, with absolutely positioned elements inside it (like a centered three column layout). For example:

I don't give the container any offset, but positioning it with position: relative allows me to absolutely position the columns relative to the container. If the container is set to position: static, as it is by default, then the columns will be absolutely positioned relative to the browser viewport, not the container.

It's useful if you want to offset an element without futzing around with margins and its consequences for other elements. Let's say you want to intentionally have an image poke out of the side of a container and (nearly) overlap with some content in a container beside it.

------------- -----
|           | |   |
|   X  -------| Y |
|      |  a  ||   |
|      -------|   |
------------- -----

Container a is part of the normal text flow of X and you want to keep it that way, you just want it poking out of the side a bit as a neat effect. If you do that with margins, it may get really messy and reflow some of your other content.
By using position: relative; left: 10px; you can get that effect easily without the side effects.

I use position:relative; so that superscript characters can still ascend above vertical-align: top; but doesn't allow them to mess with the leading of my paragraphs.

sup { 
    font-size: .7em;
    vertical-align: top;
    position: relative;
    top: -.1em;
}