Yeah, what it takes to
really, truly stop parallelism is an unbreakable linear chain of dependencies, like how LZ decompression needs the previous unpacked block to begin work on the next. (Not that LZ unpacking is maxing out people's CPUs these days.) That certainly exists sometimes but isn't nearly common enough to make me doubt the whole enterprise of parallelizing things.
The good news is that using all cores is one of the top things you can do to get work done sooner, and as such we probably will see investment happen. My work is pretty different from AAA games, but still parallelizing a big task is not infrequently lower hanging fruit than tuning the code for one unit of work. We've paid for the whole machine (or cluster) so we might as well use it.
Browsers are an interesting instance because they have a bunch of heterogeneous tasks with weird dependencies between them, and are huge, old codebases where you have to be careful adding threading, but they still manage to make real use of threads (e.g. running expensive compilers, decoding images, rendering, etc.).
Suspect you're saying ~this re: "foundational software", but as languages, tools, and libraries keep adapting to modern hardware, I think using parallelism confidently will become easier and we'll see more of it. Ecosystems change slowly but they do change.
ESR's claim about locality particularly seems iffy. A common task with very poor locality but useful parallel implementations is garbage collection: it thrashes your CPUs' caches, but thrashing all of them at once can still get it done sooner.