$\def\R{\mathbb R} \def\del{\nabla} \def\G{\color{#8f4f00}G} \def\Q{\color{#004f8f}Q} \def\C{\color{#008f00}C}$ So you find an $n$-dimensional manifold $M$ lying around, and it's got a vector field $V(x)$ defined on every point $x$ of $M$, and you want to do useful things to it. Like maybe you want to take the derivative of the vector field in some direction. So you think to yourself something like $$\lim_{\vec h\to 0}{V(x + \vec h) - V(x) \over |\vec h|}$$ and you hit a wall. For even supposing it made sense to "add a little bit" to a point $x$ in some direction $\vec h$, that subtraction of one vector from another vector at a different place is mighty suspicious.

If $M$ were embedded in some ambient space $\R^m$, we'd be fine: all tangent vectors would live in $\R^m$, too, and we could subtract them to our hearts' content. But that's not how abstract manifolds roll. A tangent vector sitting at the north pole pointing towards Chicago, and a tangent vector sitting in Chicago pointing at NYC are just incomparable. We can only start adding and subtracting vectors once they live at the same point.

So what do we do? This is kind of a weird situation, since we can take derivatives of a scalar-valued function $f$. This is exactly what vector fields are good for, in fact! So much so that one often defines vector fields this way. A vector field $V$ is a function that takes a scalar-valued function $f : M \to \R$ and returns another such scalar function, $Vf$. It has to satisfy:

A Leibniz law: $V(fg) = (Vf)g + f(Vg)$
Linearity: $V(f+g) = Vf + Vg$ and $V(kf) = k(Vf)$ for $k\in\R$.

We call $Vf(x)$ "the derivative of $f$ in direction $V$ at point $x$".

To be able to take derivatives of vector fields, we need to demand some more structure on our metric space. We ask for a metric $g(V,W)$ that takes two vector fields and returns a scalar field, that satisfies

Symmetry $g(V, W) = g(W, V)$
Linearity $g(U+V, W) = g(U,W) + g(V,W)$ and $g(kV,W) = kg(V,W)$.

Given this, we want to define $\del_V W$, "the derivative of vector field $W$ in direction $V$". Now if we bind our own hands by insisting that $\del_V W$ satisfies the following properties:

Torsion-free $\del_V W - \del_W V = [V,W]$
($[V,W]$ is the commutator $V \circ W - W \circ V$, the difference between applying the vector fields in different orders)
Preserves the metric $U g(V, W) = g(\del_U V, W) + g(V, \del_U W)$

then it is uniquely defined. Here's how. Define for the sake of more uniform notation: $$\Q_{UVW} = g(\del_U V, W)$$ $$\G_{UVW} = U g(V, W) = \Q_{UVW} + \Q_{UWV}$$ $$\C_{VWU} = g(U, [V,W]) = \Q_{VWU} - \Q_{WVU}$$ Note that $\G$ is symmetric in its last two arguments, that is, $\G_{UVW} = \G_{UWV}$, precisely because $g$ is. Meanwhile $\C$ is antisymmetric in its first two arguments: that is, $\C_{VWU} = -\C_{WVU}$.

The intuition for why we should be optimistic at this point is that $\{\Q_{UVW},\Q_{UWV},\Q_{WUV},\Q_{WVU},\Q_{VWU},\Q_{VUW}\}$ feels an awful lot like the basis of a 6-dimensional vector space, and all the possible permutations of $\G_{\cdots}$ span 3 dimensions of it (not the full 6 because it's symmetric) and all the possible permutations of $\C_{\cdots}$ span maybe the other 3 (again, not 6 because it's antisymmetric). So maybe there's a clever linear combination of $\G$s and $\C$s that adds up to exactly one $\Q_{UVW}$ in isolation.

Spoiler: There is! It's an Old-Lady-Who-Swallowed-A-Fly story that has a happy ending.

We start with $\G_{UVW} = \Q_{UVW} + \Q_{UWV}$, which at least gives us a $\Q_{UVW}$, although there's that pesky $\Q_{UWV}$ there. Maybe we can send in $\C_{WUV}$ to cancel it out. $$\G_{UVW} + \C_{WUV} = (\Q_{UVW} + \Q_{UWV}) + (\Q_{WUV} - \Q_{UWV}) = \Q_{UVW} + \Q_{WUV}$$ Got rid of it! But we picked up a $\Q_{WUV}$. Maybe we can send in a $-\G_{WUV}$ to get rid of it... $$\G_{UVW} + \C_{WUV} - \G_{WUV} = ( \Q_{UVW} + \Q_{UWV}) - (\Q_{WUV} + \Q_{WVU}) = \Q_{UVW} - \Q_{WVU}$$ Ok, how do we get rid of $- \Q_{WVU}$? Send in some $\C_{WVU}$! $$\G_{UVW} + \C_{WUV} - \G_{WUV} + \C_{WVU} = ( \Q_{UVW} - \Q_{WVU}) + (\Q_{WVU} - \Q_{VWU}) = \Q_{UVW} - \Q_{VWU}$$ Oof, starting to get a bit tired of this. Let's throw in a $\G_{VWU}$ to eat the $-\Q_{VWU}$. $$\G_{UVW} + \C_{WUV} - \G_{WUV} + \C_{WVU} + \G_{VWU} = ( \Q_{UVW} - \Q_{VWU}) + (\Q_{VWU} + \Q_{VUW}) = \Q_{UVW} + \Q_{VUW}$$ Gosh, how many permutations of three things are there? One more try, with $\C_{UVW}$ to eat the $\Q_{VUW}$... $$\G_{UVW} + \C_{WUV} - \G_{WUV} + \C_{WVU} + \G_{VWU} + \C_{UVW} = ( \Q_{UVW} + \Q_{VUW}) + (\Q_{UVW} - \Q_{VUW}) = 2\Q_{UVW}$$ Success! We learned that $$\Q_{UVW} = (1/2)(\G_{UVW} + \C_{WUV} - \G_{WUV} + \C_{WVU} + \G_{VWU} + \C_{UVW})$$ which, expanding our definitions, means $$g(\del_U V, W) = (1/2)(Ug(V,W) + g(W,[U,V]) - Wg(U,V) + g(W,[V,U]) + Vg(W,U) + g(U,[V,W]))$$ and, since we now know the inner product of $\del_UV$ with every possible $W$, we can uniquely recover what $\del_U V$ has to be. This is the Koszul formula for the Levi-Civita connection.