Why is MySQL more popular than PostgreSQL?

When MySQL first started to take off (circa 1998) it had a lower memory requirement. So did PHP.

Memory was the most precious resource at that time, and so MySQL, PHP expanded rapidly.

They are both still going strong from that initial popularity spurt.

.

2. .:: Novice coders do not think about DB corruption ::.

:: If you are new to coding, and have not been classically trained in a Computing Degree, it seems that people don't care for transactions or consistency.

MySQL was/is worse than Postgres on both these fronts, but the users do not care. At all. And if they do care, it may be because they heard that it was important, rather than actually feeling ill without transactions.

.

:: I value my users and would never want to inflict data loss on them if I could avoid it. I also know SQL well from my Computer Engineering degree and industry experience with Oracle / PSQL / DB2. So I use Postgres and have since 1996.

Most people using MySQL don't realize they could cause data loss to their customers. Even if they do, they may still use MySQL and work around failures by having adequate backup strategies that cover them even if MySQL failed at a bad point.

A lot of it has to do with historical reasons which have reinforced themselves over time.

Here are some of the early differences:

* MySQL had an ungly way to allow users to only see their tables. While it wasn't great it was better than PostgreSQL which didn't really have a way to hide other databases.

* MySQL could create and destroy connections quickly. PostgreSQL made more robust connections but they took longer to setup.

* MySQL ran simple queryies much quicker under a light load.

* PostgreSQL database maintence tasks often required the database to be offline.

While these differences didn't make a big difference in a business environment PostgreSQL would have been a better option because:

* Use connections pools and not care about the connection setup time.

* Have a high load and care more about how the database ran under load.

* Have a trusted users and not care that they could see other databases.

* Have maintence periods.

That being said in the business world users used MS SQL or Oracle. Where MySQL took off was shared hosting which:

* Had light usage.

* Limited resources which meant that they often banned connection pooling.

* Had multiple untrusted users.

* Can handle a slowdown but not downtime.

In this situation MySQL was the better option.

This then caused a network effect where most software was written for MySQL so more companies offered it (remember at this time lots of companies only offer static hosting). Since most companies offered it more people wrote software that supported it.

Programs that support PostgreSQL often support MS SQL, MySQL and a number of databases BUT there are a number programs that only support MySQL. This means that if you want to run a number of programs it is easier to run MySQL rather than PostgreSQL since you'll have to run it anyway.

So while PostgreSQL has largely fixed the problems they had on shared hosting MySQL still has the market share.

At work, our Rails apps run on Postgres because our Big Freaking Consultingware runs on Postgres and, since they're all black boxes for me anyhow...

File that as instance #572 of the Power of Defaults. Now, go take a look at the popular OSS and commercial packages for deployment at web hosts, and you're going to see a preponderance of MySQL. (Wordpress, VBulletin, etc, etc.) That is a self-fulfilling prophecy.

Plus if you look under the covers of MySQL each "table" is just a file on the disk. Unsophisticated database users feel comfortable with that too. "Sharding", another thing they like, is another attempt to evade using advanced features (not that partitioning is actually "advanced" these days, nor is a semi-decent query optimizer) that other databases take for granted.

The other major difference is that MySQL AB put a lot more effort into having an extremely concise, easily navigable and user-friendly online documentation repository and associated support community. Postgres has since made good strides in this area, but a lot of the documentation still reads like something intended for a fairly specialised audience that more or less knows what it wants; the ignoramus-friendly parts of MySQL's documentation are a lot friendlier.

For this and its administrative simplicity, it just got to be known as the quick and easy database, and Postgres as the rocket science database. (In reality, this is not true; only Oracle is the rocket science database. :-)

Also, MySQL was/is more appealing to corporate adopters since an Actual Company(TM) is Behind(R) the project. Postgres has a commercial footprint in the form of various third-party consultancies like CommandPrompt, but the core of the project is a Debian-like anarchic band of hackers. Nothing turns corporate America off more than a bunch of long-haired GNU hippies when it comes to big-ticket stuff, though they begrudgingly put up with it for Linux by now, Linux having become somewhat "legitimised" by the backing lent to it by IBM, the existence of Redhat, etc.

On the other hand postgres is way faster for complex queries and its support for SQL features is second to none.