There's a big 0->1 jump required for it to actually be used by 99% of consumers -- x86 and ARM have to both make a pretty fundamental shift. Do you see that happening? I don't, really.
I see this happening on ARM world, that is why ARM is working alongside CHERI folks,
https://www.arm.com/architecture/cpu/morello
x86, well Intel has already messed up hardware memory tagging multiple times.
Tbh I can imagine this catching on if one of the big cloud providers endorses it. Including hardware support in a future version of AWS Graviton, or Azure cloud with a bunch of foundational software already developed to work with it. If one of those hyper scalers puts in the work, it could get to the point where you can launch a simple container running Postgres or whatever, with the full stack adapted to work with CHERI.