1. The commands are very user friendly. In MySQL you can issue commands like "show tables" and "show databases". The last time I used PostgreSQL, the commands were much more esoteric. Things like "\dt". It adds a good hill to the learning curve
2. MySQL is everywhere. Basically anywhere you go on the 'net you can find people who know MySQL, quirks and all. It's very easy to get help, and it's the DB that most tutorials (for other things, like Rails, PHP, etc) use. MySQL is also the standard database you can find on any web host.
3. Replication was a big deal. MySQL has had replication built in for many years, and it's quite easy to use. At my last job we would have been quite happy to go to PostgreSQL for the performance, but we needed replication. At the time, there were solutions for PostgreSQL in outside projects such as Slony. Since we didn't have any PostgreSQL experience we didn't want to expend all the resources on testing and configuring everything. In the last two years or so PostgreSQL has gained an official in-tree replication solution. This makes understanding how to get replication up and running much easier.
4. MyISAM is small and fast. When machines were much slower, that could be a real benefit. Of course you sacrificed all sorts of consistency to get that.
At this point, I'd say PostgreSQL's biggest problem is mindshare. MySQL has been improving the whole time, and some of the worst warts are gone. It may not be the best tool for the job, but MySQL is just so common. It's trivial to find help, advice, employees familiar with it, programs that support it, etc.
I'd really like to use PostgreSQL in production so I could get more experience with it. It has some amazing capabilities. But our needs are relatively simple and we have lots of MySQL experience so there is no reason for us to look at switching right now.
"show tables;" is a server-side command. \dt is a command you give psql to say "ask the server what tables there are and list them." \d stands for describe. If you use a graphical client, it will have to query the system catalogs itself which you can do if you want.
As for replication, the real challenge is that replication is not a one-size-fits-all thing. Slony, Bucardo, and the built-in streaming replication have different limitations. For example you can't replication from 9.0 to 9.1 with streaming replication but you can with Slony and I would expect you could with Bucardo too. So with the out of tree replication systems you can actually have a zero-downtime upgrade as long as you block writes during the upgrade of the master. With streaming replication, assuming pg_upgrade is supported for your upgrade, you only have a short downtime window, but it is downtime.
Also streaming replication is all or nothing. With Slony you can replicate different pieces of your database. On the other hand, with Slony, you can (accidentally) replicate only a piece of your database.
So the differences give you a lot of flexibility there, but you need to have a clear idea of what you need and why you need it before choosing a replication solution. Slony is perfect for what Affilias uses it for, while streaming replication wouldn't meat their needs.
Also I don't think that PostgreSQL suffers from a lack of mindshare. Ever since I have been building apps on open source db's, PostgreSQL has been regarded as the go-to database for complex business apps. The sorts of things people do with the database are different. There is consequently a huge "dark community" if you will (in the sense that you don't see them).
This was driven home to me when I went to the Malaysian Government Open Source Software convention last year. There were two booths (Oracle and one other) which had the MySQL logo, and a few more that were offering MySQL services, while I counted at least 5 that were using the PostgreSQl logo. Most of those were reselling EnterpriseDB's PostgreSQLPlus but it when I looked at the level of interest and the number of government deployments I was asked about, it was clear which people were usually choosing for complex work.
As for user-friendliness, MySQL used to be much more user-friendly than PostgreSQL but I don't think that's true anymore. The command-line tools are more full-featured and the in-app help is better on PostgreSQL. \? brings up a list of psql client-side commands and what they do for example, and \h [command] will give you a summary of the syntax of an SQL command. I use \h quite a bit when I am trying to remember the specific syntax of a new feature, or when I am doing something I rarely do (like ALTER TABLE). I think that since about PostgreSQL 7.3, PostgreSQL has been at least as easy to use as MySQL, and quite frankly far more robust.
Finally, the PostgreSQL planner is awesome. I have not had to rewrite a query to get around planner limitations since 8.1.
I've been able to look at PostgreSQL a few times over the years, and your right that the friendliness is getting better.
It's a very impressive system.
I guess if "popular" is a synonym for "demotic" (in the sense of progressing away from professional dba's etc) then MySQL is also more popular in that sense too. But in terms of actual mindshare, it is rediculously hard to compare them.
I can't speak to horizontal scaling, as my little database with maybe 50 million rows never comes close to loading out its aging 4-core server, even when being hammered by lots of web visitors running complicated reports and internal clients performing all sorts of CRUD operations.
The best advice I have is that using a different database system, particularly ones as different as MySQL and PostgreSQL (which are essentially opposites) is like going to a foreign country. You'll find things you like and things that you don't like, and it will have a real impact on your quality of life, but it's hard to say in a few words the things that will really matter to you. You don't have time to live in every country in the world, but you owe it to yourself to at least visit a few that come highly recommended.
Whatever you do, don't try to make the new country into your old country. You will just be disappointed.
The same goes for database systems.
The fact that many large companies and organizations have successfully scaled a particular technology is a useful yardstick, and while it does little to answer the question of which scales better, it does, by the process of elimination, help answer the question of which cannot scale.
I don't know about others here, but I would welcome actual objective information on this topic rather than sarcasm and an appeal to popularity.
I had hoped that others here would enjoy the sarcasm in the context of the full-throated yet fact-free advocacy of the parent, the text of which somehow still remains legible on an off-white background.
Also I expect that with 9.2 we will probably see a release of Postgres-XC which will kick the pants off any MySQL sharding solution out there.
Postgres-XC is a way to abstract database shards such that referential integrity etc. is enforced between shards. The best comparison to date would be to Teradata's clustering system. Better yet, Postgres-XC is just a set of patches on PostgreSQL, and is on-the-wire compatible, so you can swap it out when your database gets too big. This will also bring an ability to concurrently query different shards and aggregate the results without the application having to know anything about the sharding....