What is the intuition behind short exact sequences of groups; in particular, what is the intuition behind group extensions?

A short exact sequence $1\rightarrow A\rightarrow E\rightarrow Q\rightarrow1$ is really just a fancy way of saying "$E$ has a normal subgroup $A$ where $E/A\cong Q$". [The sequence also gives the isomorphism $\beta: E/A\rightarrow Q$, while $\alpha$ corresponds to the embedding of the abstract group $A$ as a subgroup of $E$.]

Because you care about presentations: if $A$ has presentation $\langle \mathbf{x}\mid\mathbf{r}\rangle$ and $Q$ has presentation $\langle \mathbf{y}\mid\mathbf{s}\rangle$ then the group $E$ given by the above short exact sequence has presentation of the form: $$ \langle \mathbf{x, y}\mid SW_S^{-1} (S\in\mathbf{s}), \mathbf{r}, \mathbf{t}\rangle $$ where $W_{S}\in F(\mathbf{x})$ for all $S\in\mathbf{s}$, and $\mathbf{t}$ consists of words of the form $y^{-\epsilon}xy^{\epsilon}X^{-1}$ with $x\in\mathbf{x}$, $y\in\mathbf{y}$ and $X\in F(\mathbf{x})$. The intuition here is that relators in $\mathbf{t}$ ensure normality of $A$, and so removing all the $x$-terms makes sense. When they are removed you get the presentation $\langle \mathbf{y}\mid\mathbf{s}\rangle$, because of the relators $SW_S^{-1}$. I will leave you to work out where the maps $\alpha$ and $\beta$ fold in to this description.

The above presentation justifies the term extension of $A$ by $Q$: we've started with a presentation for $A$, and then added in the presentation for $Q$ in a specific way to obtain a presentation for $E$.

For a worked example of the above (with some genuinely amazing applications, both in the paper and in subsequent research), look at the paper Rips, E. (1982), Subgroups of small Cancellation Groups. Bull. Lond. Math. Soc. 14: 45-47. doi:10.1112/blms/14.1.45

It is an interesting question when a presentation of the above form does actually define a group extension. This was studied in the paper Pride, S., Harlander, J. & Baik, Y. (1998). The geometry of group extensions. J. Group Theory, 1(4), pp. 395-416. doi:10.1515/jgth.1998.028

Let's say you want to classify all the finite groups (up to isomorphism). You know that there are simple groups, the groups which have no non-trivial normal subgroups. You can think of these as the "atoms." Now you want to classify all the finite groups, not just the simple ones. You might hope that an arbitrary finite group is a product of the simple ones, but unfortunately it's not that... simple.

For example, the dihedral group of order 6 is "built out of" $\mathbb{Z}/2\mathbb{Z}$ and $\mathbb{Z}/3\mathbb{Z}$ in the sense that it has a normal subgroup isomorphic to $\mathbb{Z}/3\mathbb{Z}$ and the quotient is isomorphic to $\mathbb{Z}/2\mathbb{Z}$, but it's definitely not their product. So to finish the classification problem, you need some set of rules for forming "molecules" from the atoms.

The "molecule" rule is equivalent to this: given two groups $G$ and $Q$, classify all groups $E$ such $G$ is a normal subgroup of $E$ with the quotient isomorphic to $Q$. This is exactly the extension problem. If we have an effective way to calculate this for every pair of groups, and we also know all the simple groups, then we have solved the classificaiton problem.