MySQL was the M in the LAMP stack (I’m this old, crap) but it had issues with downright brain dead behaviors and defaults which were only fixed multiple major versions later. In the meantime if you wanted to store what you asked to be stored without silent truncation etc you chose Postgres.
From the user's perspective, it's the little things. The feature set is overall bigger and better than comparable RDBMSs. Not having to deal with Oracle or Microsoft is also nice. But there's not a huge difference in usage vs MySQL.
Those familiar with the internals might say a lot more about how it's faster or something. IDK, wouldn't surprise me if it were.
PostgreSQL's fame is kind of a paradox: it's so good because everyone uses PostgreSQL because it's so good. With the variety and scale of applications PostgreSQL is used for, it has organically evolved to be a swiss army knife of a database.
Basically PostgreSQL is to databases as Linux is to an OS.
Wasn't always this way...even not that long ago. Back when Heroku was getting serious about building up the Postgres service (I helped some...this is circa 2010) the common question was "that's weird, why not MySQL, isn't that how most Rails applications are run?" RDS only had a MySQL product for a very long time, and that followed from market demand. Tutorials for Rails were generally written with MySQL.
Hot standby (streaming replication) was a key feature to arrive around then. Also, I'd like to think a lot of the work Heroku did to engineer Heroku Postgres, and market it, contributed much better Postgres support in web frameworks and their affiliated ORMs in those critical years from 2010-2014, where encountering headwinds in defects in Postgres support for Rails, Node, etc was common.
When RDS came around with their Postgres offering, I'd say at that point, it could be said that Postgres entered a new stage: it was no longer reflexive for engineers to shrug their shoulders at shaky Postgres support in drivers/ORMs/etc like they did before that.
I'm partial to MS SQL Server myself.
So many rave about Postgre. Isn't it just another RDBMS?