Why isn't PostgreSQL as widespread as MySQL?

Question

I'm currently developing a pretty big project, and I'm considering open source databases to use.

One of the main factors I consider is support, and it seems like there is not much support/community for PostgreSQL compared to MySQL, even though MySQL seems like a much less fully featured product than PostgreSQL.

Should this fact shape my opinion in one direction or the other? Am I wrong with my conclusion of PostgreSQL? Is there any real technical reason which makes it unsuitable for widespread use which I am not aware of?

Answer 1

Most people use MySQL, because most people use MySQL.

It sure has advantages to use whatever is the most popular (community support, driver support, books, etc)

Answer 2

PostgreSQL is somewhat more full-featured than MySQL, but it's also a lot harder to setup and administrate. Part of this is due to the fact that everyone's using MySQL, so there's tons of help available online. (at sites like this one!) However, most of the issue is due to PostgreSQL's design philosophy. There are several factors which contribute to this, none of them are objectively wrong, but they can inhibit pure ease of use. For example, a lot of PostgreSQL administration tasks must be performed as the postgres user. This is ok, but it's not really as slick as mysqladmin or just mysql and the good ole SQL shell.

I have heard (not verified myself) that MySQL is better for clustering and sharding, possibly due to superior vetting from companies like Google. However, that's just hearsay. Someone else may be able to confirm or deny this.

If it were me, I would pick MySQL. However, depending on the database features your application requires (such as proper optimistic locking), you may be better off with Postgres. Don't make your decision strictly on the basis of which one is "easier" to manage.

Answer 3

I actually started out as somewhat of a postgres partisan, but, and I realize this is kind of lame, MySQL just has a better windows client. Query browser is pretty slick and PgAdmin is annoying to use. If you're doing quick and dirty database, it's just less of a pain to setup.

Another huge factor is support. You can get MySQL/PHP hosting just about anywhere, it's practically a given these days. I'm not sure how common that is with Posgres. Of course with more and more cloud computing apps, it will be easier to have a virtual server setup exactly how you like it. Maybe that will help with getting more people to use postgres.

And speaking of PHP/MySQL, there are tons of OSS apps that are MySQL only. So if you're going to run a server with some off the shelf software you're going to need to have MySQL installed anyway, and why run both?

This is all from a hobbyist/low end perspective, of course. If you were doing a higher-end business a lot of these factors wouldn't matter, with the exception that the way a lot of developers learn new technologies is by playing around with them in their spare time.

Answer 4

I would recommend you to read this comparison.

In short MySQL is absolute leader in many of web sites when the transaction support is not required. But when it comes to transactions the picture is not that one sided and Postgres has a number of advantages and wide presence.

Answer 5

Here is my list of contrasts between Mysql and Postgres from my experiences:

I can say that Mysql is barely faster than PostgreSQL. But most of the people who are fan of PostgreSQL claim that Postgres is more solid & stable than Mysql and they alsp claim that Postgres is better for critical and large datas.

Interestingly Postrgres doesn't have a bug tracking web site. I guess they get bug reports by their maillists.

Postgres choice of their elephant logo came from their target of being heavy-weight rock-solid DB. Mysql's dolphin is symbolizing being Fast.

Another contrast between Mysql and Postgres is Mysql using SQL:2003 standards compliant language which has some similarities with MsSQL'S T-SQL and Postgres has Oracle's Pl/Sql like language.

As mentioned above I found mysql's shell tools easier and more useful than the Postgres's tools.

But choice is yours you have to decide on the one according to your priorities.

Answer 6

The database managaemnt tools for MySQL are free and absolutely brilliant to use whereas for PostgreSql one will have to pay and the free ones that I have been using (pgAdmin) is very flaky to use. So, if I were you I would go for MySql.

Answer 7

In the past MySQL had very little overhead for very small databases. So the common €0.99 hosting companies offered it. You know those hosting offers for thousands of websites which get less than one visitor per day

The intended customers just want something like a feedback form and did not care at all about "strange" concepts like sub-queries or transactions.

