As I periodically state here, the real world will have to rebuild itself around NAND latency. Long latency is a given, it looks like it'll never go away, but queueing brings immense acceleration. Yes, I understand it's far from trivial for developers to achieve that.
Optane solved it, but was never successful enough in the market to get the investment and improvements that NAND has had. NAND SSDs started life at SATA2 speeds with 200ns latency good for 5K IOPS and now were at >10GB/s with 200K IOPS.
Optane's starting point was better than NAND's starting point, but unlike NAND, Optane wasn't entering the market as a revolutionary new technology that was desperately needed. SSDs are good enough and there's little call for lower-latency storage, especially not at the prices Optane was selling for.
Given NAND SSDs are here to stay, the hope is (as you say) that the real world will adjust. Hopefully NAND-aware software, drivers, and OS evolve to the point where hardware tiers can be managed seamlessly from CPU to RAM to cache to <
small 3D Xpoint buffer?> to pSLC cache to TLC/QLC NAND.