I assume that even a retired Sam would train new hunters, give advice, set up waystations and teams, collate new data on monsters, maintain a weekly email newsletter, let people know about exciting sales of wolfsbane and dead man's blood, etc..
I can understand the irritation that after 15 seasons of battling werewolves, vampires, poltergeists, serial killers, Satan, archangels, Leviathans, mutated angels, Robert Singer, the King of Hell, the Men of Letters, God's disgruntled sister, the FBI, God himself and dental cavities, it's frustrating for Dean to be taken out by a vampire and a nail. But to me, that just captures how death sucks whether it's some self-sacrificing explosion or a small scuffle. Death is never happy in real life, and SUPERNATURAL was quite true to life in that respect.
In addition, Dean has died 112 times: 106 freak accidents in "Mystery Spot," a car accident, dragged to hell, shot to death, becoming Death and self-induced death to talk to Death (twice). The only death that the show hadn't done at this point was something small and low-key. As for whether or not Dean had to die -- I think that it was a dramatic necessity because the show had repeatedly resurrected Dean (and Sam). But this was to be the series finale, and rather than leave fans wondering how Sam and Dean would come back for some hypothetical Season 16, they showed Dean die and in the afterlife to make it clear that this was the end.
There's also the fact that SUPERNATURAL has had a decidedly unromantic view of death, often at its own cost and to the outrage of fans, specifically with Kevin Tran and Charlie -- but I'm not sure SUPERNATURAL was wrong to kill those characters the way it did. There was a strange sense of consequence to Kevin's death. He was only ever expected to be a guest star, but Osric Chau won over the fans and the cast and crew, so they kept him on -- but the character was in a difficult position. His mother was kidnapped. His girlfriend was murdered. He couldn't protect them.
Kevin was not a fighter, and while the show could have had him training with Sam and Dean and present him as combat capable, there came a point of no return where Kevin had spent so long as a non-combatant that it was too late to overturn it. Kevin was a scholarly student hiding in the bunker, incapable of defending himself against monsters like Sam and Dean -- so his eventual death at the hands of any threat that could gain access to the bunker was inevitable.
The show refused to excuse itself or Kevin from this painful but inescapable path of cause and effect, only giving Kevin the slight comfort that in death, he could bid farewell to his mother before Chuck sent him to heaven -- except it turned out Chuck sent him to hell to keep him in circulation for future episodes of his favourite show. (Presumably, Jack sent Kevin to the rebuilt heaven afterwards.)
And with Charlie, we had a somewhat inverted version of Kevin that came to the same unfortunate conclusion. Charlie rose to the challenges of becoming a hunter: she became combat ready, she drew upon her past skills to defend herself in her new life, she proved able to disguise herself, she was fit for life as a wanderer and researcher and fighter. But the life of a hunter is constant, endless, repeated exposure to deadly situations with enemies who are stronger than any human. Hunters survive based on knowledge of monster's weaknesses, partnerships, teams, preparations -- but those can fail and in the end, Charlie was trapped in a room with a monster too strong for her to beat and she died.
This was incredibly offensive to many fans. Felicia Day was offended. The cast were offended; when Season 10 showrunner Jeremy Carver was asked to explain this at a convention, the cast and crew around Carver took a step back from the microphones; they would not defend him or side with him. Carver said that it was where the story took him and ultimately, Charlie's death was upsetting and hurtful because death is upsetting and hurtful and most hunters die horribly.
The fan protest is that Sam and Dean have repeatedly been spared death and that surely a beloved character played by a beloved actress had earned the same privileges as the straight white men who lead the show; that surely a lesbian and a female hunter and a beloved role model to young girls and LGBTQ viewers should be excused from the likely outcome of a dangerous life, and that refusing to give some of Sam and Dean's privilege to Charlie and Felicia Day proved ghastly and horrific.
And I can see that -- except the show seems to have addressed that with Season 14 - 15, noting that Sam and Dean were spared because Chuck spared them. Because they were his "favourite show." Season 15 in "The Heroes Journey" further notes that Sam and Dean have been granted many special exemptions by being the leads of Chuck's favourite show that other characters like Bobby, Garth, Kevin and Charlie don't receive. Sam and Dean have exemptions from death, credit limits, parking tickets, the common cold, car battery failures, dental decay, indigestion and food allergies.
It's also fair for fans to feel sad that many guest-stars and recurring characters never got an on camera resolution. This was an unfortunate effect of the pandemic making it impossible to bring back many guest stars for the finale. I am sad that we never saw Jack face off against Harper Sayles the necromancer again. But -- the unfortunate truth is that any show that makes it five years (never mind 15) is going to have unresolved arcs because TV is perpetually hit by actor unavailability or sudden changes in the rush to crank out 20 - 25 episodes a year. No series escapes this.
From a creative standpoint, SUPERNATURAL absolutely grasped that it needed to give its guest-stars more screentime and development, especially its female characters. Over several seasons, the show built a cast of recurring female guest stars in Jody Mills, Donna Hanscum, Alex and Claire and then built momentum to launch a spinoff in WAYWARD SISTERS, a place where they could finally offer their female players the same advantages as Sam and Dean. The spinoff wasn't picked up due to (a) Kathryn Newton's success in BLOCKERS and DETECTIVE PIKACHU making her more expensive and (b) the CW having a lot of other shows that year that wouldn't come with the Kathryn Newton pricetag. The SUPERNATURAL creators had absolutely no control over that; they saw their own flaws with women, they attempted to balance it out, but their measure was rejected by the network.
Also, SUPERNATURAL addressed the Charlie issue by bringing in an alternate universe version of Charlie which proved satisfactory. This would suggest to me that the issue was not killing off Charlie, but rather killing off Felicia Day, and since Felicia Day returned to the show, SUPERNATURAL's point with Charlie's death has a clearer validity: hunters lead dangerous lives and will likely die during a hunt, their deaths will be violent, and death is always upsetting. And ultimately, once Chuck was no longer forcing Sam and Dean to be the star of his favourite show, Dean was treated in the same way as Charlie: he lived a life of constant kill or be killed situations and he was killed. His number came up.
Could Jack have saved Dean? Of course. But Jack freed Dean (and Sam), liberating them from the endless cycle of death and resurrection and being at the center of Chuck's favourite show. To resurrect Dean again would have been to steal his freedom instead of granting him peace.
I think it's fine to dislike it. But it was a perfectly valid writing choice and there weren't that many options that hadn't been explored after 326 episodes.