PostgreSQL on the other hand had far better transaction support (MySQL did lock the complete table!), did support sub-queries. PostgreSQL did hardly ever destroy a huge table, whereas MySQL had serious trouble which huge amounts of data. (OK, this is my subject impression).

But this did not matter. Those people did not need it. They did not have GBs of data.

Nowadays this all has changed. MySQL does support transactions in good way and Wikipedia proves that MySQL can deal which huge amounts of data. But still a lot of people have heard about MySQL because of its history. And as other poster already pointed out: Because it is used more often, there is more help and knowledge available.

It comes around in a handy bundle together with Apache and PHP. No need to think about a database server software. Just use the one provided.

Answer 8

As somebody who has used both, I would say the ease of use is entirely dependent on the eye of the beholder. I have no problem with either and both allow to do any administrative task quickly both from commandline and GUI. But they DO do these things differently, so it is the same as people claiming that Windows/Linux is more difficult to use, just because they have used one for a long time and are now trying to switch to the other.

Regarding support, I have never had need to move beyond Postgres online documentation, which is extremely well organized. I have heard people claim MySQL documentation superior, but my personal impression is just the opposite (for example, many of the function descriptions were lumped together in one big HTML page and not shown at table of contents, so you had to manually scroll through function list to find the syntax of the relevant one).

Regarding solidity, I have never had any issues with a Postgres database that runs now for years on a server that has occasionally had power outages. I have administered a server for a client who used MySQL, and 5 times out of 10 whenever server died (they were doing custom app development and tended to hog the server every now and then), you had to manually tweak MySQL to restore the database.

To answer the title question, all in all, Postgres is not as widespread as MySQL for reasonably the same reasons Linux is not so spread on desktop as Windows - Windows got there first, and the way you do things becomes convienient enough for you, so that a change is perceived as an annoyance. Additionally, MySQL has a commercial company behind it, which actively pushes things, which is not the case with Postgres.

Answer 9

PostgreSQL was not designed for the masses. It is much more powerful, robust, and reliable than MySQL, but MySQL is much easier to use.

Recent changes in MySQL make it a poor choice for small shops/independent projects.

You should use PostgreSQL if you can, and SQL Server Express if you want ease of use.

The linked post has references as to why MySQL is no longer a good choice.

Answer 10

I have used both products in a business environment for many years. Mostly I've used MySQL for smaller, primarily read-only databases which require high speed, low overhead, and quick setup/administration. Typical standalone web sites would be an example. But I prefer PostgreSQL for core business or "mission critical" apps. Some of this is for historical reasons, since at the time I began, MySQL was pretty much a joke. It didn't do many things, and what it did do, it didn't always do well -- referential integrity, transactions, schemas, etc. In some cases it permitted invalid dates, and it handled certain other things in a non-standard way. And then there were the licensing issues and questions about how "open" and "free" MySQL and its various plug-in database engines would be in the future.

PostgreSQL, although being somewhat more difficult to set up and maintain, impressed me from the start as a very solid product. Certainly it has a few quirks of its own (e.g. "vacuuming"), but there was a certain quality and extensibility to it that somehow told me that the designers knew something about databases and how they were to function in a serious, demanding environment. Over the years, PostgreSQL has pushed the edge and offered functionality that was difficult to find even in commercial database products -- things like MVCC, OODBMS capabilities, and user-defined datatypes. Plus, I liked the licensing and the cohesiveness of its development effort and user community. Fewer users, perhaps, but knowledgeable, advanced users.

Today, both products (and their associated administration tools and drivers) have evolved and have greatly improved, and it would be much more difficult to recommend one over the other. I continue to use both, mostly drawing on experience and a "gut feel" to decide which is more appropriate for a given task. Each has its own tradeoffs, and you need to assess the situation depending on the features and levels of performance you need for getting the job done, and on how well it will tie in with your existing or future infrastructure. But I wouldn't give much attention to which product gathers the most media attention. Much like people first learning Windows and later exploring Linux, a similar situation seems to exist with MySQL and PostgreSQL. It's good to know both.

Answer 11

