Very interesting question! I do not have a full answer, but here are a few cases when one can say something not completely trivial. I will assume as in your last paragraph that $X = {\rm Spec (S/(f))}$ and $Y={\rm Spec(S)}$. Furthermore, let's assume $Y$ is normal.

Then $X_{\rm red}$ would be Spec of $S/I$, where $I$ is the intersection of the minimal primes $P_1,\dots,P_n$ of $f$. The main points now are:

1)The isomorphism class $[I]$ is an element in the class group $Cl(S)$, equal to $[P_1]+\dots+ [P_n]$ in that group.

2)$X_{\rm red}$ would be Cohen-Macaulay if and only if $I$ is CM as an $S$-module, as can be seen from the exact sequence $0\to I\to S\to S/I\to 0$.

**Thus, knowing the Cohen-Macaulay elements in the class group $Cl(S)$ would be helpful.**

Let me discuss some concrete examples. First, if $S$ is an UFD (e.g if it is the cone of some smooth projective complete intersection of dimension at least $3$), then $X_{\rm red}$ will always be CM. That is because $Cl(S)$ is trivial, so $I$ is isomorphic to $S$.

Now, let's say $S= \mathbb C[x,y,u,v]/(xy-uv)$. The class group is isomorphic to $\mathbb Z$, generated by the class of $P=(x,u)$ with $Q=(x,v)=-P$. The class $n[P]$ is isomophic to $P^n$ if n positive and $Q^n$ if $n$ negative, so has $|n|+1$ generators. The only CM elements must be isomorphic to $S$, $P$ or $Q$. In other words, $I$ is CM *if and only if* it has at most $2$ generators. Even just knowing the number of generators of the minimal primes $P_1, \dots, P_n$ can help you decide. For example, if there are $3$ primes with $2, 3, 7$ generators than they can not add up to $0$ or $+-[P]$, thus $I$ will not be CM.

The CM elements of the class groups have been studied by people such as Bruns and Gubeladze over toric rings. For example it is known, see the intro here that all torsion classes over such rings are CM. It provides another sufficient criterion: if $S$ is a toric ring, and the multiplicity of $f$ at each $P_i$ is constant, then $X_{\rm red}$ is CM.

In many cases (toric, determinantal) one can prove that there only finitely many such CM isoclasses. (Shameless plug: K. Kurano and I have had a few results on this problem for more general settings, it is being written).