My laptop has died, and the new one I'm thinking of getting has both a 256GB NVMe drive and space for a 2.5in drive. I plan to populate that space with the 240GB SATA SSD from my old laptop. (It's a year old, has 90% lifetime remaining, and should see less intense use here than before, so it's unlikely to break.) This will get me a total of 496GB of space.

However, I am lazy and would prefer not to have to decide which files go on which drive. That should be automatic. I want them merged into one filesystem, mounted as my rootfs. I want the system to be smart and put often-used files on the NVMe drive, bumping infrequently used files onto the SATA drive if the NVMe drive runs out. If possible, I'd also prefer to be able to mount each disk on its own for disaster recovery and reliability purposes. I also don't want FUSE because it has too much overhead.

However, I can't find anything that looks to be able to do exactly what I want:

One filesystem on RAID 0: NVMe will get bottlenecked by the SATA drive, will cause some storage space to go to waste, one drive failure causes loss of all data

One filesystem on LVM: As far as I can tell, data goes wherever it feels like with no preference for the faster drive, one drive failure causes loss of all data

Two filesystems joined with overlayfs: Looks like one fs must be read only

Two joined with mergerfs/mhddfs: FUSE, can't find mentions of it supporting preference for the faster drive so I don't think it's supported

Unionfs/aufs: can't find mentions of it supporting preference for the faster drive so I don't think it's supported

I think joining two filesystems is the way to go, but I can't find anything that looks to do quite what I need. Was I wrong about the capabilities of something I dismissed, is there something I didn't find, or is there actually nothing that does what I want? (I'm on Arch Linux if it matters.)

I think you need to split your files/dirs first, for example /home will be stored on ssd and /usr /etc (and so on) on your NVMe. There is no easy way to achieve what you want.
Looks like a persistent cache for me. I think a smart solution like this will need a lot of resources and makes the system complicated.

I don't think a smart solution will make the system complicated. It's just another layer of abstraction, on top of filesystems, LUKS, LVM, and device drivers that are already there. I think the real reason it doesn't exist is because not enough people want it.

Anyways, since it doesn't exist, I'll split my data manually.

Probably you are right, yes. Probably not that easy to implement....