Coming at this from a small sample size, every time I've seen it used has been because some of the developers on a team love it and think it's cooler than the other options. And, over time, the operational experience has gotten better (AWS' Postgres support for RDS/Aurora is all recent, for example); and, in fairness, I'd take psql over SQL Server any day of the week.
Regarding why it has popularity beyond mySQL/mariaDB is still a confounding mystery as far as I'm concerned. The additional behaviors Postgres tends to encourage (I'm looking at you, publisher/subscriber and trigger functions) seems to lead to devs advocating it as 'easy' while those in my position are left to keep the damn thing running.
I developed my preference for PostgreSQL years ago, before MySQL supported foreign key constraints or defaulted to durable commits. MySQL also had this annoying tendency to silently store invalid timestamps as zero. All of these things have been fixed since (I hope?), but I still can’t shake my impression that PostgreSQL takes correctness more seriously.
I would point directly at two culprits kind of under the same umbrella -- Google usurping StackOverflow answers in its search results (as in, displaying a Q&A format where google's 'answer' is just a portion of a StackOverflow accepted answer) and the general SEO poisoning of search results (ad networks / LLMs scraping and rehosting the content, Google failing to return relevant results etc.)
Until seeing this article, though, I hadn't thought about how much I had unconsciously changed my habits to work around the aforementioned issues -- I swapped to DuckDuckGo, started appending site:stackoverflow.com to searches, performing multiple searches around the same content (like grabbing a few keywords from a scraped article and then feeding that into a search of the official documents), and also more aggressively searching GitHub issues
With the caveat that I still use SO relatively frequently -- everything from 'I can't remember this specific syntax' to 'I have an intractable terraform/AWS/k8s issue and need to see if anyone else is experiencing it'
I got so mad about this exact behavior that I started an (occasionally-updated) git repository with no-cruft recipes. https://github.com/byronic/recipes
