alt Hacker NewsI love postgres and use it heavily, but I still don't fully understand how it overlook MySQL. Maybe because of Heroku adopting it.
MySQL was generally faster, and while MyISAM was a bit limited Innodb was pretty powerful, and you had the choice. It was also simpler (imo) and avoided a lot of the xid/vacuum issues.
That said, still love Postgres. But at the time it started eclipsing MySQL, MySQL felt better positioned.
Replies
I always thought Postgres was the one that did correctness first, then performance, while MySQL was the inverse. I also enjoyed Postgres documentation. But in practice I have very little experience with MySQL, but I do recall it liked to silently coerce invalid dates and its UTF-8 wasn't quite UTF-8.
And MySQL apparently still doesn't support transactional DDL (i.e. BEGIN, ALTER, ALTER, UPDATE, COMMIT), which is quite nice for db schema version migrations.
Back in the day, the sentiment was the MySQL was more-performant but the criticized tradeoff of having "cut corners". I still remember when their transaction support InnoDB table engine came out. Anyway Postgres was viewed as slower but more standards compliant - so mature architects preferred that. MySQL, in my opinion, fell into default usage among LAMP stacks and PHP-using kiddies. Postgres took the crown over time.
MySQL had some problematic design decisions initially. They might be fixed now, but the impression remained. And later there was the added complication that they were bought by Oracle, so you didn't really know how this would turn out in the end.
PostgreSQL also had more features back then, e.g. the JSON support is very nice if you need to do anything that doesn't neatly fit into the relational model.
Maria, MySQL, Oracle shenannigans, perhaps.
Also postgres is a "proper" db, so I'm glad it generally won out.
Maybe things have changed, but my memory of MySQL ~15 years ago was that it was so... hacky. Basically, the (non-strict) JavaScript of the RDBMS world.
MySQL was a proven solution back in the day, i.e late 2000s. Github/Twitter/Heroku etc were using it. In the past 10-15 years, Postgres has come a long way.
In the times when everyone was installing Apache + PHP + "Some database" stack, the easy path was to use MySQL for a very simple reason: it had ready to use MS Windows installer.
Another thing: those were times when web applications were practically 99% reads, and not so great ACID was a non-issue.
Postgres is OK, but it has really a lot of quirks that are not that obvious.
I've not interacted with mysql a whole lot, but when i did I was regularly surprised that it didn't have stuff I was missing from Postgres. Off the top of my mind:
- Query planner is much worse (just yesterday I had to USE INDEX to sped up a query by 300x, I'm near-certain postgres would just have gotten it right) - Indexes are much more limited: no GIST, no GIN - No transactional lock (`pg_advisory_xact_lock` in postgres). This one was very surprising, it's a really useful thing and I had to implement it myself as a lock table