I am currently enrolled in a General Relativity course, and was taught about the connection but I can't really wrap my head around it qualitatively. All I can think of is that it must have something to do with the coordinate system that one uses to describe a space(or space-time) but I can't give it a geometric interpretation**(Check the 2nd EDIT)**.
Thank you.
EDIT 1: I am searching for an explanation in terms of the curvature of the space and the coordinates.
EDIT 2: Upon searching for an answer, I found that the relation of the connection with the covariant derivative offers some insight: The connection term in the covariant derivative is an extra term to the normal derivative that is there in order to account for the changes in the coordinate basis vectors. If anybody could use this type of logic to give a complete geometric interpretation of the connection, it would be great!
Maybe it helps to look at some simple example.
In this example, I just take the normal Euclidean plane with normal Cartesian coordinates. However I attach a non-standard basis to each point. Namely, to describe vectors at point $(x,y)$ I use the basis $$e_1 = \begin{pmatrix} \cos x \\\sin x \end{pmatrix}, e_2 = \begin{pmatrix} -\sin x \\\cos x \end{pmatrix}$$ So you see, the basis in general is different at different points (and not the slightest related to the metric!). It is, however, differentiable.
Now let's further assume that on my Euclidean plane, I have a constant vector field $$v(x) = \begin{pmatrix}a\\b\end{pmatrix}$$ So nothing very interesting. Except that I'm now going to express it in my local basis. Then I get $$v(x) = v^1 e_1 + v^2 e_2 = (a\cos x + b\sin x) e_1 + (-a\sin x + b \cos x) e_2$$ That is, although the vector field is constant, the components of the vector field in my contrieved basis are not; instead they depend on $x$ (the reason why they don't also depend on $y$ is only because I chose the basis that way).
In particular, we get for the partial derivatives of the components: \begin{align} v^1{}_{,x} &= -a\sin x + b\cos x & v^1{}_{,y} &= 0\\ v^2{}_{,x} &= -a\cos x - b\sin x & v^2{}_{,y} &= 0 \end{align} This apparent variability of the vector field is only due to the change of the basis; we have actually defined the vector field to be constant. But of course, not all vector fields are constant, so the question arises: How can we distinguish between actually changing vector fields and component changes that are just artefacts of the vectors?
Well, we are still in an Euclidean space, so we can just calculate the true change. So let's say we start at point $(x,y)$ and move along the path $(x+t,y)$, so we recover the derivative in $x$ direction. Then we have \begin{align} \frac{\mathrm dv}{\mathrm dt} &= \frac{\mathrm d}{\mathrm dt}(v^1 e_1 + v^2 e_2)\\ &= v^1{}_{,x} e_1 + v^1 e_{1,x} + v^2{}_{,x} e_2 + v^2 e_{2,x} \end{align} If we write this "true derivative" with semicolon instead of comma, we + therefore get for the components of $\frac{\mathrm dv}{\mathrm dt}$: $$v^i{}_{;x} = v^i{}_{,x} + v^1 \omega^i(e_{1,x}) + v^2 \omega^i(e_{2,x}) = v^i{}_{,x} + \omega^i(e_{j,x})v^j$$ where I used the notation $\omega^i$ for the dual basis of $e_i$, that is $\omega^i(e_j)=\delta^i_j$, and in the last step the Einstein summation convention.
As you see, the "true" derivative decomposes into two parts: The component derivative, and a correction term. The correction is the contraction of the vector with an object with three indices: An upper and a lower index for the basis, and an additional lower index for the coordinate direction in which we are differentiating. These three-index objects are the connection coefficients, $\Gamma^i_{jk}=\omega^i(e_{j,k})$.
Now of course nobody would choose such a strange basis. Instead, the basis is chosen to point in the direction of the coordinates. This means that the basis indices coincide with the coordinate indices; that is, all indices correspond to the same set of directions. Therefore you don't have to distinguish between those two types of indices (unlike above, where the basis indices were $1$ and $2$, while the coordinate indices were $x$ and $y$).