Edit: The setting for the question is some abelian category.
From this question I learned that one way to view a short exact sequence $$0\rightarrow A\rightarrow B\rightarrow C\rightarrow 0$$ is as saying that $B$ is an extension of $C$ by $A$. This makes sense to me and has been very helpful, but now I'm at a loss as far as what long exact sequences are telling me. I know they're very useful because they relate the arrows they involve, thereby allowing for calculation, but I was hoping for a simple interpretation not too dissimilar from the one for short exact sequences.
As a particular case, what is the geometric interpretation of the relative homology long exact sequence?
You can always break up a long exact sequence into small exact sequences. Suppose your long exact sequence is: $$\dots \to A_{n+1} \xrightarrow{f_{n+1}} A_n \xrightarrow{f_n} A_{n-1} \xrightarrow{f_{n-1}} A_{n-2} \to \dots,$$ then you can split it as: $$0 \to \operatorname{coker} f_{n+1} \xrightarrow{f_n} A_{n-1} \xrightarrow{f_{n-1}} \operatorname{im} f_{n-1} \to 0.$$ Proving this is a bit tedious, but it follows directly from the definitions. So in some sense a long exact sequence lets you think of $A_{n-1}$ as an extension of the image of $f_{n-1}$ by the cokernel of $f_{n+1}$.
But as you can see that's not really insightful, and remembering the correct indices at all is a feat in itself (I had to revise what I wrote three times and I'm still not 100% sure). In this context long exact sequences are more of a computational tool than anything. As you say they're very useful, but giving them an interpretation like for short ones doesn't really work, in my opinion.
In homological algebra chain complexes are the basic objects of study, and a long exact sequence is a chain complex with zero homology; just like a space can have trivial homology but be very interesting, so can chain complexes...
I'd just like to point out an additional thing: there is something called the $\operatorname{Ext}$ functor that classifies extensions, that is if $A, B$ are modules over some ring $R$, then $\operatorname{Ext}^1_R(A,B)$ classifies extensions of the form $0 \to B \to X \to A \to 0$ (for some suitable notion of isomorphism of such extensions, see Wikipedia for details). More generally the $n$th $\operatorname{Ext}$ functor $\operatorname{Ext}^n_R(A,B)$ classifies extensions of the type: $$0 \to B \to X_n \to \dots \to X_1 \to A \to 0$$ so in some sense you can think of bounded long exact sequences as higher extensions. (If you think about it, this is very related to the splitting I talked about at the beginning, that would allow you to do proofs by induction on the length of the sequence...)