I've read Jeffs essay. I've used programmers.se and basically vowed never to go back after a few years because the moderation itself is piss poor (there one person gnat who's sole mission is to close every new question unless it's asked by one of their inner circle of buddies). It's really a joke. There's literally no reason for programmers to exist if not for expert opinioned discussions but they started cracking down on that and rendered the site useless. It's not just me either. Other good contributors have posted the same reasons for leaving in meta and in other forums.
I think you're conflating useful questions and good moderation with opinionated Q/A. I don't think anyone wants SE to be yahoo answers. But a question like what is the correct way to implement IO multiplexing is really useful. I think in SOs crusade to rid the site of opinion-based questions that are hard to handle, they threw the baby out with the bath water and got rid of some of the most useful discussions as a byproduct. Is SO dead? No. But I certainly get less utility out of it these days because I'm less willing to contribute when 90% of the time questions are met with instant downvotes and flags because someone didn't understand the finesse in a question and/or didn't assume positive intent so they could justify being pedantic assholes. I'm pedantic too. I draw the line at using it as a reason to be an asshole though.
I'm not saying SO didn't do its homework. But I am giving one data point that feels SO's usefulness has been marginalized because of the way the community has interpreted "no opinion based questions". I feel that zealotry has spilled out into the general air at stack overflow and it's no longer engaging or fun for many people to contribute these days. Take that for what it is, I guess.
They gave it a try - https://stackoverflow.blog/2010/12/17/introducing-programmer... and it veered off into unmanageable and at the end of the day, uninteresting https://stackoverflow.blog/2010/09/29/good-subjective-bad-su...
It had a really big initial peak, but people didn't stick around. https://meta.stackexchange.com/a/200144
There's a place for such questions, it just isn't Stack Overflow. It isn't the trash either... but its that the structure and the framework of Stack Overflow doesn't work for that sort of question.
The problem is that the endless debates tend to drive away the people who provide the answers. Look at the questions on https://ai.stackexchange.com and consider some of the older ones https://ai.stackexchange.com/questions?page=30&sort=newest to the newest https://ai.stackexchange.com/questions?page=1&sort=newest . They really had a problem with the... science fiction questions https://ai.meta.stackexchange.com/q/1283 and appear to have a done a strong change. Sure, those are interesting, but if one wants to ask questions of the experts, they need to ask questions the experts want to answer. There's a site that handles those questions better... https://worldbuilding.stackexchange.com/questions/tagged/art...
The main thing is that it takes some degree of moderation to keep the people who have the answers that people want. This means matching the site to the vision of the site that the people who have answers want to visit... and that's a lot of clauses. Without that moderation striving for what the site could be, you get https://answers.yahoo.com/dir/index?sid=396545663&link=list and there's a reason that experts tend not to go there and try to answer those questions.
As to the its a human issue, not a technical one - I'd suggest giving http://www.shirky.com/writings/group_enemy.html a read. The first of the three things to accept.