I am looking for a simple way to understand why do we need infinite-dimensional Hilbert spaces in physics, and when exactly do they become neccessary: in classical, quantum, or relativistic quantum physics (i.e. when particles can be created and destroyed)?
I would like to understand both physical interpretation and the mathematical point of view - what exactly becomes ill defined in the mathematical formalism - is it the commuation relation for quantum mechanics or something else?
The canonical commutation relations are not well-defined on finite-dimensional Hilbert spaces. The canonical prescription is
$$ [x,p] = \mathrm{i}\hbar\mathbf{1}$$
and, recalling that the trace of a commutator must vanish, but the trace of the identity is the dimension of the space if it is finite-dimensional, we conclude that we have a space for which the trace of the identity is not well-defined, which is then necessarily infinite-dimensional.
Hilbert spaces of infinite dimension are necessary, in the minimal case, to describe the non-relativistic quantum mechanics of a massive particle with at least a single real degree of freedom, and they are needed to allow the theory to describe, in general, states with arbitrarily high level of detail and at arbitrarily far-away positions.
However, for any given experiment, you will always be able to find good approximations to the dynamics which use only a finite-dimensional Hilbert space, because any given experiment will involve dynamics over a finite portion of space and at finitely small length scales. The easiest way to do this is via a direct discretization of the position, but you could also do it e.g. by (shameless plug) truncating the photon basis of a harmonic oscillator.
Now, these are quite legitimately called 'truncated' schemes, because once you truncate the dimension required by the full theory you can no longer recover the translation and scale invariance that are required for full generality.
Moreover, as has been pointed out, in any finite dimension the canonical commutation relations $[x,p]=i\hbar$ are not realizable, because in finite dimension $x$ and $p$ are trace-class operators, and the trace of their commutator must vanish. It's important to realize, though, that this is not fatal: you can still get commutation relations like $[x,p]=i\hbar\left[1-(\dim\mathcal H)|\text u⟩⟨\text u|\right]$, where $|\text u⟩$ is some state that's highly unlikely to be reached by your dynamics.
Further, it's perfectly possible to have interesting commutation relations in finite dimension (such as those of angular momentum), and to have quasi-canonical commutation relations on finite dimensions which nevertheless approach the classical limit $\{x,p\}=1$ as $\hbar\to0$ as long as your dimension is allowed to be arbitrarily high for the given dynamics.
However, these are all ugly and artificial schemes, and there is very little reason to prefer them over the perfectly reasonable standard Schrödinger theory, which is why we use infinite-dimensional Hilbert spaces in everyday quantum mechanics.
Hilbert spaces, in general, can have bases of arbitrarily high cardinality. But the he specific one used on QM is, by construction, isomorphic to the space L2, the space of square-integrable functions, and this space as an infinite (but discrete) number of dimensions. The reason you want squere integrable functions is that you want the probabilities calculated from the wavefunction to be finite. When we say that the state vectors must be “square integrable” , this means, strictly speaking, that $\int^{\infty}_{-\infty} \overline{\psi}\psi dx $ is finite.
Maybe it is not relevant to you now, but it will be to someone. The commutation relation $[x,p]=i\hbar$ produces the Heisenberg algebra, and you can easily see that this algebra is solvable. You then have the Lie-Kolchin theorem that says that every finite-dimensional irreducible representation of solvable algebra has to be one dimensional.
This means that if you only have one dimensional representations, everything would commute, and you would not have anything, so the only other option is an infinite dimensional representation.
This explanation is maybe more abstract but I find it fundamental.
You need an infinite dimensional Hilbert space to represent a wavefunction of any continuous observable (like position for example).
Wavefunctions map real numbers, which correspond to classical observables like spin or position, to complex coefficients of some set of basis kets in a Hilbert space. That basis and those coefficients define a ket that can be identified with the wavefunction, is called the "quantum state" of the system, and can be used in calculations.
How is each real number mapped to a complex coefficient? Each basis ket is an eigenvector of some observable. The real number is the corresponding eigenvalue.
It follows from this that a basis in a finite dimensional Hilbert space can only be used to define a ket corresponding to a wavefunction that is defined only for specific eigenvalues, which you might call a "discrete" wavefunction.
For example, you could use a 2-dimensional basis for a system describing the spin of one particle, with two basis kets, |up> and |down>. The wavefunction, psi(), would only be defined for two values, +1 and -1, which are the eigenvalues corresponding to |up> and |down>. psi(+1) * psi*(+1) would give you the probability that the particle was in the "up" state, and psi(-1) * psi*(-1) the probability that it was in the "down" state.
If |psi> is the ket identified with the wavefunction psi(), then <up|psi> gives you the same result, and has the same interpretation, as psi(+1) and <down|psi> corresponds to psi(-1) in the same way.
psi(3) or psi(0.6) don't make any sense here because there are no such observables-- there are only two observable states in this system, and hence an eigenbasis in a 2-dimensional Hilbert space with two eigenvalues can be used to represent it.
But an observable like position is continuous-- we can ask what's the probability that the position is 0.6, or 0.601, etc. So we need a wavefunction defined for every real number, each of which must be the eigenvalue of an eigenvector in a basis in a Hilbert space. Since there are infinite possible values of position, we need infinitely many eigenvectors, and an infinite-dimensional Hilbert space.
This is all explained in this excellent book by Leonard Susskind.
As Scott Aaronson points out here, the de Broglie-Bohm interpretation of quantum mechanics can only be formulated for an infinite-dimensional Hilbert space. The de Broglie-Bohm interpretation isn't "necessary" of course, but it's a neat extra feature that you get when the Hilbert space is infinite-dimensional.
