I 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.

▲

williamdclt an hour ago | parent | next [-]

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

▲

atherton94027 an hour ago | parent [-]

At least you have access to USE INDEX on MySQL. On Postgres it's not rare to have a query suddenly perform awful in production because some switch flipped in the planner and now it's picking some random index

	▲	tux3 38 minutes ago \| parent [-]
		Good news is that they just added a plan stability feature in pg19. It's actually full planner hints, so you can edit the plan to whatever you want if you have no fear, but the main motivation is exactly index stability.

▲

_joel 2 hours ago | parent | prev | next [-]

Maria, MySQL, Oracle shenannigans, perhaps.

Also postgres is a "proper" db, so I'm glad it generally won out.

▲

pandinus an hour ago | parent | prev | next [-]

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.

▲

fabian2k an hour ago | parent | prev | next [-]

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.

▲

bingemaker an hour ago | parent | prev | next [-]

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.

▲

jeremyjh an hour ago | parent [-]

MySQL was always behind in terms of features. In early 2000s it had very limited constraints. Most people were running in ISAM backend and did not even have transaction support. It was being used by people who did not understand how advanced relational DBs were being used. What changed is Postgres overtook its actual competition, which were Oracle, SQL Server and Sybase.

MySQL caught up as well as far as I know, but it still may have some poor defaults that are widely used.

▲

roryirvine an hour ago | parent [-]

In the early days, PostgreSQL was so much more awkward to deal with. Crash-prone at first, and then there was the whole business around having to drop the db during upgrades. It didn't really match MySQL operationally until around 2002.

During the dot com era it was common to develop and launch on MySQL with the intention of migrating to something else if they became successful (though your typical LAMP stack developer regarded Oracle and SQL Server as being deeply 'weird', so many were willing to stick with MySQL despite the well-known limitations of MyISAM).

From where I'm standing, it seems that PostgreSQL became clearly preferable for new projects from the mid 2000s onwards, but it was only the Oracle acquisition that began to push existing users off MySQL.

	▲	jeremyjh 11 minutes ago \| parent [-]
		I would agree with that, although I personally found it preferable in 2001-2002, since it matched the feature set of SQL Server in all the important ways that MySQL didn't. I think it may have been late 2000s before it was clearly preferred for new projects and even later before it was the thing you had to explain why you weren't using it. edit to add: MySQL did have operational advantages even later than mid 2000s since it had master/master replication from very early (I don't know how sound it was, I doubt it was perfect). That was a real reason to choose it. We still don't have it in Postgres without extensions and even there citus is not really the same.

▲

piokoch an hour ago | parent | prev [-]

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.