PostgreSQL holds up well when you put the screws to it. Think, 18-table joins with combined inner/outer/subselect type queries. Big, nasty f!@kers that take hours to write, sort through many millions of records, and need to consistently get results in under 5 seconds.

MySQL falls completely flat on its face here, while PostgreSQL shines if you give it lots of RAM. PostgreSQL also slaughters MySQL for ACID compliance and data integrity. Try entering a date of "02/31/2007" for example. MySQL takes it, PostgreSQL covers your arse with an error.

MySQL replication is easy to setup, which is important because you'll continually have to set it up.... again. Postgres is much better about this, even though its replication support is more recent.

In short.... do you care about your data? If so, go with PostgreSQL. Otherwise, go with MySQL.

Answer 12

PostgreSQL support is excellent. It is the best mailing list i was ever subscribed to. It does not have so much volume because of its excellent documentation. You only need help when you are in trouble or you have not read the docs.

Answer 13

Based on my recollections of doing app development in the 90's, the first free relational database that folks could get their hands on was by a guy in Australia named Dave Hughes. It was called "mSQL". It was fine, but it wasn't open source- and there were some commercial restrictions on it. You could write perl against it, and so folks doing lots of perl-based web development would use it as the backend. This was the age of mod_perl and cgi, so perl compatibility was pretty important if you were doing web stuff.

Eventually, mSQL atrophied, and there wasn't much support. A replacement database called MySQL was available, and it was binary-compatible with the mSQL interfaces. A number of perl shops switched over.

At this time, PostGres was a very early, very experimental Object / Relational DBMS. It was basically a bunch of grad student's thesis project, so it had lots of experimental stuff, but it wasn't really solid. It took until PostGresql95 and then it got pretty darn good- including support for a pretty decent flavor of ansi SQL, etc.

MySQL was there because- even though it didn't support transactions or foreign keys in those days- it was compatible with the existing de facto standard, worked well enough, and could be thrown into service doing all those guestbook apps- the only credible alternative was Oracle, at $50K / seat back then.

Answer 14

Probably the prime reason for MySQL's relative dominance has been that it has run nicely on Windows for years, whereas PostgreSQL didn't do this natively until 2005. Given that most web developers would have been using Windows, and probably still are, MySQL makes for a more convenient test environment.

On top of that, MySQL has some niceties that many may prefer where ease of use is concerned. eg. REPLACE or INSERT ON DUPLICATE KEY UPDATE. Or, DESC tablename. These conveniences perhaps add resistance to migrating to PostgreSql.

Answer 15

I used both for many years (80% for web applications), and I love both, but I should say use the one that your prefer, both are great database, both can fit on 90% of cases, but there is still some good/bad points for each.

If you want (easy) replication, go to MySQL, for now, replication on Postgresql is the hell to manage and monitor (and I don't think the stream replication (9.0) will change something, it's still very light on the administration/monitoring side), same thing if you want clustering.

MySQL also allow some things that are missing on PostgreSQL: Partitioning (PostgreSQL "cheats" to simulate partitioning), a Query Cache that can be really useful on read-massive applications, the Event Scheduler (okay it's pretty basic on MySQL but it do it's job !)

the REPLACE statement can also be useful, it's missing on PostgreSQL.

On the other side, PostgreSQL also have some good advantages, you can create your custom data type, you have better backup systems (custom smaller format, but YES on MySQL you CAN do coherent backups with InnoDB WITHOUT locking), you can create almost anything you want with UDF (you can use a lot of language), for GIS database, PostgreSQL is also better (but there is some "cheating" that make is harder to use).

Just take a look on the features that you want, if it's missing on PostgreSQL, choose MySQL, if it's missing on MySQL, choose PostgreSQL.

Answer 16

MySQL, as simple as it is, is better suited to.. well.. simple people.. which are the vast majority.

PostgreSQL and other better RDBMSes out there have better performance, scalability, more features and so on but they come at the expense of the complexity. Many small (web) apps start very small and very simple with mysql and in the end, as they grow, tend to get stuck in this rDBMS mainly because of the costs of a migration to a real RDBMS. Sad but true.

PS : I personally prefer PostgreSQL.